Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: run time error 3464 data type mismatch

    Hi All,

    I get run time error 3464 data type mismatch on below code on line :

    Set rsCommsManagerID = db.OpenRecordset(sqlCommsManager)

    The code was working before so no idea why this would happen.
    Any help is appreciated.

    Many thanks,

    Katrin

    Dim sqlCommsManager As String 'sql to extract the managers id and their direct reports id as mapped in table position.
    sqlCommsManager = " SELECT TBLPOSITION.POSITIONID, Min(TBLPOSITION.POSITIONSTARTDATE) AS PositionStarts, Max(TBLPOSITION.POSITIONENDDATE) AS PositionEnds, TBLPOSITION.REPORTSTOPOSITIONID" & _
    " FROM TBLPOSITION " & _
    " GROUP BY TBLPOSITION.POSITIONID, TBLPOSITION.REPORTSTOPOSITIONID, Right([POSITIONSTARTDATE],4)" & _
    " HAVING (((TBLPOSITION.REPORTSTOPOSITIONID)=" & CommsPositionID & ") And TBLPOSITION.POSITIONID<>804 And ((Right([POSITIONSTARTDATE],4))=" & CommsYear & "))"

    Dim rsCommsManagerID As Recordset 'recordset holds the managers id and their direct reports id .
    Set rsCommsManagerID = db.OpenRecordset(sqlCommsManager)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    off hand I'd guess its the difference between a DAO and a ADO connection. it may clear if you expressly define what type of recordset you plan on using. the methods are similar but NOT identical
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The one thing that leaps out at me is the un-displayed field
    Right([POSITIONSTARTDATE],4)"
    POSITIONSTARTDATE is a date. The Right() function only works on text/string info. The proper function to use is the Year() function. Assuming that CommsYear has the correct year, the code would be:
    Code:
    sqlCommsManager = " SELECT TBLPOSITION.POSITIONID, Min(TBLPOSITION.POSITIONSTARTDATE) AS PositionStarts, Max(TBLPOSITION.POSITIONENDDATE) AS PositionEnds, TBLPOSITION.REPORTSTOPOSITIONID" & _
    " FROM TBLPOSITION " & _
    " GROUP BY TBLPOSITION.POSITIONID, TBLPOSITION.REPORTSTOPOSITIONID, Year([POSITIONSTARTDATE])" & _
    " HAVING (((TBLPOSITION.REPORTSTOPOSITIONID)=" & CommsPositionID & ") And (TBLPOSITION.POSITIONID<>804) And (Year(POSITIONSTARTDATE) = " & CommsYear & "))"
    Sam
    Last edited by Sam Landy; 07-24-12 at 13:00.

  4. #4
    Join Date
    Jul 2012
    Posts
    16

    Thanks,

    I am going to try it... will let you know if it works

  5. #5
    Join Date
    Jul 2012
    Posts
    16
    [QUOTE=bareveveryone;6561993]Hi All,

    I get run time error 3464 data type mismatch on below code on line :

    Set rsCommsManagerID = db.OpenRecordset(sqlCommsManager)

    The code was working before so no idea why this would happen.
    Any help is appreciated.

    Many thanks,

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cam we see tha actual SQL being sent to the SQL engine... not the VBA bt the actual values

    data type mismatch occurs when the SQL engine expects a different datatype than its getting. that may be a numeric value in place of a string or vice versa.

    it would also help to see the table(s) design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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