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

Datatype mismatch in criteria expression - query

P: 13
Hello -

I am a novice in MS Access and do not know SQL. I am using Access 2003.

I have set up two queries that seem to be working fine. Each comes directly from a table. I have joined the two queries in a "include all records from A and only those records from B where the joined fields are equal" join.

The fields that I am using for the join are both fixed decimal, zero decimal place fields in their respective queries.

I am getting the "datatype mismatch in criteria expression" error, but have no idea how to go about de-bugging to track down the problem.

I gather from reading other posts that the problem may not even be in the join fields, but could be elsewhere. But how do I go about tracking this down?

The SQL code from the join is below.

thanks very much in advance,
Kristin Graves
Expand|Select|Wrap|Line Numbers
  1. SELECT gibbons_starting_meter_list.Account15,
  2.        gibbons_starting_meter_list.[Old Mtr Prfx],
  3.        gibbons_starting_meter_list.[Old Mtr #],
  4.        mv90_info.Channels,
  5.        gibbons_starting_meter_list.[New Mtr Prfx],
  6.        gibbons_starting_meter_list.[New Mtr #],
  7.        gibbons_starting_meter_list.[Trucking Order #],
  8.        gibbons_starting_meter_list.[Ship Date],
  9.        mv90_info.LPC_Phone,
  10.        gibbons_starting_meter_list.[Comm Type],
  11.        gibbons_starting_meter_list.[Signal Strength],
  12.        gibbons_starting_meter_list.[Signal Quality],
  13.        gibbons_starting_meter_list.[New Equip?],
  14.        gibbons_starting_meter_list.[Timers Date],
  15.        gibbons_starting_meter_list.[LPDS Date],
  16.        gibbons_starting_meter_list.[MExtra 1],
  17.        gibbons_starting_meter_list.[MExtra 2],
  18.        gibbons_starting_meter_list.[MExtra 3],
  19.        gibbons_starting_meter_list.[MExtra 4],
  20.        gibbons_starting_meter_list.[MExtra 5],
  21.        gibbons_starting_meter_list.Comments
  23. FROM   gibbons_starting_meter_list LEFT JOIN
  24.        mv90_info
  25.   ON   gibbons_starting_meter_list.[Old Mtr #] = mv90_info.Meter;
Mar 11 '10 #1
Share this Question
Share on Google+
12 Replies

Expert Mod 100+
P: 2,321
You have no WHERE clause in your SQL statement, so I would guess that it IS related to the join.

Your joining Field [Old Mtr #] from Table gibbons_starting_meter_list with Field Meter from table mv90_info

To start debugging this, I would suggest opening each of the tables in design view, and finding those Fields in each table, and checking the datatype. Its likely to be either String or number. If its Number, you also need to check what KIND of number it is, Byte, Integer, Long, Single, Double.

Report that back here, and we can advice you on how to proceed.
It would also help if you post a few entries from each of the joined fields, from each table, so we can tell you if its "safe" to change the datatype, in order to allow the join.
Mar 11 '10 #2

Expert Mod 15k+
P: 31,261
It's also conceivable that the error is actually coming from one or both of the subqueries.

Like Smiley, I'll request that you report back after you've tried running each of the subqueries individually.

Have fun and Welcome to Bytes!
Mar 11 '10 #3

P: 13
OK, here is a little more about my set-up and the format of my fields.

I start with two tables, read each of those tables into a query, and then the problem comes when I try to join the two queries into a third query.

Here is the information about the field formats for my two join fields:
[Old Mtr #] field
The starting point for the [Old Mtr #] field is table A. In table A, the field is called E_MTR_NO, and the format is number, long integer, fixed, 0 decimal places.

The query that reads table A is called gibbons_starting_meter_list. In that query, I re-name E_MTR_NO as follows: Old Mtr #: E_MTR_NO -- the format is fixed, 0 decimal places. Each record is populated with a non-zero value.

Some sample records follow:
Old Mtr #

[Meter] field
The starting point for the [Meter] field is table B. In table B, the field is called RecorderID, and it is a text field with the meter number embedded in it - I fix that in the query. The specific format is text (I don't see any other parameters assigned).

The query that reads table B is called mv90_info. In that query, I re-name and fix RecorderID as follows: Meter: IIf(Left([RecorderID],3)="REC",Val(Mid([RecorderID],4,7)),Val([RecorderID])) -- the format is fixed, 0 decimal places. There are quite a number of records that take a value of 0.

Sample records follow:

Thanks again for any help you folks can provide.
Mar 11 '10 #4

Expert Mod 15k+
P: 31,261
Did you run the queries separately? Were there any problems reported?
Mar 11 '10 #5

Expert Mod 100+
P: 2,321
If it was me, I would try to:
1) Add a new field ot table B, lets call it NewMeter, making it Number, Long Integer. Remember that Fixed and 0 decimal spaces, has nothing to do with the STORAGE of the value, only how its visually presented to the user.

2) Run a update Query on Table B, setting NewMeter=IIf(Left([RecorderID],3)="REC",Val(Mid([RecorderID],4,7)),Val([RecorderID]))

3) Then run the original query, joining on the new field.
The problem as I see it (could be wrong) is that your trying to join a String with a Number field.

It might also work in the original query if you do a conversion Meter:Clng(IIf(Left([RecorderID],3)="REC",Val(Mid([RecorderID],4,7)),Val([RecorderID]))) though Im a bit fuzy on that.
Mar 11 '10 #6

Expert Mod 15k+
P: 31,261
Although the original data is textual Smiley, the result after the Val() call is numeric. This is a typical way of linking two datasets where the originating data doesn't have linkable fields. It's the method I'd recommend in a similar situation.
Mar 11 '10 #7

Expert Mod 100+
P: 2,321
I learned something today too then. Thank you. :)
Mar 11 '10 #8

Expert Mod 15k+
P: 31,261
I missed this bit earlier. This is actually very close to what may be required (still waiting on some results).

If the data that is being linked to is actually long, then CLng() would be a better conversion function to use. Also, as you have it, the conversion function could happily be outside of (encapsulating) the whole IIf() function call. There is then no requirement to call it twice inside. Something like :
Expand|Select|Wrap|Line Numbers
  1. CLng(IIf(Left([RecorderID],3)='REC',Mid([RecorderID],4,7),[RecorderID]))
I presume this is here because some of the records preceed the number with the text "REC".
Always a pleasure. I frequently learn things and pick up tips from the other experts around here.
Mar 11 '10 #9

P: 13
Yes, I did run the two queries separately without problems. Everything appears to be fine when I do it separately.

I will try the CLng function as suggested. If CLng doesn't do it, then I'll try the new field and the update query (I don't even know what that is, but I'll look it up), and then the revised original query joining on the new field (all as suggested by thesmileyone.

I'll get back with results, but it won't be til Saturday.

Thanks again everyone!

Mar 11 '10 #10

Expert Mod 15k+
P: 31,261
Cool. Thanks for keeping us updated.

In view of your answer to my question, I would recommend the SQL posted in my previous post. I'd be interested to hear if that works for you. I can only assume that the Val() function was too non-specific to match the fields from the two sources.
Mar 12 '10 #11

P: 13
Hi all - I tried the CLng function as suggested by NeoPa, but it did not work.

I then looked into the three-step process suggested by TheSmileyOne. As I mentioned earlier, I am pretty inexperienced with Access. One thing that I had not mentioned previously is that my tables are imported from Excel worksheets. Moreover, the worksheets are refreshable (that is, with the click of a button, they update with the latest set of data from an external database). The way that I have the Access set up, we will periodically be refreshing the Excel worksheets and then importing them into Access, where we will be running the queries, forms, etc. This is a long-winded way of saying that it appeared to me to be easier to get the right formula for my meter number field in Excel than in Access. So, that's what I did. I extracted the numeric part of the number in Excel, used that field in Access, and it is working fine now.

I greatly appreciate the time that you folks spent helping me out, and learned quite a bit along the way about formats in Access, various functions, etc. so this was not a waste of anyone's time.

Thank you all so much for your help with this.

Kristin Graves
Mar 13 '10 #12

Expert Mod 15k+
P: 31,261
Thanks again for the update Kristin.

I can't see why the process wouldn't have worked as suggested, but there are always imponderables when dealing via a forum site. The important thing is that you're happy with your results.
Mar 15 '10 #13

Post your reply

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