Data type Mismatch and delimiting different data types

[charli]

SQL:

SELECT COUNT(month) as c
FROM pool
WHERE [reg] = "GP06NHL"
  AND [litres] = "43.38"
  AND [fuel] = "NORMAL UNLEADED"
  AND  [month] = #01/04/2009#

Gives 'Data Type Mismatch' because the 43.38 is delimited as a string, when it is infact a number.

But, this sql is automatically generated (the columns names and data in the WHERE clause comes from another table), so the loop that generates it just delimits everything.

Is there a delimiter that works for text and numbers? Or is there a way with SQL to see the data type of the Access field so I can only add quotes into the string if needed? Or is this a horrible and wrong way to generate SQL?

Thanks
Charli

[MikeTheBike]

so the loop that generates it just delimits everything.

If this is true, why doesn't the date parameter have quote delimiters ??
Perhaps the date criteria is not held in the 'Criteria' table??

My suggestion is to add a FieldDataType field to the 'Criteria' Table so you can decide whether to add delimiters to the SQL (or not). This could be done using VBA or the query designer.

I haven't tried this but it should be feasible.

MTB

[charli]

Great plan! And it works too. Sometimes the simplest answers work the best!

Thank You!

[NeoPa]

Charli,

I (just today) posted some code in a thread (Multi-level Group BY clause is not allowed in a subquery) which illustrates some of this and gives some ideas of how to do some of this stuff. It's probably more involved than you need, but I thought you might like to cherry-pick bits of the code to suit your needs.

Welcome to Bytes!