Datatype mismatch in criteria expression regarding a CDate function field.

[Kellen]

Hi this is my first post here.

I have this linked table query from a OBDC and I need to be able to filter out specific dates in that query. The dates in the table were in text format and I converted the dates using the CDate function. I wanted to filter the query to a single date and always I get the Datatype mismatch in criteria expression error.

However, filtering dates does work only when there are other specifications in the criteria fields (e.g. if I specify a date and and name).

This really frustrating and I'm at my limit here :banghead:

My SQL code in error looks like this:

SELECT
purch_hist.PUITM AS ITEM,
purch_hist.PUPO AS PO,
purch_hist.PUQTY AS QTY_RECEIVED,
CDate([purch_hist.PURDT]) AS RECEIPT_DATE,
itmcnt.ITBYR AS BUYER,
purch_hist.PUCST AS UNIT_COST,
vendor.NVNO AS VENDOR NO,
vendor.NVNM AS VENDOR,
purch_hist.PUCOMM AS COMMODITY

FROM
itmcnt INNER JOIN (purch_hist INNER JOIN NewVend ON purch_hist.PUVEN = NewVend.NVNO) ON itmcnt.ITITM = purch_hist.PUITM

GROUP BY
purch_hist.PUITM,purch_hist.PUPO, purch_hist.PUQTY, CDate([PURDT]), itmcnt.ITBYR,
purch_hist.PUCST, NewVend.NVNO, NewVend.NVNM, purch_hist.PUCOMM

HAVING (((CDate([purch_hist.PURDT]))=#6/16/2014#))


Thanks in advance!

P.S. This query works fine with a non converted date field, however the dates I need are in text format and need to be converted since I do not have permissions to edit the tables.

 

[CJ_London]

since you are only grouping and not summing/counting etc have you tried using SELECT DISTINCT instead?

SELECT DISTINCT
purch_hist.PUITM AS ITEM, 
purch_hist.PUPO AS PO, 
purch_hist.PUQTY AS QTY_RECEIVED, 
CDate([purch_hist.PURDT]) AS RECEIPT_DATE, 
itmcnt.ITBYR AS BUYER, 
purch_hist.PUCST AS UNIT_COST, 
vendor.NVNO AS VENDOR NO, 
NewVend.NVNM AS VENDOR, 
purch_hist.PUCOMM AS COMMODITY

FROM 
itmcnt INNER JOIN (purch_hist INNER JOIN NewVend ON purch_hist.PUVEN = NewVend.NVNO) ON itmcnt.ITITM = purch_hist.PUITM

WHERE CDate([purch_hist.PURDT])=#6/16/2014#

Also you appear to have a superflous semi colon

purch_hist.PUCST, NewVend.NVNO, NewVend.NVNM, purch_hist.PUCOMM;

HAVING (((CDate([purch_hist.PURDT]))=#6/16/2014#))

 

[MarkK]

Maybe one of the strings you are converting to a date is not a valid date? Try a query like . . .

SELECT Count(*) As InvalidDateCount 
FROM purch_hist
WHERE Not IsDate(PURDT);

 

[Kellen]

since you are only grouping and not summing/counting etc have you tried using SELECT DISTINCT instead?

SELECT DISTINCT
purch_hist.PUITM AS ITEM, 
purch_hist.PUPO AS PO, 
purch_hist.PUQTY AS QTY_RECEIVED, 
CDate([purch_hist.PURDT]) AS RECEIPT_DATE, 
itmcnt.ITBYR AS BUYER, 
purch_hist.PUCST AS UNIT_COST, 
vendor.NVNO AS VENDOR NO, 
NewVend.NVNM AS VENDOR, 
purch_hist.PUCOMM AS COMMODITY

FROM 
itmcnt INNER JOIN (purch_hist INNER JOIN NewVend ON purch_hist.PUVEN = NewVend.NVNO) ON itmcnt.ITITM = purch_hist.PUITM

WHERE CDate([purch_hist.PURDT])=#6/16/2014#

Also you appear to have a superflous semi colon

Sorry that was my bad. I added the WHERE clause after I posted and forgot to edit the semicolon out. The semicolon isn't the issue because it would bring up a different error.

I'm not at work right now but I'll try to see if your solution works.

 

[CJ_London]

This query works fine with a non converted date field

Are you quite sure it is a text field and not simply being displayed this way?

 

[Kellen]

Are you quite sure it is a text field and not simply being displayed this way?

I'm positive it's a text field. If the table creators would have made the receipt date field with a date/time property rather than a text property none of this would be happening.

 

[namliam]

Have you tried:
WHERE [purch_hist.PURDT]=#6/16/2014#

or

WHERE [purch_hist.PURDT] between #6/16/2014# and #6/17/2014#

or


WHERE int([purch_hist.PURDT]) between #6/16/2014# and #6/17/2014#

Just to exclude the posibility of it actually being a date?

 

[Kellen]

SELECT DISTINCT
purch_hist.PUITM AS ITEM, 
purch_hist.PUPO AS PO, 
purch_hist.PUQTY AS QTY_RECEIVED, 
CDate([purch_hist.PURDT]) AS RECEIPT_DATE, 
itmcnt.ITBYR AS BUYER, 
purch_hist.PUCST AS UNIT_COST, 
vendor.NVNO AS VENDOR NO, 
NewVend.NVNM AS VENDOR, 
purch_hist.PUCOMM AS COMMODITY

FROM 
itmcnt INNER JOIN (purch_hist INNER JOIN NewVend ON purch_hist.PUVEN = NewVend.NVNO) ON itmcnt.ITITM = purch_hist.PUITM

WHERE CDate([purch_hist.PURDT])=#6/16/2014#

I get the same error.

 

[CJ_London]

does the query work without any filtering? i.e. remove the where clause.

Just wondering where

vendor.NVNO AS VENDOR NO

comes from since vendor is not a table and you have a relationship

purch_hist.PUVEN = NewVend.NVNO

Also since there is a space in it you should have AS [VENDOR NO]

 

[Kellen]

Have you tried:
WHERE [purch_hist.PURDT]=#6/16/2014#

or

WHERE [purch_hist.PURDT] between #6/16/2014# and #6/17/2014#

or


WHERE int([purch_hist.PURDT]) between #6/16/2014# and #6/17/2014#

Just to exclude the posibility of it actually being a date?

6/16/2014 is a confirmed date and these gave blank results.

 

[Kellen]

does the query work without any filtering? i.e. remove the where clause.

Just wondering where

vendor.NVNO AS VENDOR NO

comes from since vendor is not a table and you have a relationship

purch_hist.PUVEN = NewVend.NVNO

Also since there is a space in it you should have AS [VENDOR NO]

Yes the query works fine without filtering. The query also filters everything else except date, unless another field is also being filtered.

Also I made another mistake writing this post, vendor.NVNO is NewVend.NVNO in my actual query. I changed the names of the table here to make things easier to read.

 

[CJ_London]

running out of ideas - can you post some sample data from the purch_hist table

 

[Kellen]

running out of ideas - can you post some sample data from the purch_hist table

That would be difficult. the purch_hist table has over 700,000 records and with almost 30 columns.

However the invalid date count query I ran produced 1, but it was with purch_hist.PURDT unconverted and running that query with CDate(purch_hist.PURDT) also gives a data mismatch error.

So either there's an invalid date somewhere or CDate is ruining my life.

 

[CJ_London]

more likely to be an invalid date.

What is the structure of the date at the moment?

01/01/2014
1st January 2014
01/01/2014 06:30:00
01/01/2014 6:30AM

or what?

 

[Kellen]

more likely to be an invalid date.

What is the structure of the date at the moment?

01/01/2014
1st January 2014
01/01/2014 06:30:00
01/01/2014 6:30AM

or what?

I'm not sure what you mean.

In text form: 06/16/2014
Converted with CDate: 6/16/2014

 

[CJ_London]

OK, take the simple option first and open the table and sort on the date column and check the first and last record to ensure they are a valid date format (and not blank)

If this finds the invalid date, job done

if not you will need to write a number of queries looking for invalid dates (e.g. month>13). for example, check for length - must be 10 chars long, check the / is in positions 3 and 6, check there are no alpha characters etc.

 

[Kellen]

OK, take the simple option first and open the table and sort on the date column and check the first and last record to ensure they are a valid date format (and not blank)

If this finds the invalid date, job done

if not you will need to write a number of queries looking for invalid dates (e.g. month>13). for example, check for length - must be 10 chars long, check the / is in positions 3 and 6, check there are no alpha characters etc.

I cant sort them because the dates I need in that table are in text format and I can't change that because I don't have the privilege of altering tables at all.

At best I can create a query and convert but that may change the invalid date.

 

[Kellen]

I think you may be right. There might be a null or invalid date since I saw a blank space for the unconverted dates and I can execute my converted date query, but using the group by function and sorting the date give a datatype mismatch in criteria expression error.

If this is the correct diagnosis then this is out of my hands and it's a problem for the people who control the ODBC.

Thanks.

 

[CJ_London]

you could try a simple query

SELECT * FROM purch_hist WHERE PURDT is Null

 

[MarkK]

Maybe have another look at my post #3. If the date is stored as a string, it can easily be an invalid date without being Null. Consider "31-Feb-2014#, which will cause CDate() to choke.

But check out the query in my post . . .