IIF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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#));
 
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
Click to expand...


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]
 
Back
Top