Data Type Mismatch in Criteria Expression

J

JoeP

In Access 2003 I have a calculated field in a query that is converting a
string of numbers into a date format. The formula for doing this is...
TscDate:
DateSerial(Left([DateInput],4),Mid([DateInput],5,2),Right([DateInput],2)).

The formula works fine but when I enter criteria for the calculated field
(criteria example Between #2010/01/01# and #2010/01/31 I receive a Data Type
Mismatch in Criteria Expression. I am stumped about what I am doing wrong.
Any help would be appreciated. Thanks.

JoeP
 
J

John Spencer

I would try the following.

Field: RealDate:
IIF(IsDate(Format(DateInput,"@@@@-@@-@@")),CDate(Format(DateInput,"@@@@-@@-@@")),Null)

Criteria: Between #2010/01/01# and #2010/01/31#

Your problem could be that Dateinput is blank or null and DateSerial is
generating an error when it tries to process the zero-length strings you are
passing it.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
D

De Jager

JoeP said:
In Access 2003 I have a calculated field in a query that is converting a
string of numbers into a date format. The formula for doing this is...
TscDate:
DateSerial(Left([DateInput],4),Mid([DateInput],5,2),Right([DateInput],2)).

The formula works fine but when I enter criteria for the calculated field
(criteria example Between #2010/01/01# and #2010/01/31 I receive a Data
Type
Mismatch in Criteria Expression. I am stumped about what I am doing wrong.
Any help would be appreciated. Thanks.

JoeP
 

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

Top