Query Formula error. Help!

[Debbie]

I entered this into a query. It says it is to complex or typed
incorrect. Can anyone give me some suggestions?

Thank you in advance.

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")>901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))>901),9,1),DateAdd("yyyy",1,[Date]))

 

[KARL DEWEY]

Do you have a field named Date? Poor if you do, can cause problems as it is
a reserved word.
Is it a DateTime datatype?

Format([Date],"mmdd") for a DateTime field will result in 1001 for today
(10/01/2007).
You have a single IIF statement but multiple evaluations withing it.
1- ([ClassroomID]="Kool Kidz" And (Format([Date],"mmdd")>901),
2- DateSerial(Year([Date])-(CLng(Format([Date],"mmdd"))>901),9,1),
The second one above should be results when the first is evaluated as true
but you have a bunch of extra stuff.

 

[Marshall Barton]

I entered this into a query. It says it is to complex or typed
incorrect. Can anyone give me some suggestions?

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")>901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))>901),9,1),DateAdd("yyyy",1,[Date]))

Sorry, but I can't quite figure out what you are trying to
accomplish with that. It sort of looks like you want to add
one year to the Kool Kidz date field values that are after
September 1st. If so, then try using:

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
Month([Date]) >= 9, DateSerial(Year([Date]) + 1, 9, 1),
DateAdd("yyyy",1,[Date])

 

[Debbie]

I entered this into a query. It says it is to complex or typed
incorrect. Can anyone give me some suggestions?

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")>901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))>901),9,1),DateAdd("yyyy",1,[Date]))

Sorry, but I can't quite figure out what you are trying to
accomplish with that. It sort of looks like you want to add
one year to the Kool Kidz date field values that are after
September 1st. If so, then try using:

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
Month([Date]) >= 9, DateSerial(Year([Date]) + 1, 9, 1),
DateAdd("yyyy",1,[Date])

I need the Kool Kidz to come up with 9/1 of the next year if [Date] is

9/2 (If paperwork was signed 9/28/07, then paperwork expires 9/1/08;

All other classes and Kool Kidz with a date signed before 9/2 expire
one year from [Date].

If paperwork was signed 8/28/07 then paperwork expires 8/28/08.
otherwise ALL classes add 1 year to [Date] I am using the formula in
a query.
Thanks again in advance.

 

[Marshall Barton]

I entered this into a query. It says it is to complex or typed
incorrect. Can anyone give me some suggestions?

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")>901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))>901),9,1),DateAdd("yyyy",1,[Date]))

Sorry, but I can't quite figure out what you are trying to
accomplish with that. It sort of looks like you want to add
one year to the Kool Kidz date field values that are after
September 1st. If so, then try using:

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
Month([Date]) >= 9, DateSerial(Year([Date]) + 1, 9, 1),
DateAdd("yyyy",1,[Date])

I need the Kool Kidz to come up with 9/1 of the next year if [Date] is

9/2 (If paperwork was signed 9/28/07, then paperwork expires 9/1/08;

All other classes and Kool Kidz with a date signed before 9/2 expire
one year from [Date].

If paperwork was signed 8/28/07 then paperwork expires 8/28/08.
otherwise ALL classes add 1 year to [Date]

Gee, I was closer than I thought ;-)

I think this is what you want:

Paperwork Expires: IIf([ClassroomID]="Kool Kidz" And
[Date] >= #9/1/2007#, #9/1/2008#,
DateAdd("yyyy",1,[Date])

but, with those hard coded dates, this won't work after
9/1/08

You really should follow Karl's advice about not using a
reserved word for a field name.