This is a dangerous approach, because it can end up with meaningless junk in your mailing address fields. For example, if your mailing address used only two of the AddrX fields, but your home address uses all four, you don't want MailAddr3 and MailAddr4 to be populated from HomeAddr3 and HomeAddr4!
It also creates a maintenance problem, because if the home address changes, how do you know whether or not to change the mail address?
A better approach would be to add a boolean field, DifferentMailAddress. On your data entry form you can hide the mail address fields if that checkbox is not selected, and when you are generating mailing address labels etc, you can use IIf expressions in your query – for example:
IIf( [DifferentMailAddress], [Mail Addr1 Ld], [Home Addr1 Ld] ) AS [Addr1 Ld]
Graham Mandeno [Access MVP 1996-2016]
I have a table that has address fields. There are two sets of addresses which are Home and Mailing. Sort of like a business and a home address.
Because there are nulls in one the sets of addresses I want to put the values from the Home addresses into the Mailing address fields if the Mailing address fields are null.
Right now I have the following sql and it works but if I add the other fields nothing happens.
How do I write the query to update all of the fields in one query?
here are the fields
Home Addr1 Ld
Home Addr2 Ld
Home Addr3 Ld
Home Addr4 Ld
Home City Nm
Home Postal Cd
Home State Cd
Mail Addr1 Ld
Mail Addr2 Ld
Mail Addr3 Ld
Mail Addr4 Ld
Mail City Nm
Mail Postal Cd
Mail State Cd
UPDATE tblEmployeeAddresses SET tblEmployeeAddresses.[Mail Addr1 Ld] = [Home Addr1 Ld]
WHERE (((tblEmployeeAddresses.[Mail Addr1 Ld])="-"));
Posted by: "Graham Mandeno" <email@example.com>
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (2)|