IIf in Validation Rule

[Bugsy]

I have a form that is used for initial entry of data as it is captured. On that form is a field called PROVCODE that is a pick-list from a provider dictionary with a bound column of the provcode value from the provdict table (e.g. - J07). On that same form a few fields down there is a DOS (date of service) text box. It is this box I want to add the validation rule to. Originally the validation rule was <=Date() so that the user couldn't enter a DOS value as a future date. Now I need to change this rule so that if the PROVCODE value is J07 that the validation rule should be (<=Date()-21) (meaning the value being entered must be at least 21 days ago in the past) otherwise is should be <=Date(). I thought it would be simple, but cannot get it to work in the Expression builder for the DOS field in the form.

I am not a VBA programmer.

Any ideas?

Thx

[June7]

I don't think the validation expression can use IIf, seems I tried that. Will have to programmatically handle the validation in VBA. Two approaches.

1. Use the PROVCODE box AfterUpdate event. Code would modify the Validation property of DOS box.

2. Use the BeforeUpdate event of DOS box to validate entry based on value in PROVCODE. Validation property not used.

[Bugsy]

June7:

Thx, but as I don't "do" vba can you give me some idea as to what this code would look like?

Many thanks.

[hertfordkc]

Go to the property sheet of Provcode and select the afterupdate property. Click on the 3 dots and select "Code Builder".
The Visual Basic screen will appear with the event handler
Sub Provcode_afterupdate()
End Sub

Insert:

Dim S as string
If [Provcode] = "J07" then
S = "<= #" & StringDate(Dateadd("d",-21,today())) & "#"
else
S = "<= # & StringDate(Today()) & "#"
end if
[DOS].validationrule = S

This all depends on how the date field is defined in DOS. I suggest you test by manually inserting a string into the Validation Rule, followed by making sure that the above code gets it right.

[Bugsy]

hertfordkc:

Thanks for taking the time to help. I am getting an error message (Compile Error: sub or functuion not defined) on the frst instance of "today". I also think I may be mssing a " in the Else portion, but got the same error when I added one.

Again, thanks for any insights you can share.

Best Regards

[hertfordkc]

Go to the property sheet of Provcode and select the afterupdate property. Click on the 3 dots and select "Code Builder".
The Visual Basic screen will appear with the event handler
Sub Provcode_afterupdate()
End Sub

Insert:

Dim S as string
If [Provcode] = "J07" then
S = "<= #" & Dateadd("d",-21,Date) & "#"
else
S = "<= #" & Date) & "#"
end if
[DOS].validationrule = S

This all depends on how the date field is defined in DOS. I suggest you test by manually inserting a string into the Validation Rule, followed by making sure that the above code gets it right.

My apologies for the error

[Bugsy]

THANKS!

No apology necessary. Your help proved invaluable.