VBA Access SQL - Zip Code (9 Digit) Split to 4 DATA TYPE MISMATCH ERROR

Question

I currently have a system of importing an Excel into a table, which includes the Zip Code. The Zip Code has instances of 10 Characters (#####-####) - and sometimes just the first 5 Characters (#####).

I am looking for a way for it to detect the one's that have the 10 Characters with 4 Digit extension and place those 4 Characters in a different field. Below is What I have for my code. However, it is giving me a Data Type Mismatch.

Can anyone possibly help me to detect what is wrong? Both the Zip Code and the Zip Extension are Text Fields with default settings. BATCH_NO is simply the current import, and this condition works on all other parts of the import. So Unless I made a syntax error on BATCH_NO that isn't a problem.

Error I am currently getting: "Data Type Mismatch in criteria expression."

Dim tempsql As String

tempsql = "UPDATE Processing" & _
" SET [Zip Extension] = RIGHT([Zip Code],4)" & _
" WHERE (LEN([Zip Code]) > 5) AND (BATCH_NO = " & li_batch_no & ")"

CurrentDb.Execute tempsql

Are you sure there's a "-" in the zip codes and that they aren't just formatted to look that way? When you click in a cell with a long zip, is the dash there? Excel has a custom format that adds a dash. — Doug Glancy
Just triple checked my Excel input files - the values do indeed have a "-" in the zip codes values - which is what prevents me from having the fields as number fields (I think?) Previously I had inputs when the fields were set to numbers, now it at least imports them when set to Text. But considering my code - how would having a dash or not make a difference? The code is supposed to take the right 4 characters if the field is > 5 characters, so that shouldn't make a difference? Thank you for your help! — user2296381
@Mike - BATCH_NO is an AutoNumber from the Batch table that is imported/copied to the processing table under a Number Field. li_batch_no is an integer. — user2296381
@user2296381 is this code importing the data from excel or just processing the table? Are you sure this code fails not a different line? Can you show more code? — Mike

kiks73 kiks73 · Accepted Answer · 2013-05-31T09:20:10

Are you sure that li_batch_no is a string? Try replacing last line of your code with this:

" WHERE (LEN([Zip Code]) > 5) AND (BATCH_NO = " & cstr(li_batch_no) & ")"

VBA Access SQL - Zip Code (9 Digit) Split to 4 DATA TYPE MISMATCH ERROR

1 Answers