Query File Type Mismatch

[sarahrosenberg]

I am trying to link two tables that each contain an "Account Number" field. The two tables were data mined differently, and in one table this field is a Text field, and in the other table it is a Number field. This gives me a file type mismatch.

I though the obvious thing was to change the field format. But, if I tried to change Text to Number, it will delete about 1/3 of my records. When I change Number to Text, the numbers do not exaclty match up, because the Text field is always the 11 digit number even if there is a zero at the front to make it 11 digits. When I convert my Number to Text, the field is sometimes 10 digits and sometimes 11. I don't know how to force a zero at the front to make it 11 digits every time.

Thanks!

Sarah

 

[uriah]

You could do an if is length "LEN" = 10, "0" & the "field", else use the number
in a query. Or, you could convert both to 'values' using trim function
in query. I had that before and I had to have 4 expressions in my
query before I could get the exact match. (exp1,exp2,exp3,exp4)

Funky little formula. "iif" I think. I might have one somewhere if you can't find.

 

[uriah]

FYI.. SAMPLE OF FIRST EXPRESSION

Expr2: IIf(Len([RATE_CODE])>1,"A" & [RATE_CODE],[RATE_CODE2])

 

[Andrew Fergus]

Hi Sarah,

Following on from the suggestion from uriah, one way to do this might be to change the format of the account number field in the 2nd table to 'text'. Create a new query, make it an update query, add the account number field from the 2nd table, enter a criteria of : Len([Account_Number])<(11) and enter this in the 'update to' field : "0" & [Account_Number]. Be sure to use your actual field name where I have used 'Account_Number'. This will add leading zeroes to the account numbers that are 1 digit short and you can now link on this field.

If you are going to import more data into this field, then it may be safer to create a new account number field in the second table (formatted as text) but leave the orginal field alone. In your update query, you want to update the new account number field to something like this : IIf(Len([Account_Number])=11, [Account_Number], "0" & [Account Number]) {make sure you use your actual field names} You would then link on this field instead.

HTH, Andrew.