Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Query error 3615 - mismatch error

    Query Statement

    I've looked until I'm blue in the face but I can't see a mismatch here . . .

    Code:
    Private Sub Command133_Click()
    'SAN DIEGO NORMALIZE OFFICE NAMES
    MsgBox "Normailze San Diego Office Names"
    strSQL = "UPDATE [SandicorData] INNER JOIN [TblSandicorRoster] ON [SandicorData].[ListID] = [TblSandicorRoster].[OfficeKey] Set [SandicorData].[ListName] = [tblSandicorRoster].[Officename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE [SandicorData] INNER JOIN [TblSandicorRoster] ON [SandicorData].[SellID] = [TblSandicorRoster].[OfficeKey] Set [SandicorData].[SellName] = [tblSandicorRoster].[Officename]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED SAN DIEGO OFFICE NORMALIZATION"
    End Sub
    Thanks . . . Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the columns to update
    the values to update those columns with

    as with all these things if you actually look at the SQL being sent to the SQL engine (ie examine the value of strSQL it should give you a clue as to what is going wrong
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    It looks the same as this one and this one works perfectly???

    Code:
    Dim strSQL As String
    strSQL = "UPDATE tblPreScmls INNER JOIN ScmlsNormalize ON tblPreScmls.ListID = ScmlsNormalize.UID Set tblPreScmls.ListName = [scmlsnormalize].[normalizedofficename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE tblPreScmls INNER JOIN ScmlsNormalize ON tblPreScmls.SellID = ScmlsNormalize.UID Set tblPreScmls.SellName = [scmlsnormalize].[normalizedofficename]"
    CurrentDb.Execute strSQL
    I can see the mismatch . . .

    Rick

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are [SandicorData].[ListID] and [TblSandicorRoster].[OfficeKey] as well as tblPreScmls.SellID and [TblSandicorRoster].[OfficeKey] of the same data type (check in the tables definitions)?
    Have a nice day!

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Quote Originally Posted by Rick Schreiber View Post
    It looks the same as this one and this one works perfectly???

    Code:
    Dim strSQL As String
    strSQL = "UPDATE tblPreScmls INNER JOIN ScmlsNormalize ON tblPreScmls.ListID = ScmlsNormalize.UID Set tblPreScmls.ListName = [scmlsnormalize].[normalizedofficename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE tblPreScmls INNER JOIN ScmlsNormalize ON tblPreScmls.SellID = ScmlsNormalize.UID Set tblPreScmls.SellName = [scmlsnormalize].[normalizedofficename]"
    CurrentDb.Execute strSQL
    I can't see the mismatch . . .

    Rick
    Uh . . . maybe it's a text field with a number field?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Rick Schreiber View Post
    Uh . . . maybe it's a text field with a number field?
    That would explain the type mismatch error. If it's the case, convert one of the column to the type of the other:
    From Numeric to String: CStr(<ColumnName>)
    From String to Long: CLng(<ColumnName>)
    Have a nice day!

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Yes - I discovered that one field was a number field and the other a text field.

    With a number like this SD32366 is there a query that will remove just the SD so I can change it to a primaryKey number field?

    I tried Left([Myfield],2) but that deleted ALL DATA in the table. Lucky I backed up first as there are over 27,000 records.

    Any clean way to do this?

    Rick

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Rick Schreiber View Post
    I tried Left([Myfield],2)
    It should be Mid([MyField], 3) if you want to remove "SD" and keep "32366".
    Have a nice day!

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Yep that what caused the problem. Primary keys should have all been numbers.

    Once fixed the query slammed through 27,000 plus records in a blink of an eye.

    As always - thanks very much.

    Rick

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •