Expression using Iif with dates

  • Thread starter Thread starter Yappy
  • Start date Start date
Y

Yappy

The following expression results in an error telling me that I may have
entered an operand without an operator. What ami I missing?

Iif ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1, Certification
Date]), Iif ([Certification Frequency] = “Monthlyâ€, DateAdd (“mâ€, 1,
Certification Date]), Iif ([Certification Frequency] = “Quarterlyâ€, DateAdd
(“qâ€, 1, Certification Date]), Iif ([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, Certification Date]), [Certification Date]))))


Expression entered has an invalid syntax….you may have entered an operand
without an operator

Thanks for your help!
 
Yappy said:
The following expression results in an error telling me that I may have
entered an operand without an operator. What ami I missing?

Iif ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
Certification
Date]), Iif ([Certification Frequency] = “Monthlyâ€, DateAdd (“mâ€, 1,
Certification Date]), Iif ([Certification Frequency] = “Quarterlyâ€,
DateAdd
(“qâ€, 1, Certification Date]), Iif ([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, Certification Date]), [Certification
Date]))))


Expression entered has an invalid syntax….you may have entered an operand
without an operator
Click to expand...


Where are you putting this expression? It's missing the usual leading "="
sign for a controlsource expression, and it's missing some left-brackets (
[ ). Maybe this:

=IIf ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
[Certification Date]), IIf ([Certification Frequency] = “Monthlyâ€, DateAdd
(“mâ€, 1, [Certification Date]), IIf ([Certification Frequency] = “Quarterlyâ€,
DateAdd(“qâ€, 1, [Certification Date]), IIf([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, [Certification Date]), [Certification
Date]))))
 
Thanks for your help. I am putting this into a text box called New Cert Due
on a form. My Certification Frequency is a combo box with the choices of
Annual, Semi-Annual, Quarterly, and Monthly. I want it to calculate the date
based on the Certification Date and the Frequency chosen.

I tried your expression and it is returning the following: #Name?

Is there something that I may be doing incorrectly?

Dirk Goldgar said:
Yappy said:
The following expression results in an error telling me that I may have
entered an operand without an operator. What ami I missing?

Iif ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
Certification
Date]), Iif ([Certification Frequency] = “Monthlyâ€, DateAdd (“mâ€, 1,
Certification Date]), Iif ([Certification Frequency] = “Quarterlyâ€,
DateAdd
(“qâ€, 1, Certification Date]), Iif ([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, Certification Date]), [Certification
Date]))))


Expression entered has an invalid syntax….you may have entered an operand
without an operator
Click to expand...


Where are you putting this expression? It's missing the usual leading "="
sign for a controlsource expression, and it's missing some left-brackets (
[ ). Maybe this:

=IIf ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
[Certification Date]), IIf ([Certification Frequency] = “Monthlyâ€, DateAdd
(“mâ€, 1, [Certification Date]), IIf ([Certification Frequency] = “Quarterlyâ€,
DateAdd(“qâ€, 1, [Certification Date]), IIf([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, [Certification Date]), [Certification
Date]))))


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Yappy said:
Thanks for your help. I am putting this into a text box called New Cert
Due
on a form. My Certification Frequency is a combo box with the choices of
Annual, Semi-Annual, Quarterly, and Monthly. I want it to calculate the
date
based on the Certification Date and the Frequency chosen.

I tried your expression and it is returning the following: #Name?
Click to expand...

So you've gotten past the syntax error? Hmm, a #Name error ... is there a
field in your form's recordsource table named "New Cert Due"? If a control
has the same name as a field, it must be bound to that field, so if "New
Cert Due" is the name of a field, you can't have a calculated control with
that name.
 
Dirk,
I figured it out. The solution is:

=IIf([Certification Frequency]="Annual",DateAdd("yyyy",1,[Certification
Date]), IIf([Certification
Frequency]="SemiAnnual",DateAdd("m",6,[Certification Date]),
IIf([Certification Frequency]="Quarterly",DateAdd("q",1,[Certification
Date]), IIf([Certification Frequency]="Monthly",DateAdd("m",1,[Certification
Date])))))

Thanks again!

Yappy said:
Thanks for your help. I am putting this into a text box called New Cert Due
on a form. My Certification Frequency is a combo box with the choices of
Annual, Semi-Annual, Quarterly, and Monthly. I want it to calculate the date
based on the Certification Date and the Frequency chosen.

I tried your expression and it is returning the following: #Name?

Is there something that I may be doing incorrectly?

Dirk Goldgar said:
Yappy said:
The following expression results in an error telling me that I may have
entered an operand without an operator. What ami I missing?

Iif ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
Certification
Date]), Iif ([Certification Frequency] = “Monthlyâ€, DateAdd (“mâ€, 1,
Certification Date]), Iif ([Certification Frequency] = “Quarterlyâ€,
DateAdd
(“qâ€, 1, Certification Date]), Iif ([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, Certification Date]), [Certification
Date]))))


Expression entered has an invalid syntax….you may have entered an operand
without an operator
Click to expand...


Where are you putting this expression? It's missing the usual leading "="
sign for a controlsource expression, and it's missing some left-brackets (
[ ). Maybe this:

=IIf ([Certification Frequency] = “Annualâ€, DateAdd (“yyyyâ€, 1,
[Certification Date]), IIf ([Certification Frequency] = “Monthlyâ€, DateAdd
(“mâ€, 1, [Certification Date]), IIf ([Certification Frequency] = “Quarterlyâ€,
DateAdd(“qâ€, 1, [Certification Date]), IIf([Certification Frequency] =
“Semi-Annualâ€, DateAdd (“mâ€, 6, [Certification Date]), [Certification
Date]))))


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Yappy said:
Dirk,
I figured it out. The solution is:

=IIf([Certification Frequency]="Annual",DateAdd("yyyy",1,[Certification
Date]), IIf([Certification
Frequency]="SemiAnnual",DateAdd("m",6,[Certification Date]),
IIf([Certification Frequency]="Quarterly",DateAdd("q",1,[Certification
Date]), IIf([Certification
Frequency]="Monthly",DateAdd("m",1,[Certification
Date])))))
Click to expand...

Very good. It looks like I let some spaces creep into the expression I
posted, where they didn't belong, and I may have been short a closing
parenthesis. Good job figuring it out!
 
Back
Top