Data MisMatch error on code?

**d9pierce1** · 09-13-2025, 06:45 AM

CSZ Test.zip

Hi all,
Data Base Attached, Opens to frmZip, enter Zipcode and Tab. If Zip does not exist or has more then one, it will open another form. If does exist then hit edit button and will open form.

I am trying to get this to add to different tables (See Code) if it doesnt exist. I have tried everything and I get a mis match error.... Have no idea and could use some assistance please

Code:

Option Compare Database
Option Explicit




Private Sub CmdSet_Click()


    If IsNull(TxtCity) Or IsNull(TxtState) Or IsNull(TxtZipCode) Then
            MsgBox "You must enter City, State & ZipCode, County is Optional"
            Exit Sub
    End If
    
        Dim strSQL As String
        Dim db As DAO.Database
        Set db = CurrentDb
        
    'See if ZipCode Exist, If not add to table
       If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode=" & Me.TxtZipCode)) = 0 Then
     strSQL = "INSERT INTO tblSysZipCode (ZipCode) " & "VALUES ( " & TxtZipCode & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
        
    'See if City Exist, If not add to table
    ElseIf Not IsNull(DLookup("City", "tblSysCity", "City=" & Me.TxtCity)) = 0 Then
     strSQL = "INSERT INTO tblSysCity (City) " & "VALUES ( " & TxtCity & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
        
    'See if State Exist, If not add to table
    ElseIf Not IsNull(DLookup("State", "tblSysState", "State=" & Me.TxtState)) = 0 Then
     strSQL = "INSERT INTO tblSysState (State) " & "VALUES ( " & TxtState & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
        
    'See if County Exist, If not add to table
    ElseIf Not IsNull(DLookup("County", "tblSysCounty", "County=" & Me.TxtCounty)) = 0 Then
     strSQL = "INSERT INTO tblSysState (County) " & "VALUES ( " & TxtCounty & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
    Else
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCity = TxtCity
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtState = TxtState
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCounty = TxtCounty
        DoCmd.Close acForm, "frmSysZipLookUpBusiness", acSaveYes
    
    End If
  
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ZC As String
ZC = Nz(Me.TxtZipCode, "")
    If Nz(DCount("ZipCode", "tblSysCSZ", "ZipCode='" & ZC & "'"), 0) <> 0 Then Exit Sub
        Primary = True
End Sub

When I type in a new Zip,City, state and county, it does put the zip in the correct table but give error????
Thanks

Dave

**Welshgasman** · 09-13-2025, 07:00 AM

I just managed to add 71047 ok.
You have been using Access long enough to be able to recognise this problem.

zipcode is text, so you need at least single quotes around it, as you will NEVER get a single quote in a zip code.

Code:

       If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) = 0 Then
     strSQL = "INSERT INTO tblSysZipCode (ZipCode) " & "VALUES ( " & TxtZipCode & ")"
        Debug.Print strSQL

DEbug.print

Code:

INSERT INTO tblSysZipCode (ZipCode) VALUES ( 71047)

I would not be doing If/Else as all three were missing from 71047 and now with that correction you have only added the zipcode?

What I normally advise

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything
Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

**Welshgasman** · 09-13-2025, 07:07 AM

Why do you have this in frmZip?

Code:

ID = Nz(DLookup("CSZID", "tblSysCSZ", "ZipCode=""" & TxtZip & """"), 0)

so that takes into account txtZip is text?

**d9pierce1** · 09-13-2025, 07:40 AM

Hi Welshgasman,
First off, thank you
You are correct I should know however I dont do this enough to remember all these things.... Sorry about that.
I changed the code and again, i got an error 128 at city level, two few parameters...
It did put the zipcode in, but no city state or county. If not using the If/ElseIf, how can i get these to fire?

Code:

Private Sub CmdSet_Click()


    If IsNull(TxtCity) Or IsNull(TxtState) Or IsNull(TxtZipCode) Then
            MsgBox "You must enter City, State & ZipCode, County is Optional"
            Exit Sub
    End If
    
        Dim strSQL As String
        Dim db As DAO.Database
        Set db = CurrentDb
        
    'See if ZipCode Exist, If not add to table
    If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) = 0 Then
     strSQL = "INSERT INTO tblSysZipCode (ZipCode) " & "VALUES ( " & TxtZipCode & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
        
    'See if City Exist, If not add to table
    ElseIf Not IsNull(DLookup("City", "tblSysCity", "City='" & Me.TxtCity & "'")) = 0 Then
     strSQL = "INSERT INTO tblSysCity (City) " & "VALUES ( " & TxtCity & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing


    'See if State Exist, If not add to table
    ElseIf Not IsNull(DLookup("State", "tblSysState", "State='" & Me.TxtState & "'")) = 0 Then
     strSQL = "INSERT INTO tblSysState (State) " & "VALUES ( " & TxtState & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
   
    'See if County Exist, If not add to table
    ElseIf Not IsNull(DLookup("County", "tblSysCounty", "County='" & Me.TxtCounty & "'")) = 0 Then
     strSQL = "INSERT INTO tblSysState (County) " & "VALUES ( " & TxtCounty & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
        
     Else
        
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCity = TxtCity
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtState = TxtState
        'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCounty = TxtCounty
        DoCmd.Close acForm, "frmSysZipLookUpBusiness", acSaveYes
    
    End If
    
  
End Sub

From Immediate Window

Code:

INSERT INTO tblSysZipCode (ZipCode) VALUES ( 23434)
INSERT INTO tblSysCity (City) VALUES ( Testing)

I entered Zip (23434) & City (Testing) & State (TT) & County (Tester) just for a test....
Inserted zip fine, then got error 128...
I also tried getting rid of the ElseIf and did IF and End If after each and still same error?

**d9pierce1** · 09-13-2025, 07:44 AM

Originally Posted by Welshgasman

Why do you have this in frmZip?

Code:

ID = Nz(DLookup("CSZID", "tblSysCSZ", "ZipCode=""" & TxtZip & """"), 0)

so that takes into account txtZip is text?

That is a temp form I made to simulate a form on my real db, It basically when entering an address, you type in zipcode and it either puts the City, St, and County in the form or opens up the Look Up form if 0 or more than one to select or add.
That works real well, I just need to add the new Zip,City,St,Counnty if not in the other tables when adding a new record.

**davegri** · 09-13-2025, 07:57 AM

Your ELSEIFs are not needed. It restricts the update to a single column, not checking the others. And as WGM states you need the delimiters. Also make allowance for optional county.

Code:

Private Sub CmdSet_Click()
    If IsNull(TxtCity) Or IsNull(TxtState) Or IsNull(TxtZipCode) Then
        MsgBox "You must enter City, State & ZipCode, County is Optional"
        Exit Sub
    End If
    
    Dim strSQL As String
    Dim db As DAO.Database
    Set db = CurrentDb
        
    'See if ZipCode Exist, If not add to table
    If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) = 0 Then
        strSQL = "INSERT INTO tblSysZipCode (ZipCode) " & "VALUES (" & TxtZipCode & ")"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
    End If
    'See if City Exist, If not add to table
    If Not IsNull(DLookup("City", "tblSysCity", "City='" & Me.TxtCity & "'")) = 0 Then
        strSQL = "INSERT INTO tblSysCity (City) " & "VALUES ('" & TxtCity & "')"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
    End If
    'See if State Exist, If not add to table
    If Not IsNull(DLookup("State", "tblSysState", "State='" & Me.TxtState & "'")) = 0 Then
        strSQL = "INSERT INTO tblSysState (State) " & "VALUES ('" & TxtState & "')"
        Debug.Print strSQL
        db.Execute strSQL, dbFailOnError
    End If
    'See if County Exist, If not add to table
    If Not IsNull(DLookup("County", "tblSysCounty", "County='" & Me.TxtCounty & "'")) = 0 Then
        strSQL = "INSERT INTO tblSysCounty (County) " & "VALUES ('" & TxtCounty & "')"
        Debug.Print strSQL
        If TxtCounty <> "" Then db.Execute strSQL, dbFailOnError
    End If
    
    'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCity = TxtCity
    'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtState = TxtState
    'Forms!frmBusiness.sfrmBusinessAddress.Form.TxtCounty = TxtCounty
    Set db = Nothing
    DoCmd.Close acForm, "frmSysZipLookUpBusiness", acSaveYes
End Sub

Another problem, since County is optional, form frnZip needs this NZ

Code:

    If ZC = 1 Then
        ' ZIP code exists and there's only ONE record
        ID = Nz(DLookup("CSZID", "tblSysCSZ", "ZipCode=""" & TxtZip & """"), 0)
        sCity = DLookup("City", "tblSysCSZ", "CSZID=" & ID)
        sState = DLookup("State", "tblSysCSZ", "CSZID=" & ID)
        'county is optional so might be missing
        sCounty = Nz(DLookup("County", "tblSysCSZ", "CSZID=" & ID), "")
    Else

**d9pierce1** · 09-13-2025, 08:05 AM

Thank you Davegri
That worked perfect. Many thanks to all
I will mark this solved...
Someday I will get this down, LOL, I hope

**Micron** · 09-13-2025, 08:32 AM

Does this really work if a zip code is not found? It doesn't make sense to me.
If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) = 0 Then

DLookup returns Null if the criteria finds no record or the domain contains no records. Since you seem to be looking up zip codes, how could a valid result ever equal zero? If it returns Null, that is not equal to zero either. Should it be

If IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) Then

**Welshgasman** · 09-13-2025, 08:44 AM

Originally Posted by d9pierce1

That is a temp form I made to simulate a form on my real db, It basically when entering an address, you type in zipcode and it either puts the City, St, and County in the form or opens up the Look Up form if 0 or more than one to select or add.
That works real well, I just need to add the new Zip,City,St,Counnty if not in the other tables when adding a new record.

No, what I meant is that there, you realise you need quotes, though you are using double double quotes instead of the single quotes, which is a good idea if the control/variable could ever have a single quote in it like "O'Hare".

**d9pierce1** · 09-14-2025, 04:56 AM

Originally Posted by Micron

Does this really work if a zip code is not found? It doesn't make sense to me.
If Not IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) = 0 Then

DLookup returns Null if the criteria finds no record or the domain contains no records. Since you seem to be looking up zip codes, how could a valid result ever equal zero? If it returns Null, that is not equal to zero either. Should it be

If IsNull(DLookup("ZipCode", "tblSysZipCode", "ZipCode='" & Me.TxtZipCode & "'")) Then

Hi Micron,
This code seems to work just fine. I have ran it over and over to test it and it does work.

Updated:
Hi Micron
I did change the code to your suggested method and it also works great. Thank you.
Just wanted to let you know!
Thanks
Dave

**Micron** · 09-14-2025, 09:43 AM

Good to know thanks. After further thought I think I see why it works:
When the value exists (e.g. 74035), and taking your use of parentheses into account
- do the lookup. The result will be 74035, so not Null.
- the IsNull test then returns False. Next, does False= 0? That is True.
- If statements ultimately end up being a test for a condition being True. Since Not(True) is False, then go to the next block of code
Reverse everything for when the value is not found by the lookup.

Kind of convoluted, yes? That can have you scratching your head months later if you have to edit or troubleshoot. Keeping with the thought that an If statement is a test for a condition being True, my suggestion just says
If IsNull(something) = True Then do what follows. You don't even need the = True part, so I left it off in my suggestion.

Data MisMatch error on code?

Thread Tools

Data MisMatch error on code?

Similar Threads

Code to change record data returns "data type mismatch in criteria"

Error Message on db.OpenRecordset (Compile error Type mismatch)

Error: Run-time error '13' Type mismatch

Data Type Mismatch Error In Criteria Expression : Error 2001

Error 13 Type Mismatch error

Posting Permissions