Not sure, because I don't see the whole sql-query.

but my guess, based on the error, you do something with a variable that you expand-inline.
test-query will succeed because you use actual values.
runtime, it might be empty and then certain queries will end up as an error.

im using this sql code on the advanced query

SELECT falsecount.USERTEMPID,(COALESCE(truecount.STATCOUNT,0) + COALESCE(QAAuditTruecount.STATCOUNT,0)),
(COALESCE(falsecount.STATCOUNT, 0) + COALESCE(QAAUDITFalsecount.STATCOUNT,0)), 
(COALESCE(truecount.STATCOUNT, 0) + COALESCE(falsecount.STATCOUNT, 0)+ COALESCE(QAAUDITFalsecount.STATCOUNT,0)
+ COALESCE(QAAuditTruecount.STATCOUNT,0)) AS TOTALCOUNT
from

   (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT 
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate] AND {QAChecklist}.[IsPass] = false
    GROUP BY {BPAChecklist}.[UserID]
    ) falsecount

left join 

    (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate]   
    INNER JOIN {ForEasyFix}
        ON {ForEasyFix}.[QAChecklist] = {QAChecklist}.[Id]
    INNER JOIN {QAAudit}
        ON {ForEasyFix}.[Id] = {QAAudit}.[ForEasyFix] AND {QAAudit}.[IsPAss] = False
  
        -- WHERE datefinish between dateparam1 and dateparam2
    GROUP BY {BPAChecklist}.[UserID]
    ) QAAUDITFalsecount

on falsecount.USERTEMPID = QAAUDITFalsecount.USERTEMPID

left join 

   ( select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate] AND {QAChecklist}.[IsPass] = true
        -- WHERE datefinish between dateparam1 and dateparam2 
    GROUP BY {BPAChecklist}.[UserID]
    ) truecount

on truecount.USERTEMPID = falsecount.USERTEMPID
left join 

    (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate]
    INNER JOIN {ForEasyFix}
        ON {ForEasyFix}.[QAChecklist] = {QAChecklist}.[Id] 
    INNER JOIN {QAAudit}
        ON {ForEasyFix}.[Id] = {QAAudit}.[ForEasyFix] AND {QAAudit}.[IsPAss] = True
  
        -- WHERE datefinish between dateparam1 and dateparam2
    GROUP BY {BPAChecklist}.[UserID]
    ) QAAUDITTruecount

on falsecount.USERTEMPID = QAAUDITTruecount.USERTEMPID

ah, remove the comments at least.

in my experience, comments are buggy in runtime-mode
(don't ask me why :) )

Hi Joshua,

If you want to put comments inside your query use this format /*XXXXX*/ instead and it will probably work (not sure about MySQL though as I haven't used it yet).

Cheers
Guilherme

hi guys

just tried to change the way i commented out the where clauses and it worked. it seems that when we use the "--" to comment out it gets a runtime error but if we use the format that Guilherme gave it works perfectly

thanks for the responses guys
Josh