problem with "between" in an iif statement

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

Guest

I am trying to creat a new field [cp] that is an integer based on the data of
records in a database. If i write the query as:
CP:IIF([date] >5/29/2002,1)
then the query works fine and returns a 1 for all observations that have a
date < 5/29/2002. However if i want to write:
CP:IIF([date] between 5/29/2002 and 6/17/2002,1)
Then the query returns blank cells for the field CP, even though there are
definitely dates within that range.
I get the feeling I am doing something wrong that might be quite simple to
fix, but can not find anything in the help menu and ACCESS does not give me
any error messages.
 
I am trying to creat a new field [cp] that is an integer based on the data of
records in a database. If i write the query as:
CP:IIF([date] >5/29/2002,1)
then the query works fine and returns a 1 for all observations that have a
date < 5/29/2002. However if i want to write:
CP:IIF([date] between 5/29/2002 and 6/17/2002,1)
Then the query returns blank cells for the field CP, even though there are
definitely dates within that range.
I get the feeling I am doing something wrong that might be quite simple to
fix, but can not find anything in the help menu and ACCESS does not give me
any error messages.
Click to expand...

Regarding ... CP:IIF([date] >5/29/2002,1)
Access is reading the 5/29/2002 as a variable name. Since you haven't
given the variable a value, access assumes it's value is Zero.
All dates will then be greater than 0 and your expression will always
evaluate to True, and return a value of 1.

You must surround dates with the date delimiter symbol #.
CP:IIF([date] between #5/29/2002# and #6/17/2002#,1)
should then evaluate the dates properly.
Note: You should add a False value to the expression.
CP:IIF([date] between #5/29/2002# and #6/17/2002#,1,"")

Also ... Date is a reserved Access/VBA/Jet word and should not be
used as a field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
Several observations:
1. Do NOT use date as the name of a table field. Date is a VB function. Do
not name your fields with names that have special meaning to Access or VB.
Besides which, date does not tell you what the field is the date of.
2. IIF() syntax requires 3 expressions: the expression to evaluate, a true
value and a false value. So unless these are typos (or my NG Reader is
wacky), the first of these should read: Iif ([SomeDate] > 5/29/2002, 1,
<something else>)

Before adding the 3 argument to the IIF, I got the same results as you did.
The CP column was blank. Correct the syntax and it should work just fine.
 
Actually 5/29/2002 is being interpreted as 5 divided by 29 divided by 2002.
This is a time on December 30, 1899. Every date in your table is probably
greater than a date in the 19th century.

--
Duane Hookom
MS Access MVP


fredg said:
I am trying to creat a new field [cp] that is an integer based on the
data of
records in a database. If i write the query as:
CP:IIF([date] >5/29/2002,1)
then the query works fine and returns a 1 for all observations that have
a
date < 5/29/2002. However if i want to write:
CP:IIF([date] between 5/29/2002 and 6/17/2002,1)
Then the query returns blank cells for the field CP, even though there
are
definitely dates within that range.
I get the feeling I am doing something wrong that might be quite simple
to
fix, but can not find anything in the help menu and ACCESS does not give
me
any error messages.
Click to expand...

Regarding ... CP:IIF([date] >5/29/2002,1)
Access is reading the 5/29/2002 as a variable name. Since you haven't
given the variable a value, access assumes it's value is Zero.
All dates will then be greater than 0 and your expression will always
evaluate to True, and return a value of 1.

You must surround dates with the date delimiter symbol #.
CP:IIF([date] between #5/29/2002# and #6/17/2002#,1)
should then evaluate the dates properly.
Note: You should add a False value to the expression.
CP:IIF([date] between #5/29/2002# and #6/17/2002#,1,"")

Also ... Date is a reserved Access/VBA/Jet word and should not be
used as a field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Click to expand...
 
Back
Top