Update Empty Cells with Above Cell (1 Viewer)

tejap

New member
Local time
Today, 06:27
Joined
May 20, 2019
Messages
3
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:27
Joined
Feb 19, 2013
Messages
16,555
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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:27
Joined
Jul 9, 2003
Messages
16,245
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:27
Joined
Feb 28, 2001
Messages
27,001
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/microsoftaccess/Forms/shortcut_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.
 

Micron

AWF VIP
Local time
Today, 09:27
Joined
Oct 20, 2018
Messages
3,476
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.
 

tejap

New member
Local time
Today, 06:27
Joined
May 20, 2019
Messages
3
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 a moderator:

isladogs

MVP / VIP
Local time
Today, 13:27
Joined
Jan 14, 2017
Messages
18,186
The last post was moderated.
I've edited it to remove lots of blank lines
Posting to trigger email notifications
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:27
Joined
Feb 19, 2013
Messages
16,555
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)
 

Users who are viewing this thread

Top Bottom