Autopopulate empty fields with values from another field. (1 Viewer)

Dio

Registered User
Joined
Jul 7, 2019
Messages
10
[SOLVED] Autopopulate empty fields with values from another field.

I have a table which contains data on Number of offspring and Number alive imported from an Excel spreadsheet.

Number of offspring always contain value while Number alive is sometimes empty. I would like an empty row of Number alive to be auto-populated from Number of offspring and leave "as-is" if the row is not empty.

This could be accomplished in a form or report but I would prefer to do it during import using VBA or using Update Query to save it to the table, but I can't figure out how.

TIA!
 
Last edited:

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,295
Sounds like you'll have to use some code after the import process to modify the table design to add the default value based on the current import information.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,497
Can you please post sample data to demonstrate your issue? Give us 2 sets of data:

A. Starting data--Show what you begin with. Include table and field names and enough data to cover all cases.

B. Expected results of A--show what you hope to end up with.
 

vba_php

Software Engineer
Joined
Oct 6, 2019
Messages
1,400
i believe you can do this with an update query. maybe something like:
Code:
UPDATE table SET (field1) iif(numberalive = null, numberoffspring, numberalive)
i have no idea if that will work, but you can take this and modify it? I haven't written a query in access for a while now, so by all means don't quote me. :)
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,400
You have two problems.
1. fixing existing data
2. keeping it from happening again

The update query solves problem 1 but problem 2 should be solved by modifying your append query to include the same expression. If you are currently just importing data, change that process to link to the spreadsheet rather than import it and then run an append query with the same expression as the update query example above.
 

Dio

Registered User
Joined
Jul 7, 2019
Messages
10
i believe you can do this with an update query. maybe something like:
Code:
UPDATE table SET (field1) iif(numberalive = null, numberoffspring, numberalive)
i have no idea if that will work, but you can take this and modify it? I haven't written a query in access for a while now, so by all means don't quote me. :)
That didn't work. I even replaced null with "". Both detect the null/empty rows but don't update them.

But this, i think is serendepity, leading me to the venerable Nz([Number alive], [Number of offspring]).

One thing noticed though is that although Nz() detects null, MS Access displays You are about to update (shows the total number rows) rows, but only updates the null ones.

Does MS Access always displays the total number of rows in the warning dialog regardless of how many rows it actually updates?
 
Last edited:

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,295
Hi. Without seeing your SQL statement, my guess is Access updated all the rows. It may appear only the null records were updated because the other records were updated with the same values.
 

Dio

Registered User
Joined
Jul 7, 2019
Messages
10
Hi. Without seeing your SQL statement, my guess is Access updated all the rows. It may appear only the null records were updated because the other records were updated with the same values.
Well, that's a relief. Thanks!
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,295
Well, that's a relief. Thanks!
Just be aware though, if your query is processing a lot of records, updating those records that don't need to be updated (because they have the same values anyway) could slow down your query. Good luck!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top