iif statement to generate date

[Theremin_Ohio]

There are three fields in my customer database that should be taken into consideration for my iif statement:



Fields:
Contact_Date (date field)
Contact_Type (look-up field)
Next_Appointment (date field)

How ought I to express "if Contact_Type=[Flag_Only], [Flag/Pole_Install] or [Scheduled_Maintenance], then [Next_Appointment]=[Contact_Date]+180 days"?


I want to be able to send maintenance reminders to current customers.


Cheers!

[June7]

Where are you using this expression - in a query or textbox on form? Cannot set value of [Next_Appointment] within a query or textbox. Must use code (VBA or macro). However, really no need to save this calculated value. You can always calculate it when needed. A principle of relational database is to save raw data and do calcs in queries/reports.

IIf([Contact_Type]="Flag_Only" Or [Contact_Type]="Flag/Pole_Install" Or [Contact_Type]="Scheduled_Maintenance", [Contact_Date]+180, Null)

[Theremin_Ohio]

Where are you using this expression - in a query or textbox on form? Cannot set value of [Next_Appointment] within a query or textbox. Must use code (VBA or macro). However, really no need to save this calculated value. You can always calculate it when needed. A principle of relational database is to save raw data and do calcs in queries/reports.

IIf([Contact_Type]="Flag_Only" Or [Contact_Type]="Flag/Pole_Install" Or [Contact_Type]="Scheduled_Maintenance", [Contact_Date]+180, Null)

I am using this expression in a table that appears as a sub at the bottom of a form. I will need to know this date for potentially thousands of customers (right now, there are several hundred names in the database), but already it takes incredibly long to review each record and select the type of contact and/or next appointment date. I work in a fast-paced environment where I cannot afford to spend thirty to forty-five minutes inputting customer contact history.

Is the VBA code simple to embed? I would be much happier with this option.

[Theremin_Ohio]

Also, I tried to enter the expression as a validation rule in design view, and a dialogue box popped up stating there is an invalid operand operator?

[NTC]

off the cuff - and assuming you didn't make a typo - to enter that phrase as a validation rule I think you need to tweak it:

=(phrase)


am pretty sure you have to wrap that phrase with the =( )

if it were in a query you would need to preceed it with:
FieldName: phrase

[Theremin_Ohio]

off the cuff - and assuming you didn't make a typo - to enter that phrase as a validation rule I think you need to tweak it:

=(phrase)


am pretty sure you have to wrap that phrase with the =( )

if it were in a query you would need to preceed it with:
FieldName: phrase

Do you mean I ought to write my validation rule as...

"Next_Appointment"=(IIf([Contact_Type]="Flag_Only" Or [Contact_Type]="Flag/Pole_Install" Or [Contact_Type]="Scheduled_Maintenance", [Contact_Date]+180, Null))

I am attempting to do a lot of new things at once! Thanks for your assistance.

[NTC]

=(phrase)

just add those symbols in the appropriate place and see if it works....

[Theremin_Ohio]

=(phrase)

just add those symbols in the appropriate place and see if it works....

I am assuming that was a yes. Thank you very much! I'll try this tomorrow.

[Theremin_Ohio]

=(phrase)

just add those symbols in the appropriate place and see if it works....

NTC,

I tried wrapping this expression as you suggested (I copied and pasted to eliminate errors), by entering Field_Name=(expression), but I got an error message:

"Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint."

I take it that this means, in plain English, that there was a problem with the manner in which the look-up field options are referenced in the expression, OR it means I have wrapped your expression incorrectly.

I tried the following two variations which both yielded the same error message:

"Next_Appointment"=(IIf([Contact_Type]="Flag_Only" Or [Contact_Type]="Flag/Pole_Install" Or [Contact_Type]="Scheduled_Maintenance", [Contact_Date]+180, Null))

[Next_Appointment]=(IIf([Contact_Type]="Flag_Only" Or [Contact_Type]="Flag/Pole_Install" Or [Contact_Type]="Scheduled_Maintenance", [Contact_Date]+180, Null))

Am I using incorrect brackets or parentheses to wrap certain portions of my expression? Any further assistance would be greatly appreciated.

Theremin_Ohio