data type mismatch in criteria expression

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

Guest

I have a query in which one of the columns is as follows:

expr1: CDate(Mid([activity date],3,1) & "/" & IIf(Right([activity date],2)
Like "0*",Right([activity date],1),Mid([activity date],4,2)) & "/" & "200" &
Left([activity date],1))

expr2: CDate(Mid([activity date],3,1) & "/" & IIf(Right([activity date],2)
Like "0*",Right([activity date],1),Mid([activity date],4,2)) & "/" & "200" &
Left([activity date],1))

when I run another new query with just these columns in it and a condition
of "between #7/1/2006# and #7/31/2006#" for [expr2], it runs fine without a
problem, but when I run my entire query with these columns in addition to the
added condition of "between [popenterdt] and [resolveddt] for [expr2], I get
the aforementioned error message.

Note that [popenterdt] and [resolveddt] are both date format columns.

What do I need to do? Should I add the following to the criteria for
[expr2]?:
between "#" & [popenterdt] & "#" AND "#" & [resolveddt] & "#"

Thanks in advance,
geebee
 
Access is not understanding the data type of your parameters in the where
clause. So, either force the type of your parameters (1) or declare the
parameters (2).

(1)
between CDate([popenterdt]) and CDate([resolveddt])

(2)
Parameters [popenterdt] DateTime, [resolveddt] DateTime;
SELECT ...
FROM ...
WHERE Expr2 between [popenterdt] and [resolveddt]
 
CDate will blow chunks if it runs into anything that it can not evaluate as a
valid date. I use the IsDate function first to ensure that no 'bad' dates get
in. If it's a one time job, run a query using the IsDate function on your
data and then fix the bad dates.

SELECT YourTable.*
FROM YourTable
WHERE (((IsDate([date]))=False));
Or
SELECT IIf(IsDate([date])=True,Cdate([date]),#1/1/1950#) AS NotDate
FROM YourTable;
 
Back
Top