Datatype mismatch in criteria expression - query

[gravesk]

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

SELECT gibbons_starting_meter_list.Account15,
       gibbons_starting_meter_list.[Old Mtr Prfx],
       gibbons_starting_meter_list.[Old Mtr #],
       mv90_info.Channels,
       gibbons_starting_meter_list.[New Mtr Prfx],
       gibbons_starting_meter_list.[New Mtr #],
       gibbons_starting_meter_list.[Trucking Order #],
       gibbons_starting_meter_list.[Ship Date],
       mv90_info.LPC_Phone,
       gibbons_starting_meter_list.[Comm Type],
       gibbons_starting_meter_list.[Signal Strength],
       gibbons_starting_meter_list.[Signal Quality],
       gibbons_starting_meter_list.[New Equip?],
       gibbons_starting_meter_list.[Timers Date],
       gibbons_starting_meter_list.[LPDS Date],
       gibbons_starting_meter_list.[MExtra 1],
       gibbons_starting_meter_list.[MExtra 2],
       gibbons_starting_meter_list.[MExtra 3],
       gibbons_starting_meter_list.[MExtra 4],
       gibbons_starting_meter_list.[MExtra 5],
       gibbons_starting_meter_list.Comments

FROM   gibbons_starting_meter_list LEFT JOIN
       mv90_info
  ON   gibbons_starting_meter_list.[Old Mtr #] = mv90_info.Meter;

[TheSmileyCoder]

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_startin g_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.

[NeoPa]

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!

[gravesk]

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_startin g_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 #
7116190
7117022
7719119
7144228
7144239
19893
7424026


[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(M id([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:
Meter
90370107
40360309
6596820
6815256
6815261
87940
87941
87942



Thanks again for any help you folks can provide.

[NeoPa]

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

Did you run the queries separately? Were there any problems reported?

[TheSmileyCoder]

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(Le ft([RecorderID],3)="REC",Val(M id([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(M id([RecorderID],4,7)),Val([RecorderID]))) though Im a bit fuzy on that.

[NeoPa]

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

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.

[TheSmileyCoder]

I learned something today too then. Thank you. :)

[NeoPa]

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

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 :

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".

I learned something today too then. Thank you. :)

Always a pleasure. I frequently learn things and pick up tips from the other experts around here.

[gravesk]

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!

Thanks.

[NeoPa]

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

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.

[gravesk]

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

[NeoPa]

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.