Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-19-2019, 08:56 PM   #1
tejap
Newly Registered User
 
Join Date: May 2019
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
tejap is on a distinguished road
Lightbulb Update Empty Cells with Above Cell

Hi Team,


I am trying to update the blank cells in Access. Need to update the date, in case if the cell is empty or blank then take value from above cell else keep as it is.

tejap is offline   Reply With Quote
Old 05-19-2019, 11:50 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,112
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Update Empty Cells with Above Cell

Welcome to the forum

Access doesn't have cells - it has fields and controls. Your question is too vague to provide a possible solution. Please provide more information.

Are you referring to a table or a form? is the form continuous? is the 'cell above' in the same record? a different record? if a different record, how are you defining the 'record above'. Is this to populate a new record or an existing record?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-20-2019, 12:22 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,226
Thanks: 512
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Update Empty Cells with Above Cell

Do these cells already exist? Or are you creating a new record? Are you in datasheet view, on a form, in other words the question is not very well formed.

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 05-20-2019, 05:58 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,220
Thanks: 86
Thanked 1,620 Times in 1,503 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Update Empty Cells with Above Cell

tejap - the question betrays an Excel solution somewhere in the recent history of whatever you are trying to do. In Excel, that "fill-in" is automatic, or nearly so. In Access? Not so much, because tables don't have a well-defined "before" and "after" record. For complex reasons, Access tables have no particular or predictable order at all. Queries, on the other hand, can impose order on the chaos.

If you are manually entering data into a datasheet view of a table or query, you can use shortcut keys. See, for example,

https://www.fmsinc.com/microsoftacce...rtcut_keys.htm

In that case <CTRL><'> (control apostrophe) brings down the value from the previous record's corresponding field. The article shows you a couple of other shortcut sequences as well, and they are good to remember.

Be aware that "datasheet view" is an Access "default form" that imposes order based on how it is viewed. If there is no primary key and no index in the table, however, there is no guarantee that if you close datasheet view and then open it again on another day that you would get the same order, particularly if any updating of that table has occurred in the interim.

If you are manually entering data in a form of your own making, then code in your form could be made to remember the most recent value entered for a particular field and would allow you to define a way to select that value. You would probably need to use some VBA event code to make that happen.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-20-2019, 04:03 PM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 858
Thanks: 10
Thanked 177 Times in 168 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Update Empty Cells with Above Cell

My interpretation is that if a record has no date, update with the date from the prior record. This will require an ordered query as the domain (but not on the date field) and a subquery that gets the prior date where Is Null [dteDate]. This would only be the basis for an append query that calls the 1st query. Not something I'd want to try as I find subquery problems are seldom as simple as the posted examples one can find. Regardless of the difficulty of this one, it is quite possible that once ordered, the date fields are no longer in sync with what is seen in the table, thus the "prior" date is no longer the desired date.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 05-20-2019, 08:33 PM   #6
tejap
Newly Registered User
 
Join Date: May 2019
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
tejap is on a distinguished road
Post Re: Update Empty Cells with Above Cell

Hi Team,

Thank you for your response. The data is in datasheetView and indexed, look like below.
I would like to get date from the the above reference in case if date is blank, doing it will be much difficult as there are millions of records. Can we build a query to populate the date or do we have any alternative?

Thank you

Id Date
1990025 28/09/2018
1870952 28/05/2018
1864529 1853686 1853703 1857246 1852582 21/05/2019
1849318 29/03/2018
1849497 1847253 1850939 02/04/2018
1846428 21/03/2018
1848937 1867052 1854600 21/05/2019
1959745 1919099 1853248 1870994 1919663 1947072 1865051 1850748 1869200 1915098 1865297 11/05/2018

Last edited by isladogs; 05-20-2019 at 09:51 PM.
tejap is offline   Reply With Quote
Old 05-20-2019, 09:52 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,056
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Update Empty Cells with Above Cell

The last post was moderated.
I've edited it to remove lots of blank lines
Posting to trigger email notifications

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-20-2019, 11:54 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,112
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Update Empty Cells with Above Cell

looks like you have multiple ID's in the same field e.g.

ID1 - 1990025
ID3 - 1864529 1853686 1853703 1857246 1852582

is this correct?

As mentioned by others databases need an order - and you do not have anything in what you have provided to set an order (if you sort by ID, ID1 will come after ID3 for example)

Access is not excel and works in a completely different way. You cannot apply excel methods to databases.

Suggest rather than asking for help on solving a method you think is the way to do it, explain more clearly what you are trying to do - and provide some examples, your example data does not include any records without dates. (hopefully your table is not storing data as you have it above)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Update table where column cell is empty Heidestrand Queries 7 08-06-2015 04:40 AM
One cell in tbl 1 refers to sum of cells in tbl2 Keeperen Tables 11 07-04-2012 04:47 AM
Update Query and Empty Cells batwings Queries 8 07-01-2009 09:33 AM
1 cell referring to 2 cells rvd48 Excel 3 01-07-2008 02:14 AM
Updating EMPTY cells Jonny General 2 09-02-2007 07:16 AM




All times are GMT -8. The time now is 10:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World