Date Query Question

[Nanette]

I'm trying to search data in the IWDateN field and pull out only the data
between the dates 12/1/06 and 12/31/06. If the date is what I want, I want
the row data put in the output, otherwise I don't want the row data.

I keep getting a Data Type Mismatch error.

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],"")

How do I correct this?

 

[kingston via AccessMonster.com]

Try this:
IIf(([IWDateN]>=#12/1/2006#) And ([IWDateN]<=#12/31/2006#),[IWDateN],"")
or IIf(([IWDateN] Between #12/1/2006# And #12/31/2006#),[IWDateN],"")

However, this will not work in a query's search criteria. For that you'll
need to pull the field [IWDateN] into the query output and use this for the
criteria:
Between #12/1/2006# And #12/31/2006#

I'm trying to search data in the IWDateN field and pull out only the data
between the dates 12/1/06 and 12/31/06. If the date is what I want, I want
the row data put in the output, otherwise I don't want the row data.

I keep getting a Data Type Mismatch error.

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],"")

How do I correct this?

 

[John Spencer]

Try using Null instead of "" in your IIF statement. If you use the ""
(zero-length string) Access will automatically cast IWDateN as a string.
Then when you apply date criteira against that you will get the Data Type
mismatch error

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],Null)

On the other hand, if this is supposed to be criteria then whey not just use

Field: IWDateN
Criteria: >=#12/1/2006# And <=#12/31/2006#

 

[Nanette]

Thanks John,

I'm still sort of new to this so I keep thinking in SQL when I should be
thinking in Field and Criteria. I put the date criteria as you suggested and
it worked.

Try using Null instead of "" in your IIF statement. If you use the ""
(zero-length string) Access will automatically cast IWDateN as a string.
Then when you apply date criteira against that you will get the Data Type
mismatch error

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],Null)

On the other hand, if this is supposed to be criteria then whey not just use

Field: IWDateN
Criteria: >=#12/1/2006# And <=#12/31/2006#

I'm trying to search data in the IWDateN field and pull out only the data
between the dates 12/1/06 and 12/31/06. If the date is what I want, I want
the row data put in the output, otherwise I don't want the row data.

I keep getting a Data Type Mismatch error.

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],"")

How do I correct this?

 

[Ken Sheridan]

If I could suggest one small amendment. Make the criteria:

= #12/1/2006# And [IWDateN] < #01/01/2007#

In SQL this translates to:

WHERE IWDateN >= #12/1/2006#
AND IWDateN < #01/01/2007#

The reason I suggest this is that the original approach will not pick up any
rows on the final day of the range where the IWDateN date/time value
contains a non-zero time of day. This is because #12/31/2006# does not mean
the whole of the day but the moment of time at midnight at the start of 31
December 2006 (in Access every there is no such thing as a date value per
se). Rows with non-zero times of day can quite easily creep in unnoticed
unless steps have been taken in the table design to allow only values with
zero times of day. The above amendment makes the query more bullet-proof as
it will pick up any such rows. Using a Between….And operation would also miss
any such rows BTW.

An alternative method, to return rows for the one month would be to add two
columns to the query in design view; Access will give them names like Expr1
and Expr2, but just leave those in place as the columns won't actually be
returned by the query:

Field: Year([IWDateN])
Show: Unchecked
Criteria: 2006

and:

Field: Month([IWDateN])
Show: Unchecked
Criteria: 12

In SQL this translates to:

WHERE YEAR(IWDateN) = 2006
AND MONTH(IWDateN) = 12

Ken Sheridan
Stafford, England

Thanks John,

I'm still sort of new to this so I keep thinking in SQL when I should be
thinking in Field and Criteria. I put the date criteria as you suggested and
it worked.

Try using Null instead of "" in your IIF statement. If you use the ""
(zero-length string) Access will automatically cast IWDateN as a string.
Then when you apply date criteira against that you will get the Data Type
mismatch error

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],Null)

On the other hand, if this is supposed to be criteria then whey not just use

Field: IWDateN
Criteria: >=#12/1/2006# And <=#12/31/2006#

I'm trying to search data in the IWDateN field and pull out only the data
between the dates 12/1/06 and 12/31/06. If the date is what I want, I want
the row data put in the output, otherwise I don't want the row data.

I keep getting a Data Type Mismatch error.

IIf([IWDateN]>=#12/1/2006# And <=#12/31/2006#,[IWDateN],"")

How do I correct this?