Problems with CDate in query

[LordHog]

Hello all,

I have a little application designed with MS Access 2003 which
connects to an Access 2000 database. One of the fields within a table
is called vss_date which is actually defined as a text field. I am
trying to obtain data using this vss_date field, but since it is
defined as a Text Field and not a Date Field the query is having
problems. The following is the query I have so far

SELECT VssDate, CodeSize
FROM (SELECT Format(vss_date, "MM/DD/YYYY") AS VssDate, code_size AS
CodeSize, associated_project as AssocPrj FROM swrelease WHERE vss_date
<> '' AND code_size IS NOT NULL )
WHERE AssocPrj = 33555595 AND CDate([VssDate]) < #12/1/2005#
ORDER BY vssDate DESC;

When I run this query I get a "Data type mismatch in criteria
expression". I figured this might because CDate is pulling a NULL or
Empty vss_date entry, but running the query

SELECT Format(vss_date, "MM/DD/YYYY") AS VssDate, code_size AS
CodeSize, associated_project as AssocPrj FROM swrelease WHERE vss_date
<> '' AND code_size IS NOT NULL

doesn't produce any result with a NULL or empty vss_date result. Now if
I type a literal string in the CDate function like

SELECT VssDate, CodeSize
FROM (SELECT Format(vss_date, "MM/DD/YYYY") AS VssDate, code_size AS
CodeSize, associated_project as AssocPrj FROM swrelease WHERE vss_date
<> '' AND code_size IS NOT NULL )
WHERE AssocPrj = 33555595 AND CDate("08/25/05 10:47a") < #12/1/2005#
ORDER BY vssDate DESC;

It works fine and returns a result that I expect. Does anyone know how
I can fix the query so it work correctly? I am at a lost at this point
in time. FYI: I don't think this has a bearing on the question, but the
SQL query string is built using VBA which is past to a ADODB Record Set
and this parsed. Right now I am testing the query within the Query
builder within Access trying to figure this out. Thanks.

Mark

 

[LordHog]

Hello all,

I figured I would try a different approach and convert the field to a
date at the subquery level like

SELECT VssDate, CodeSize, AssocPrj
FROM (SELECT CDate(vss_date) AS VssDate, code_size AS CodeSize,
associated_project AS AssocPrj FROM swrelease WHERE (vss_date IS NOT
NULL AND Len(vss_date) <> 0) AND code_size IS NOT NULL )
WHERE AssocPrj = 33555595 AND VssDate < #10/1/2005#
ORDER BY VssDate DESC;

but this produces the following error "Invalid use of Null", but if I
run the sub-query of

SELECT CDate(vss_date) AS VssDate, code_size AS CodeSize,
associated_project AS AssocPrj FROM swrelease WHERE (vss_date IS NOT
NULL AND Len(vss_date) <> 0) AND code_size IS NOT NULL

This does seems to return a good set of data so I am not sure why the
main query is having difficulties.

Mark

 

[Douglas J. Steele]

Even though you've got the vss_date IS NOT NULL in your WHERE clause, the
Len(vss_date) will still be evaluated for Null records.

You might use WHERE Nz(vss_date, 0) <> 0