IIF


K

kmv

I have a query with date from 2004 and 2005 based on my table. I want my
query to return a value of either 2004 or 2005. I tried writing the the
following expression and it is not working. Can anyone tell me what I am
doing wrong.

Expr1: IIf(
![date]>12/31/5,"2005","2004")

When I run this - it ask me to Enter a Parameter Value. I just want the
field to say either 2004 or 2005 based on the date.

kmv
 
Ad

Advertisements

B

Brendan Reynolds

The following example shows how to do this using the IIf function, and a
simpler solution using the Year function. (I had to change the dates because
I used Northwind for testing, and Northwind doesn't include any records with
dates in 2004 or 2005).

SELECT IIf([OrderDate]>#12/31/1997#,"1998","1997") AS OrderYear1,
Year([OrderDate]) AS OrderYear2
FROM Orders
WHERE (((Orders.OrderDate)>#12/31/1996#));
 
Ad

Advertisements

J

John Vinson

I have a query with date from 2004 and 2005 based on my table. I want my
query to return a value of either 2004 or 2005. I tried writing the the
following expression and it is not working. Can anyone tell me what I am
doing wrong.

Expr1: IIf(
![date]>12/31/5,"2005","2004")

When I run this - it ask me to Enter a Parameter Value. I just want the
field to say either 2004 or 2005 based on the date.

kmv


One of your problems is that you're using the meaningless (to Access)
syntax
! in your expression. If there is a field named [date]
in the query, just use it; if there are multiple fields named [date]
you need the actual table name, not 'table'.

Secondly, /5 is not a valid Date/Time field value. You need a two or
four digit year.

Thirdly, if [date] is a Date/Time field you need the # delimiter:

Iif([date] >#12/31/05#,

If it's a Text field, maybe with this very odd syntax where the year
2005 starts on January 1 2006, you need to enclose it in quotes, and
probably do some parsing since the text string "2/1/1850" is in fact
greater (alphabetically) than the text string "12/31/5".

Finally, [date] is a dangerous choice of fieldname, since it's a
reserved word for the builtin Date() function.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIf 2
Limits of IIF? 4
IIf statement 3
IIF statement?? 2
Problem with IIf 12
IIf statements 3
iif statement 4
IIF Function 2

Top