IIF

[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

 

[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#));

 

[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]