|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 1st, 2010, 09:39 PM
posted to microsoft.public.access
|
|||
|
|||
Data Type Mismatch in Criteria Expression
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 
|
|
#2
March 1st, 2010, 10:17 PM
posted to microsoft.public.access
|
|||
|
|||
|
Data Type Mismatch in Criteria Expression
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 JoeP wrote: 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
|
|
#3
March 1st, 2010, 10:31 PM
posted to microsoft.public.access
|
|||
|
|||
|
Data Type Mismatch in Criteria Expression
Try it this way --
Between #01/01/2010# and #01/31/2010# -- Build a little, test a little. "JoeP" wrote: 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
|
|
#4
March 13th, 2010, 05:56 PM
posted to microsoft.public.access
|
|||
|
|||
|
Data Type Mismatch in Criteria Expression
"JoeP" wrote in message ... 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
|
|
#5
March 17th, 2010, 01:34 PM
posted to microsoft.public.access
|
|||
|
|||
|
Data Type Mismatch in Criteria Expression
pùpj
"JoeP" a écrit dans le message de groupe de discussion : ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
