Duplicating data in different cells

[Peter Ramshaw]

Hi techiies

Could anyone asssit with advise on this?

I want to create and update query that will 'find' cells in one field that are not null and then copy that data into another cell in the same record that IS null.

Real world: My database has Work Email adresses [Work Email Address] and Private Email Addresses and I want to consolidate these into a third column which will have no empty cells.

Some records have BOTH so, if that's the case, I want to use the home emaiul address over the work one. Okay so far?

So, I want to query [Work Email Address] and [Email] and copy data from these into my new field [Preffered Email].

I'd like to do it in one operation if possible but I'm equally happy to do several operations:

Like first query for all Is Not Null in [Email] and copy that to [Preferred Email]

Then query all Is Not Null in [Work Email Address] and copt data to [Preferred Email] but ONLY WHERE THAT CELL IN NULL.....

Anyone poihnjt me in the right direction?

Cheers
Peter R

 

[John Big Booty]

Instead of storing one of the email addresses again (a bit like a calculated field really), I would probably make your additional field a numeric field and bind that to an option group on your form. The option group would be titled preferred email address, and store say 1 as Home and 2 as work. You could make the default for this 1 (for home). I'd also make this the tab stop on your form before both of your email address fields.

You could then test the Option group value in the On Lost Focus event of both email address fields, and if the one relating to the option group value is left blank (null) prompt the user to supply that email address.

 

[Peter Ramshaw]

Thanks John, but I won't even begin to say that I understood any of that. Sorry but I'm not that up with programming etc, just a plodder. The reason I wanted to do this (or somethign smilar) was so that I could create one field for email mail merges. I fear rthat doing anything other than copying actual text into a new field will affect the ability to run a smooth mail mewrge email mopertion ....
Cheers
Peter

 

[John Big Booty]

Have a look at the attached DB.

Check the Form's Before Update event for the code that is making sure that there is an email address in the preferred email field.

Also check the query used to generate the mail merge data.

 

[Peter Ramshaw]

Ahh, the penny's dropped now. Excellent stuff. Worked a treat!!!
You're the man!
Many thanks again.
BTW, I used the same approach to generate a list of preferred addresses for my contacts (ie, home or work!) !!!!! Awesome.
Peter

 

[John Big Booty]

Good Work