Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Union Query Issues (Access 2000 and 97)

    Ok, this problem is driving me nuts!! I created a union query in Access 97 and it works great, however when I create it in Access 2000 I get the following error message: Data type mismatch in criteria expression.
    The database created in 2000 is the same exact one that I have in 97 its a conversion copy of it, everything else in the 2000 database works with out a flaw.
    I have even tried running it using the 2000 backend and the 97 backend and no matter what I do I still et the same error. By the way both parts of the query are pulling the data from the same place.

    Please help

    SQL;
    SELECT "EXEL1" AS Exel1, Lpad([tblAssociateInfo]![AssociateId],"0",6) AS [Associate #], [tblAssociateInfo]![FName] & " " & [tblAssociateInfo]![LName] AS [Associate Name], "E" AS E, "$" AS [$], 291 AS 291, 100 AS [Dollar Amount], IIf(DateDiff("d",[tblAssociateInfo]![Hire Date],Date())<91,"**"," ") AS [On Probation]
    FROM qryInTheGreen1 RIGHT JOIN tblAssociateInfo ON qryInTheGreen1.AssociateId = tblAssociateInfo.AssociateId
    GROUP BY "EXEL1", Lpad([tblAssociateInfo]![AssociateId],"0",6), [tblAssociateInfo]![FName] & " " & [tblAssociateInfo]![LName], "E", "$", 291, 100, IIf(DateDiff("d",[tblAssociateInfo]![Hire Date],Date())<91,"**"," "), tblAssociateInfo.DeptId, qryInTheGreen1.Qrt, tblAssociateInfo.Inactive, tblAssociateInfo.Active
    HAVING (((tblAssociateInfo.DeptId) Not In ("X")) AND ((tblAssociateInfo.Inactive)=False) AND ((tblAssociateInfo.Active) In ("Active","FMLA")) AND ((Sum(qryInTheGreen1.Points))=0 Or (Sum(qryInTheGreen1.Points)) Is Null) AND ((Sum(qryInTheGreen1.Weight))=0 Or (Sum(qryInTheGreen1.Weight)) Is Null))

    union

    SELECT "EXEL1" AS Exel1, Lpad([tblAssociateInfo]![AssociateId],0,6) AS AssociateIdLP, [tblAssociateInfo]![FName] & " " & [tblAssociateInfo]![LName] AS [Associate Name], "E" AS E, "$" AS [$], 291 AS 291, 50 AS [Dollar Amount], IIf(DateDiff("d",[tblAssociateInfo]![Hire Date],Date())<91,"**"," ") AS [On Probation]
    FROM tblDept RIGHT JOIN ((tblAssociateInfo RIGHT JOIN qryFindDayorLessPersonal ON tblAssociateInfo.AssociateId = qryFindDayorLessPersonal.AssociateId) LEFT JOIN qryFindPoints ON tblAssociateInfo.AssociateId = qryFindPoints.AssociateId) ON tblDept.DeptId = tblAssociateInfo.DeptId
    WHERE (((qryFindPoints.SumOfPoints) Is Null));

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query Issues (Access 2000 and 97)

    It's just a guess, but I suspect your problem is in this expression in the first query:

    Lpad([tblAssociateInfo]![AssociateId],"0",6) AS [Associate #]

    as compared to this in the second:

    Lpad([tblAssociateInfo]![AssociateId],0,6) AS AssociateIdLP

    Access 2000 is more rigorous in many ways than Access 97. You can't slip as much by the query engine as you used to. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    Do each of the individual queries run properly in Access 2000?
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Query Issues (Access 2000 and 97)

    First of all thank you Charlotte for your response.
    The differences you

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Union Query Issues (Access 2000 and 97)

    In a union query, each SELECT subclause must return exactly the same kind of field/data - your first would appear to return a number, but subsequent ones appear to return text. Try converting the number to a text string with Str() and see if that solves your problem.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query Issues (Access 2000 and 97)

    You can't left pad a number, only a string. That means your expression has to return a string in both queries. I have no idea what your LPad function does, so I can't suggest a way to fix it. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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