Problems with an expression intended to return 1 of 2 values 1

[spherey]

Hello all -
In Microsoft Access 2000, I'm building an expression in a form which I want to return 1 of 2 possible values. The relevant fields are PARC 1 Sent (a check box) and Date PARC 1 Sent.
What I want to have happen is this: if the PARC 1 Sent check box is not checked, I want the Date PARC 1 Sent to remain blank. On the other hand, if that box IS checked, I want the Date PARC 1 Sent to automatically display that day's date. I have tried many approaches - using =IIf, =IIf(IsNull, changing the check box to a Yes/No box, changing the format of the Date PARC 1 Sent in the underlying table from Date/Time to Text, removing the =Date() from the expression and replacing it with a text value (just to see if that was the problem). Nothing works, and depending on what I try, I get one of four returns: #Error, #Name?, the =Date() returned to me as text instead of as the day's date, or the opposite value from what I specify - as in, if I say to return the date on "Yes," it returns it on "No" instead.
Here's a transcription of the first expression I tried - can anyone offer a rewrite which would accomplish the returns under the conditions I specified above? What else do I need to do to make this work?

 =IIf([Forms]![PARC Form]![PARC 1 Sent]="Yes", "=Date()", " ")

Thanks,

Spherey

 

[LarryDeLaruelle]

Spherey:

I would use the Check Box's After Update event with this code:

If [Parc 1 Sent] Then
[Date Parc 1 Sent] = Date
Else
[Date Parc 1 Sent] = Null
End If

The If statement will test the value of [Parc 1 Sent] and if it evaluates to True it will set the date field to today; if not, it will set the field to null.

It's a simple transition from the Immediate If (IIf) to a straight conditional statement in an event procedure. If you have not used Events before, simply click on the check box and open the Properties dialog, select the After Update event and from the drop down box select [Event Procedure] then click on the elipses. This will open the VB editor, just type in the code above. I used your name constructs so if those are not exactly what you named the controls, simply substitute the correct names.

Just a note on naming conventions -- avoid spaces in your field names. If you use spaces, you must enclose the field name in brackets; if you don't use spaces the brackets are not necessary.

HTH


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[spherey]

Larry -

It worked! Brilliantly, easily, and on the first try!
Hurrah!

Thank you very much. I appreciate you taking the time to offer your advice. This is going to make the rest of my programming much easier - I have about seven more fields to set up in an identical format!

Thanks again,

Spherey

 

[LarryDeLaruelle]

Spherey:

Happy I could help.

This site was very helpful when I first started using VBA with Access.

Now that I know a bit more (although it seems that I'm always finding how much more I need to know) I enjoy contributing to the forums.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[spherey]

It's a big help - thanks!