data type mismatch in criteria expression

[geebee]

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

 

[John Spencer]

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]

 

[Jerry Whittle]

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;