Results 1 to 7 of 7

Thread: SQL help (97)

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL help (97)

    I get a data type mismatch in criteria expression when I try to run the below SQL as my reports record source.

    Now in the stl statement below, the WHERE statement is my only addition. GLOBALREGIONCODE is a variant and the DIRECTORCODE being compared against it is a LONG INTEGER, is there a conflict here? Please advise.

    strSQL = "SELECT qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode, Sum(NewQuery.ActivationForecast) AS SumOfActivationForecast, Sum(NewQuery.Results) AS SumOfResults, Sum(NewQuery.TotalCommits) AS SumOfTotalCommits, Sum(NewQuery.TotalPaid) AS SumOfTotalPaid, Sum(NewQuery.Accrual) AS SumOfAccrual, Sum(NewQuery.ProjectedSpending) AS SumOfProjectedSpending FROM qryTypeMonthMgrCode LEFT JOIN NewQuery ON (qryTypeMonthMgrCode.Month = NewQuery.tempMonth) AND (qryTypeMonthMgrCode.Code = NewQuery.Code) AND (qryTypeMonthMgrCode.promotionType = NewQuery.PromotionType)" _
    & " WHERE qryTypeMonthMgrCode.DirectorCode='" & globalregioncode & "' GROUP BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode HAVING (((qryTypeMonthMgrCode.Code) <> 1000 And (qryTypeMonthMgrCode.Code) <> 1107 And (qryTypeMonthMgrCode.Code) <> 1108 And (qryTypeMonthMgrCode.Code) <> 1109 And (qryTypeMonthMgrCode.Code) <> 1202 And (qryTypeMonthMgrCode.Code) <> 1306 And (qryTypeMonthMgrCode.Code) <> 1307 And (qryTypeMonthMgrCode.Code) <> 1308 And (qryTypeMonthMgrCode.Code) <> 1309 And (qryTypeMonthMgrCode.Code) <= 1504)) ORDER BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code;"

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (97)

    It does sound like that is the problem

    try casting globalregioncode to an integer

    WHERE qryTypeMonthMgrCode.DirectorCode='" & cInt(globalregioncode) &
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (97)

    No change.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (97)

    Sorry, my fault. I really need to learn how to read.

    You said LONG INTEGER, which should be:


    cLNG(.....)
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (97)

    Don

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

    Re: SQL help (97)

    Aside from the fact that you could simplify the Having clause by using a Not In( <value list>) instead of all those "<> whatever" expressions and make your SQL more readable, you're handling globalregioncode as a string not a number because you're concatenating single quotes around it. If the DirectorCode field is numeric, you will definitely get a type mismatch.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (97)

    Thanks Charlotte, the below worked it out.

    strSQL = "SELECT NewQuery.tempYear, qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode, Sum(NewQuery.ActivationForecast) AS SumOfActivationForecast, Sum(NewQuery.Results) AS SumOfResults, Sum(NewQuery.TotalCommits) AS SumOfTotalCommits, Sum(NewQuery.TotalPaid) AS SumOfTotalPaid, Sum(NewQuery.Accrual) AS SumOfAccrual, Sum(NewQuery.ProjectedSpending) AS SumOfProjectedSpending FROM qryTypeMonthMgrCode LEFT JOIN NewQuery ON (qryTypeMonthMgrCode.Month = NewQuery.tempMonth) AND (qryTypeMonthMgrCode.Code = NewQuery.Code) AND (qryTypeMonthMgrCode.promotionType = NewQuery.PromotionType)" _
    & " WHERE qryTypeMonthMgrCode.DirectorCode=" & CLng(globalregioncode) & " AND qryTypeMonthMgrCode.PromotionType='" & filterType & "' AND NewQuery.tempYear= '" & filterYear & "' GROUP BY NewQuery.tempYear, qryTypeMonthMgrCode.PromotionType,qryTypeMonthMgrC ode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode HAVING (((qryTypeMonthMgrCode.Code) <> 1000 And (qryTypeMonthMgrCode.Code) <> 1107 And (qryTypeMonthMgrCode.Code) <> 1108 And (qryTypeMonthMgrCode.Code) <> 1109 And (qryTypeMonthMgrCode.Code) <> 1202 And (qryTypeMonthMgrCode.Code) <> 1306 And (qryTypeMonthMgrCode.Code) <> 1307 And (qryTypeMonthMgrCode.Code) <> 1308 And (qryTypeMonthMgrCode.Code) <> 1309 And (qryTypeMonthMgrCode.Code) <= 1504)) ORDER BY qryTypeMonthMgrCode.PromotionType,qryTypeMonthMgrC ode.Month, qryTypeMonthMgrCode.Code;"

Posting Permissions

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