A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Expression using Iif with dates



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 04:31 PM posted to microsoft.public.access.gettingstarted
Yappy
external usenet poster
 
Posts: 7
Default 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  
Old April 20th, 2010, 04:37 PM posted to microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 21st, 2010, 12:57 PM posted to microsoft.public.access.gettingstarted
Yappy
external usenet poster
 
Posts: 7
Default 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  
Old April 21st, 2010, 03:18 PM posted to microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 21st, 2010, 03:42 PM posted to microsoft.public.access.gettingstarted
Yappy
external usenet poster
 
Posts: 7
Default 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  
Old April 21st, 2010, 04:18 PM posted to microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.