Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79

    Data Type Mismatch Error

    Hi everyone,

    Could someone glance at the code below very quickly and help me find where the mismatch is? I have tried both string and integer data types for the variable, to no avail. When I update the combo box, I get the data type mismatch error for some reason that I cannot figure out. I have been banging my head against a wall trying to find my mistake.
    Code:
    Private Sub Combo702_AfterUpdate()
        On Error GoTo Combo702Error
        
        Dim intX As Integer
    
        intX = "UPDATE tblIncidents SET ClassificationID = Null WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';"
        CurrentDb.Execute intX, dbFailOnError
      
        intX = "INSERT INTO tblIncidents (ClassificationID) SELECT ClassificationID FROM tblClassifications WHERE ClassificationID = '" & Me.Combo702 & "';"
       
        CurrentDb.Execute intX, dbFailOnError
     
    ExitCombo702:
        Exit Sub
    Combo702Error:
        MsgBox Err & ": " & vbCrLf & Err.Description
        Resume ExitCombo702
    End Sub


  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    intX is a string. So why are you declaring it as an Integer? You cannot set text values like that to an Integer.

  3. #3
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    I thought I needed to declare it as an integer because ClassificationID is a number and I thought it wouldn't work if declared as a string.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    intX is the whole string you are building, not just the ClassificationID.
    If you were just doing:
    Code:
    intX = Me.InternalIncidentID
    then maybe you would declare it as an Integer, but you are trying to put SQL code in intX.
    Any SQL code you are trying to build or store in a variable would require that variable to be declared a String.

  5. #5
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thank you JoeM. That makes sense now. However, for some odd reason I now get "Data type mismatch in criteria expression". Is there something I can do to fix that? In the combo box, I select a ClassificationID and the name. I display the name, and hide the ID so that the user can make a selection based on the name. Is that what is causing the error possibly?

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you referring to your first SQL statement?

    If so, if your IncidentID field is an Integer, you do NOT want single quotes around it in your SQL statement, as those are text qualifiers.
    You would have something like:
    Code:
        Dim intX As String
    
        intX = "UPDATE tblIncidents SET ClassificationID = Null WHERE InternalIncidentID = " & Me.InternalIncidentID & ";"

  7. #7
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Sorry about that, I should have clarified. When I change the combo box so that the update runs, that's when I get the error. The IncidentID field is a text field because the users manually enter it, but ClassificationID is a number field.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The IncidentID field is a text field because the users manually enter it, but ClassificationID is a number field.
    These Data Types of variables and fields really seem to be giving you issues! It is important to understand their implications.

    It doesn't really matter where the value comes from or how it is populated, all that matters is what the DataType of the InternalIncidentID field is in your table.
    If it is a number, then you do NOT want the text qualifiers in the Criteria section of the SQL code you are building.

  9. #9
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    That makes sense. Since it is not a number, then the code below should work but I still get the "data type mismatch in criteria" error. Is there something else I should change? I am positive that the InternalIncidentID field is a text field, yet it still doesn't seem to work.
    Code:
    Private Sub Combo702_AfterUpdate()
        On Error GoTo Combo702Error
        
        Dim intX As String
    
        intX = "UPDATE tblIncidents SET ClassificationID = Null WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';"
        CurrentDb.Execute intX, dbFailOnError
      
        intX = "INSERT INTO tblIncidents (ClassificationID) SELECT ClassificationID FROM tblClassifications WHERE ClassificationID = '" & Me.Combo702 & "';"
       
        CurrentDb.Execute intX, dbFailOnError
     
    ExitCombo702:
        Exit Sub
    Combo702Error:
        MsgBox Err & ": " & vbCrLf & Err.Description
        Resume ExitCombo702
    End Sub

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Without seeing your table structure, it is hard to say. But I will show you how I approach these.

    First, manually create a Query in Query Builder that does something like you want.
    So, build a query that Updates the ClassificationID field for an InternalIncidentID that you select.
    Test it, and when you have it working properly, switch the query to SQL View and Copy and Paste that SQL code out to a text file or document somewhere for you to reference.
    This is the structure of the VBA code that you are trying to build via SQL.

    Now, in your VBA code, after the row where you are building your SQL (the intX variable), and before the step where you are running it (CurrentDb.Execute...), add a MsgBox that returns the value of your SQL string to the screen, i.e.
    Code:
    MsgBox intX
    Now, if you test it and run the code, it should pop-up a message box. Compare the code there to the code you copied out to the other document. Is it structured exactly the same? If so, it should work. If not, adjust your code accordingly and try again.

  11. #11
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thank you for the help! It helped me created the code below, and the UPDATE statement runs perfectly but the INSERT INTO statement kicks back a 3061 error "Too Few Parameters. Expected 1." I have verified table names and column names, but is there something I'm missing here? I've looked up solutions for this online, but none of them have worked so far.
    Code:
    Private Sub Combo702_AfterUpdate()
        On Error GoTo Combo702Error
        
        Dim intX As String
      
        intX = "UPDATE tblIncidents SET tblIncidents.ClassificationID = NULL WHERE tblIncidents.InternalIncidentID = '" & Me.InternalIncidentID & "';"
        CurrentDb.Execute intX, dbFailOnError
        intX = "INSERT INTO tblIncidents (ClassificationID) SELECT tblClassifications_ClassificationID FROM tblClassifications INNER JOIN tblIncidents ON (tblClassifications.ClassificationID = tblIncidents.ClassificationID) WHERE tblClassifications.ClassificationID = '" & Me.Combo702 & "';"
        CurrentDb.Execute intX, dbFailOnError
    ExitCombo702:
        Exit Sub
    Combo702Error:
        MsgBox Err & ": " & vbCrLf & Err.Description
        Resume ExitCombo702
    End Sub

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That usually means that you have an unrecognized field or table name (usually a typo).

    This looks wrong to me:
    Code:
    SELECT tblClassifications_ClassificationID
    I believe it should be:
    Code:
    SELECT tblClassifications.ClassificationID

  13. #13
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    I thought so too, except that it gets rid of the data type mismatch error and gives me the parameter error when I have it as tblClassifications.ClassificationID instead of using the underscore.

    Basically the code below gives me a data type mismatch error
    Code:
    SELECT tblClassifications_ClassificationID
    while this gives me the parameter error.
    Code:
    SELECT tblClassifications.ClassificationID
    At the time I thought I was eliminating the one error and finding another, but perhaps my solution is causing the 2nd error?

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, so using the techniques I mentioned to you earlier today, were you able to manually create an "INSERT INTO" query example that worked?
    If so, can you please post that exact code here that you copied out of SQL View for it?

  15. #15
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is ClassificationID numeric, if not maybe: WHERE tblClassifications.ClassificationID = " & Me.Combo702

    Or seem like you can just use the combo702 value instead of linking the table since it is inserting that ID value and not another field in the table. Maybe:

    "INSERT INTO tblIncidents (ClassificationID) SELECT [Forms]![YourFORM]![Combo702] as Expr1;"

    I don't know, been a long day and seems like I am missing something but maybe gives you something to try.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. data type mismatch error
    By accessmatt in forum Queries
    Replies: 10
    Last Post: 09-24-2014, 05:43 PM
  2. Data type mismatch error
    By Tomfernandez1 in forum Programming
    Replies: 5
    Last Post: 10-05-2012, 07:27 AM
  3. Data type mismatch error
    By nigelbloomy in forum Programming
    Replies: 1
    Last Post: 08-01-2012, 09:19 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Data type mismatch error
    By AccessUser123 in forum Queries
    Replies: 1
    Last Post: 05-22-2011, 07:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums