Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39

    run-time error 13; type mismatch

    i have this code that updates back-end sql tables from a command button on ms access form. it fails run-time error code 13; type mismatch. when i remove the (AND '" & Not IsNull(Me.TicketNum1) & "') portion of the WHERE clause it runs fine. i added this to make sure to UPDATE only when there is a value in TicketNum1 field. I have no clue what is wrong with this. i am new and i have been trying to figure out how to write this. any help to move me forward on this project would help immensely.

    Dim dbCMC1 As DAO.Database
    Dim strSQL1 As String
    Set dbCMC1 = CurrentDb
    strSQL1 = "UPDATE dbo_FieldTicketHeader " & _


    "SET dbo_FieldTicketHeader.SONumber = '" & Me.NewSONumber & "' " & _
    "WHERE dbo_FieldTicketHeader.FieldTicketNumber = '" & Me.TicketNum1 & "' AND '" & Not IsNull(Me.TicketNum1) & "' "
    Debug.Print strSQL1
    dbCMC1.Execute strSQL1, dbFailOnError

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Surround it in an IF statement - If not isnull then create and run the sql (without the isnull)

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It means you're trying to use a string in. A numeric field.
    SONumber is probably a number,so don't surround the variable with single quotes.

    "Set SOnumber = " & me.txtNumber & " _

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have a great enthusiasm for using single quotes - they are only used for text strings

    SONumber and Ticketnumber sound like they are numbers, but perhaps not if your code is not failing on those

    but isnull returns a Boolean yes/no, which is actually a number>> 0=false and -1=true

    so change to

    ....& Me.TicketNum1 & "' AND " & Not IsNull(Me.TicketNum1)

  5. #5
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    btw......SONumber is an integer on back-end table and Ticketnumber is a text field. once i change it is throwing a syntax error. the following shows up in red or as where the problem lies i guess:


    strSQL1 = "UPDATE dbo_FieldTicketHeader " & _
    "SET dbo_FieldTicketHeader.SONumber = '" & Me.NewSONumber & "' " & _
    "WHERE dbo_FieldTicketHeader.FieldTicketNumber = '" & Me.TicketNum1 & "' AND " & Not IsNull(Me.TicketNum1) "







    Quote Originally Posted by Ajax View Post
    you have a great enthusiasm for using single quotes - they are only used for text strings

    SONumber and Ticketnumber sound like they are numbers, but perhaps not if your code is not failing on those

    but isnull returns a Boolean yes/no, which is actually a number>> 0=false and -1=true

    so change to

    ....& Me.TicketNum1 & "' AND " & Not IsNull(Me.TicketNum1)

  6. #6
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    also being a beginner is there a site or something i can look at that shows how to take normal SQL script and adapt it to work in vba. i mean i know SQL pretty well but when moving it into the vba code i am struggling. thx.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    This works fine and updates correctly to back-end tables when the WHERE clause does not contain the test at the end or this part:

    AND '" & Not IsNull(Me.TicketNum1) & "'

    so the SONumber being surrounded by single quotes does not seem to be the issue. again, it works fine without the NOT IsNull part and then fails when i try to include it.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What exactly are you trying to do with that? The result of the Debug.Print should show why it isn't working. Do you mean something like:

    ...AND TicketNum1 Is Not Null"

    You're evaluating a form control and using the True/False result by itself, which doesn't make sense.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    here is what i will run in SQL to move a Ticket to a new and different Sales Order:

    UPDATEFieldTicketHeader SET SONumber=1077342 WHERE TicketNum in('ELK465345')
    UPDATE FieldTicketInvoice SET SONumber=1077342 WHERE TicketNum in('ELK465345')
    UPDATE PayrollDetailTable SET SONumber=1077342 WHERE TicketNum in('ELK465345')


    basically moving tickets to a new Sales Order. i have to ensure the TicketNum (unbound textbox) is not null as i have multiple fields on the form as sometimes not all of them will have values in them. so i don't need a NULL value put in a SONumber field as it is NOT NULL. So it works fine until i add the NOT IsNull for the TicketNum fields.



    Quote Originally Posted by pbaldy View Post
    What exactly are you trying to do with that? The result of the Debug.Print should show why it isn't working. Do you mean something like:

    ...AND TicketNum1 Is Not Null"

    You're evaluating a form control and using the True/False result by itself, which doesn't make sense.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So test the textbox:

    Code:
    If Len(Me.TextboxName & vbNullString) = 0 Then
      'message to user
    Else
      'run your updates
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    without getting into a long explanation how would i rewrite what you have to if the textbox is null then don't run update and if not null then run update. just need something as easy as that but not sure how to revise what you have. thanks.


    Quote Originally Posted by pbaldy View Post
    So test the textbox:

    Code:
    If Len(Me.TextboxName & vbNullString) = 0 Then
      'message to user
    Else
      'run your updates
    End If

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Um, exactly that code. The test I used checks for both Null and a zero length string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    thanks. as i am a beginner and have inherited this but need to make it work.....is this code put in front of the entire update script or just in front of the sql string?

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your code would go in place of where it says:

    'run your updates
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Run-time error '13' Type mismatch
    By DCV0204 in forum Forms
    Replies: 4
    Last Post: 11-13-2013, 08:19 PM
  2. Run-time error 13 type Mismatch
    By Jaron in forum Programming
    Replies: 2
    Last Post: 09-13-2013, 02:33 PM
  3. Run-time error 13: type mismatch
    By ehe in forum Programming
    Replies: 3
    Last Post: 01-13-2013, 12:58 AM
  4. run-time error '13' type mismatch
    By teebumble in forum Forms
    Replies: 8
    Last Post: 12-03-2012, 01:18 PM
  5. run-time error 13 , type mismatch
    By Compufreak in forum Programming
    Replies: 8
    Last Post: 08-13-2012, 12:17 AM

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