Need help with an if then statement on a form

[Nicole]

I am trying to modify my database so that I do not have to enter in data
twice. I have two fields for address right now: Clinic address & Mailing
address. Sometimes they are different and sometimes they are the same. I need
some sort of statement that will automatically enter in the clinic address
into the mailing address field if the mailing address field is null. Any
suggestions will be greatly appreciated.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...9f5c05d0da11&dg=microsoft.public.access.forms

 

[Mr. B]

Try placing a checkbox on your form near the Clinic address controls. Then
use code in the after update event of each of your Clinic address controls to
first check the status of that check box. If that check box is not checked
you do nothing, otherwise if the check box is checked, indicating that the
the addresses are to be the same, then you can check to see if the Mailing
address is not null and if so, write the same value that is in each control
to the matching Mailing address control. If when you check the status of the
the first Mailing address control and find that is already has a value, you
can present a message box to your user asking if they really want to replace
the existing value and take the appropriate action.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Chegu Tom]

In the AfterUpdate event of that checkbox you will also want to look at your
clinic and mailing address controls and copy what needs to be copied. In
case the user clicks the checkbox after entering the clinic assress

So you have just one field (or control) for each address or do you have
fields for street, city, state etc?

 

[Nicole]

I have seperate fields for each: address, suite/apt, city, st, zip.
Thanks for your help. I'm going to try this now.

 

[Nicole]

Apparantly, I'm not good at writing the code for the expression. Could you
give me an idea of what the code/expression should look like? Thanks.

Just an idea of what's going through my head.... When I was trying to figure
this out before I was using an If then statement as follows:

llf(IsNull([Mail Address]),"[Clinic Address]",[Mail Address])

That is what I was trying to put in to the Mail Address field which
obvioulsy didn't work.

So, now after your suggestions, I have a checkbox for people to check if
mailing address is the same as clinic address but I can't figure out the
code.......

Thanks again!

 

[John W. Vinson]

Apparantly, I'm not good at writing the code for the expression. Could you
give me an idea of what the code/expression should look like? Thanks.

Just an idea of what's going through my head.... When I was trying to figure
this out before I was using an If then statement as follows:

llf(IsNull([Mail Address]),"[Clinic Address]",[Mail Address])

That is what I was trying to put in to the Mail Address field which
obvioulsy didn't work.

This will return the *literal text string* [Clinic Address] if [Mail Address]
is null... not the value in that field.

It's actually simpler than that: you can use the NZ() function:

NZ([Mail Address], [Clinic Address])

will return the Mail Address if it exists, and the Clinic Address if it is
null.

 

[Nicole]

I tried that but it didn't work.

In the forms design view, in the properties for my check box, under data, i
put this expression in the "after update" field.

Should I be putting this expression somewhere else?

Apparantly, I'm not good at writing the code for the expression. Could you
give me an idea of what the code/expression should look like? Thanks.

Just an idea of what's going through my head.... When I was trying to figure
this out before I was using an If then statement as follows:

llf(IsNull([Mail Address]),"[Clinic Address]",[Mail Address])

That is what I was trying to put in to the Mail Address field which
obvioulsy didn't work.

This will return the *literal text string* [Clinic Address] if [Mail Address]
is null... not the value in that field.

It's actually simpler than that: you can use the NZ() function:

NZ([Mail Address], [Clinic Address])

will return the Mail Address if it exists, and the Clinic Address if it is
null.

 

[John W. Vinson]

I tried that but it didn't work.

In the forms design view, in the properties for my check box, under data, i
put this expression in the "after update" field.

Should I be putting this expression somewhere else?

Yes. In the Control Source of a textbox on your report, or a (non-editable)
textbox on the form.

If you're trying to *COPY* the clinic address into the mailing address, you'll
need to call some simple VBA code in the afterupdate event to do so.

 

[LUKE ASHILN]

I am trying to modify my database so that I do not have to enter in data
twice. I have two fields for address right now: Clinic address & Mailing
address. Sometimes they are different and sometimes they are the same. I
need
some sort of statement that will automatically enter in the clinic address
into the mailing address field if the mailing address field is null. Any
suggestions will be greatly appreciated.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...9f5c05d0da11&dg=microsoft.public.access.forms