Aggregate function error

[LHWC]

I have a query with the following SQL statement:

SELECT DISTINCT [Double Balances].[Balance #], [Double Balances].[Glass Height], [Double Balances].[Min Weight], [Double Balances].[Max Weight], [DH Consolidate].TSWGHT1, [DH Consolidate].NEW_TYPE, IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,Int([DH Consolidate]!OATVDLO),Int([DH Consolidate]!OATVDLO)-1) AS [GH Select], Sum([DH Consolidate].FQTY) AS SumOfFQTY

FROM [DH Consolidate], [Double Balances], Balances

GROUP BY [Double Balances].[Balance #], [Double Balances].[Glass Height], [Double Balances].[Min Weight], [Double Balances].[Max Weight], [DH Consolidate].TSWGHT1, [DH Consolidate].NEW_TYPE, IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,Int([DH Consolidate]!OATVDLO),Int([DH Consolidate]!OATVDLO)-1)

HAVING ((([DH Consolidate].TSWGHT1) Between [Double Balances]![Min Weight] And [Double Balances]![Max Weight] And ([DH Consolidate].TSWGHT1) Not Between [Balances]![Min Weight] And [Balances]![Max Weight]) AND ((IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,Int([DH Consolidate]!OATVDLO),Int([DH Consolidate]!OATVDLO)-1))=[Double Balances].[Glass Height]))

ORDER BY [DH Consolidate].NEW_TYPE;

When I try to run it I get the following error:

"you tried to execute a query that does not include the specified expression '[DH Consolidate].TSWGHT1) Between [Double Balances]![Min Weight] And [Double Balances]![Max Weight] And ([DH Consolidate].TSWGHT1) Not Between [Balances]![Min Weight] And [Balances]![Max Weight]) AND IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,Int([DH Consolida' as part of an aggregate function."

Does anyone know what is causing this and how to correct it?

Thanks in advance.

 

[MoLaker]

The only thing I see is a missing closing parenthesis - I think. I marked it in red color.

HAVING ((([DH Consolidate].TSWGHT1) Between [Double Balances]![Min Weight] And [Double Balances]![Max Weight]) And ([DH Consolidate].TSWGHT1) Not Between [Balances]![Min Weight] And [Balances]![Max Weight]) AND ((IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,Int([DH Consolidate]!OATVDLO),Int([DH Consolidate]!OATVDLO)-1))=[Double Balances].[Glass Height]))

 

[LHWC]

Thanks MO,
I added the ), but then got an error stating i had too many parenthesis.

 

[MoLaker]

Try laying out your SQL something like this temporarily to be able to see where parenthesis should go. While you are at it, take a closer look at the IIF statement. I'm not sure the ELSE portion will fly. I have made no changes from your original posting in this. You must find and make the corrections needed. When you think it's right, remove the comments and spaces and test it.

HAVING ((([DH Consolidate].TSWGHT1)

Between

' looks like one para missing here
[Double Balances]![Min Weight] And [Double Balances]![Max Weight])

And ([DH Consolidate].TSWGHT1)

Not Between

' looks like one para missing here
[Balances]![Min Weight] And [Balances]![Max Weight])

AND ((IIf(Int([DH Consolidate]!OATVDLO) Mod 2=0,

'then

Int([DH Consolidate]!OATVDLO),

'else

Int([DH Consolidate]!OATVDLO)-1))=[Double Balances].[Glass Height]))

 

[LHWC]

Thanks Mo,
I realized that I had forgotten to include Balances.Min Weight and Balances.Max Weight in the SELECT statement (duh). It runs fine now.
Thanks again!