Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Posts
    31

    Unanswered: Problem with query criteria

    Guys i have a problem in the criteria in my query. It says that "Data type mismatch"

    Here is my code...
    I think the problem here is the format coz i dont know what's really the exact format. Please see in my IF Else Statement...


    Dim ext
    ext = ""
    Dim Sql

    Sql = "SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName, Employee.MiddleName, BadgeStatus.BStatus, BadgeStatus.Remarks, Badge.BadgeNo, BadgeStatus.DteDeactivated, Badge.CardType, Employee.Program, RTrim(IIf(Not IsNull([Employee.LastName]),[Employee.LastName],''))+', '+RTrim(IIf(Not IsNull([Employee.FirstName]),[Employee.FirstName],''))+' '+RTrim(IIf(Not IsNull([Employee.MiddleName]),[Employee.MiddleName],'')) AS name, Badge.ActivationDate, Format([Badge.ActivationDate],'mm/dd/yyyy') AS dteAct, Employee.SiteCode, UCase(IIf(Not IsNull([Employee.SiteCode]) Or ([Employee.SiteCode])='',[Employee.SiteCode],'No Site Code')) AS Usitecode, Badge.Reason, UCase(IIf(([Badge.Reason]) Like 'New Card' & '*','New Card','Replacement')) AS card " & _
    " FROM Employee INNER JOIN (Badge INNER JOIN BadgeStatus ON Badge.BadgeNo = BadgeStatus.BadgeNo) ON Employee.EmployeeID = Badge.EmployeeID" & _
    " GROUP BY Employee.EmployeeID, Employee.LastName, Employee.FirstName, Employee.MiddleName, BadgeStatus.BStatus, BadgeStatus.Remarks, Badge.BadgeNo, BadgeStatus.DteDeactivated, Badge.CardType, Employee.Program, RTrim(IIf(Not IsNull([Employee.LastName]),[Employee.LastName],''))+', '+RTrim(IIf(Not IsNull([Employee.FirstName]),[Employee.FirstName],''))+' '+RTrim(IIf(Not IsNull([Employee.MiddleName]),[Employee.MiddleName],'')) AS name, Badge.ActivationDate, Format([Badge.ActivationDate],'mm/dd/yyyy') AS dteAct, Employee.SiteCode, UCase(IIf(Not IsNull([Employee.SiteCode]) Or ([Employee.SiteCode])='',[Employee.SiteCode],'No Site Code')) AS Usitecode, Badge.Reason, UCase(IIf(([Badge.Reason]) Like 'New Card' & '*','New Card','Replacement')) "

    If Forms!ChooseBadgeReport!cboSpecificProgram.Value = "All" Then
    ext = " HAVING Badge.ActivationDate >= '" & [Forms]![ChooseBadgeReport]![dteFrom] And "Badge.ActivationDate <='" & [Forms]![ChooseBadgeReport]![DteTO] & "' "
    Else
    ext = " HAVING Badge.CardType= '" & [Forms]![ChooseBadgeReport]![cboSpecificProgram] & "' And Badge.ActivationDate >= '" & [Forms]![ChooseBadgeReport]![dteFrom] & "' And Badge.ActivationDate <='" & [Forms]![ChooseBadgeReport]![DteTO] & "' "
    End If

    Sql = Sql + ext
    Me.RecordSource = Sql

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that error message usually means you are supplying a value of the wrong datatype
    it probably means you have a string/text value that isn't escaped with a ' or ".
    can we see the actual SQL as opposed toyour VB that creates the SQL?

    in VBA the string concatenation symbol is & NOT +, although the JIT will try to handle + as & where it can

    do you need to do the formatting as part of the query?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    31
    So what's the real concatenation sir?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I might be wrong but this part of the expression renders me suspiscious:
    Code:
    "Badge.ActivationDate <='" & [Forms]![ChooseBadgeReport]![DteTO] & "' "
    1. Date values, should be enclosed between pound (or number) characters (#), not between single quotes (').

    2. When used in a SQL or VBA expression, date values must be in "mm/dd/yyyy" (or "yyyy-mm-dd") format.
    Have a nice day!

  5. #5
    Join Date
    Dec 2012
    Posts
    31
    This is my error..please see attached file...


    Any reply would be much appreciated

    thanx..
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before can we see the SQL?

    if the worst comes to the worst dump it out as a msgbox
    or debug.print SQL

    this sort of error is going to be a pig to track down looking at the VBA
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2012
    Posts
    31
    This is the SQL


    SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName, Employee.MiddleName, BadgeStatus.BStatus, BadgeStatus.Remarks, Badge.BadgeNo, BadgeStatus.DteDeactivated, Badge.CardType, Employee.Program, RTrim(IIf(Not IsNull([Employee.LastName]),[Employee.LastName],""))+", "+RTrim(IIf(Not IsNull([Employee.FirstName]),[Employee.FirstName],""))+" "+RTrim(IIf(Not IsNull([Employee.MiddleName]),[Employee.MiddleName],"")) AS name, Badge.ActivationDate, Format([Badge.ActivationDate],"mm/dd/yyyy") AS dteAct, Employee.SiteCode, UCase(IIf(Not IsNull([Employee.SiteCode]) Or ([Employee.SiteCode])="",[Employee.SiteCode],"No Site Code")) AS Usitecode, Badge.Reason, UCase(IIf(([Badge.Reason]) Like "New Card" & '*',"New Card","Replacement")) AS card
    FROM Employee INNER JOIN (Badge INNER JOIN BadgeStatus ON Badge.BadgeNo=BadgeStatus.BadgeNo) ON Employee.EmployeeID=Badge.EmployeeID
    GROUP BY Employee.EmployeeID, Employee.LastName, Employee.FirstName, Employee.MiddleName, BadgeStatus.BStatus, BadgeStatus.Remarks, Badge.BadgeNo, BadgeStatus.DteDeactivated, Badge.CardType, Employee.Program, RTrim(IIf(Not IsNull([Employee.LastName]),[Employee.LastName],""))+", "+RTrim(IIf(Not IsNull([Employee.FirstName]),[Employee.FirstName],""))+" "+RTrim(IIf(Not IsNull([Employee.MiddleName]),[Employee.MiddleName],"")), Badge.ActivationDate, Employee.SiteCode, UCase(IIf(Not IsNull([Employee.SiteCode]) Or ([Employee.SiteCode])="",[Employee.SiteCode],"No Site Code")), Badge.Reason, UCase(IIf(([Badge.Reason]) Like "New Card" & '*',"New Card","Replacement"))
    HAVING (((Badge.ActivationDate)>=[Forms]![ChooseBadgeReport]![dteFrom] And (Badge.ActivationDate)<=[Forms]![ChooseBadgeReport]![dteTo]))
    ORDER BY Employee.LastName

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    still using the + to concatenate strings not &

    personally I'd strip out all the IIF's (at least for now) and do that via the front end
    likewise date formatting, easy to do in the front end, just confuses the SQL here

    you can use the NZ function in place of the IIF
    im not convinced [Employee.FirstName] is legal, you only need the square brackerts if there is a space in the table or column name
    eg:-
    [my table name].[my column name]
    you only need to qualify the column name with a table name if there is a column of the same name in more than one table referrred to in the select or join table definitions

    check the date columns actually have dates in them (no NULLS)

    what I'd suggest you do is paste the whole query into the query designer SQL pane and then refine it section by section
    omit the group by and having sub clauses for now
    consider pushing the formatting of the names into a function and returns, say, 'd j rods'
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2012
    Posts
    31
    Thank you very much

    It works fine already.

Posting Permissions

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