How to if in a queary

[Guest]

I'm trying to create a query which calculates an employee's pay, but their
rate depends on the date. I've created one table with employees' names, fall
rate, and spring rate. In the query, I'm trying to create a column, Rate,
which will return the appropriate rate according to the date. I've tried the
following, which didn't work:
Rate: IIf([Date]<1/1/2006,[Fall Rate],[Spring Rate])

Is it possible to create my desired result, and if so how?

Thanks in advance for your help.

 

[Al Camp]

Try...
Rate: IIf([Date]<#1/1/2006#,[Fall Rate],[Spring Rate])

Literal dates and Times must be identified by # before and after.
If you think about it, 1/1/2006 indicates 1 divided by 1 divided by 2006.

Also, never name a Date field [Date]. It's a reserved word in Access. Use
something more descriptive... like [SalesDate] or [DateHired], etc...

 

[Guest]

I tried that, but still no luck. : (
I used the expression bulider to get:
IIf([Classes by Date]!Date<#1/1/2006#,[Certified Staff]![Fall
Rate],[Certified Staff]![Spring Rate])

I keep getting the error "You tried to execute a query that does not include
the specified expression <above> as part of an aggregate function.

Try...
Rate: IIf([Date]<#1/1/2006#,[Fall Rate],[Spring Rate])

Literal dates and Times must be identified by # before and after.
If you think about it, 1/1/2006 indicates 1 divided by 1 divided by 2006.

Also, never name a Date field [Date]. It's a reserved word in Access. Use
something more descriptive... like [SalesDate] or [DateHired], etc...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I'm trying to create a query which calculates an employee's pay, but their
rate depends on the date. I've created one table with employees' names,
fall
rate, and spring rate. In the query, I'm trying to create a column, Rate,
which will return the appropriate rate according to the date. I've tried
the
following, which didn't work:
Rate: IIf([Date]<1/1/2006,[Fall Rate],[Spring Rate])

Is it possible to create my desired result, and if so how?

Thanks in advance for your help.

 

[Al Camp]

You must be doing a totals query.
If so, your expression must use one of the aggregate functions, like
Max(YourDate) or Min(YourDate) etc...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I tried that, but still no luck. : (
I used the expression bulider to get:
IIf([Classes by Date]!Date<#1/1/2006#,[Certified Staff]![Fall
Rate],[Certified Staff]![Spring Rate])

I keep getting the error "You tried to execute a query that does not
include
the specified expression <above> as part of an aggregate function.

Try...
Rate: IIf([Date]<#1/1/2006#,[Fall Rate],[Spring Rate])

Literal dates and Times must be identified by # before and after.
If you think about it, 1/1/2006 indicates 1 divided by 1 divided by 2006.

Also, never name a Date field [Date]. It's a reserved word in Access.
Use
something more descriptive... like [SalesDate] or [DateHired], etc...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I'm trying to create a query which calculates an employee's pay, but
their
rate depends on the date. I've created one table with employees'
names,
fall
rate, and spring rate. In the query, I'm trying to create a column,
Rate,
which will return the appropriate rate according to the date. I've
tried
the
following, which didn't work:
Rate: IIf([Date]<1/1/2006,[Fall Rate],[Spring Rate])

Is it possible to create my desired result, and if so how?

Thanks in advance for your help.

 

[Douglas J. Steele]

Either that, or you need to repeat the expression in the Group By clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You must be doing a totals query.
If so, your expression must use one of the aggregate functions, like
Max(YourDate) or Min(YourDate) etc...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I tried that, but still no luck. : (
I used the expression bulider to get:
IIf([Classes by Date]!Date<#1/1/2006#,[Certified Staff]![Fall
Rate],[Certified Staff]![Spring Rate])

I keep getting the error "You tried to execute a query that does not
include
the specified expression <above> as part of an aggregate function.

Try...
Rate: IIf([Date]<#1/1/2006#,[Fall Rate],[Spring Rate])

Literal dates and Times must be identified by # before and after.
If you think about it, 1/1/2006 indicates 1 divided by 1 divided by
2006.

Also, never name a Date field [Date]. It's a reserved word in Access.
Use
something more descriptive... like [SalesDate] or [DateHired], etc...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I'm trying to create a query which calculates an employee's pay, but
their
rate depends on the date. I've created one table with employees'
names,
fall
rate, and spring rate. In the query, I'm trying to create a column,
Rate,
which will return the appropriate rate according to the date. I've
tried
the
following, which didn't work:
Rate: IIf([Date]<1/1/2006,[Fall Rate],[Spring Rate])

Is it possible to create my desired result, and if so how?

Thanks in advance for your help.