By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,962 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,962 IT Pros & Developers. It's quick & easy.

Data type Mismatch and delimiting different data types

P: 23
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(month) as c
  2. FROM pool
  3. WHERE [reg] = "GP06NHL"
  4.   AND [litres] = "43.38"
  5.   AND [fuel] = "NORMAL UNLEADED"
  6.   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?

May 7 '09 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 635
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.

May 7 '09 #2

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

Thank You!
May 7 '09 #3

Expert Mod 15k+
P: 31,709

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!
May 8 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.