If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expression using Iif with dates
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! |
#2
|
|||
|
|||
Expression using Iif with dates
"Yappy" wrote in message
... 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 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) |
#3
|
|||
|
|||
Expression using Iif with dates
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" wrote: "Yappy" wrote in message ... 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 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) |
#4
|
|||
|
|||
Expression using Iif with dates
"Yappy" wrote in message
... 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? 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 Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
Expression using Iif with dates
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" wrote: 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" wrote: "Yappy" wrote in message ... 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 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) |
#6
|
|||
|
|||
Expression using Iif with dates
"Yappy" wrote in message
... 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]))))) 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! -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|