if in a query

[Guest]

I tried posting my question in the newbee group. Perhaps, I'll have better
luck here. : )

I'm trying to create a query which has a column, rate, that pulls either the
"Fall rate" field or "Spring Rate" field from a staff table depending on the
date of a class. I used the expression builder to get the following, but I
keep getting an aggregate error:
Rate: IIf([Classes by Date]!Date<#1/1/2006#,[Certified Staff]![Fall
Rate],[Certified Staff]![Spring Rate])

I'm new to Access, so I don't even know if I'm putting the expression in the
right place, Field, or if queries can do if functions.

Thanks in advance for any help.

 

[Guest]

Looks like you are on the right track, but without the entire SQL, can't
really tell.

Please post the entire SQL for the query.

BTW: Stacy's Mom by Fountains of Wayne is one of my favorite songs.

 

[Guest]

St@cy,

I see you have a field named "Date" in that query.
This is trouble as Access can get confused internally since Date is also an
Access internal *and* a VBA reserved word. Using Date as a field name is
therefore problematic. It ususally will work as expected, but sometimes...

In general, it is best to avoid using "Date" as a field name. Try a more
descriptive name like "SignUpDate" or "StartDate", or "FirstDate", or even
"TheDate" etc.,.

If the data table schema is not something you can change, then try building
a query that simply renames that field into something else and includes all
the other table fields as-is (i.e. query field like
MyDateFieldNameHere:[MyTableNameHere].[Date])
....and use this query in every place where you would otherwise address the
table directly.