Data type mismatch in criteria expression

[Mike Wilson]

Dear all,

I'm having difficulty trying to figure out what is causing the error "Data
type mismatch in criteria expression" in my Access .MDB.

Don't be put off - but I have a Visual Studio 2005 solution which queries an
Access database, and this error is sometimes thrown, and sometimes not. It
appears that this depends on what data is inside the database, but I can't
seem to pin down what precise data causes this scheme style error to be
thrown. I have managed to reproduce this error entirely in Access.

The code is:

SELECT Subcontractor_ID, SUM(Gross) AS Gross, SUM(DirectCostOfMaterials0dp)
AS DirectCostOfMaterials0dp, SUM(TotalAmountDeducted) AS
TotalAmountDeducted, SUM(Net) AS Net FROM (Payment) WHERE (PaymentDate >=
#01/01/2000#) AND (PaymentDate < #01/03/2008#) AND (Company_ID = 15) AND
(MonthlyReturn_ID IS NULL OR MonthlyReturn_ID = '') GROUP BY
Subcontractor_ID

I assume that this is something to do with the dates, but no matter how I
format it - it still doesn't work. I've put a copy of this mdb online, which
can be downloaded at: http://tinyurl.com/35kkoz (1.67mb).

All the data has been overwritten & fakes, but should work. The query is in
the database too.

Please help, as I'm tearing my hair out!

Mike

 

[KARL DEWEY]

If Company_ID is a text field then put double quotes around the 15.

I would use double quotes also in (MonthlyReturn_ID IS NULL OR
MonthlyReturn_ID = '') instead of single.

 

[Mike Wilson]

If Company_ID is a text field then put double quotes around the 15.

Thanks Karl,

It's a number, but there appears to be no difference between using double
quotes, single quotes or none.

I would use double quotes also in (MonthlyReturn_ID IS NULL OR
MonthlyReturn_ID = '') instead of single.

Changed to double quotes, still errors.

Mike

 

[KARL DEWEY]

Is your PaymentDate field a text or datetime field?
Is your PaymentDate field a text or number field? The quotes are
looking for zero lenght text.

If these are not the problem then work backwards removing criteria until it
works.

 

[Mike Wilson]

Is your PaymentDate field a text or datetime field?
Is your PaymentDate field a text or number field? The quotes are
looking for zero lenght text.

It's a DateTime field.

If these are not the problem then work backwards removing criteria until
it
works.

Good idea!

Removing the dates entirely still causes the query to fail. So I removed
the, (Or MonthlyReturn_ID='') too. That, it seems was the cause. Access did
not like making comparisons of a "Number" field in this way - yet, however,
for all other customers it was never a problem. Any ideas why this was?

*fap*

 

[KARL DEWEY]

I do not know for sure but I do not hink you can have a zero lenght number
field.

Try searching and find out if you can.

 

[Mike Wilson]

I do not know for sure but I do not hink you can have a zero lenght number
field.

Try searching and find out if you can.

Not sure either, but I found another way to express my SQL query.

Thank you very much for your suggestions, it was your suggestion that
started my mind ticking on the road to solution!

M