New to Access select statement (1 Viewer)

[cataylor]

Select
Sum([LLR Data].Miles) AS Ttl_Miles, Sum([LLR Data].[Direct Weight]) AS Ttl_Weight,
IIF([LLR Data].[Direct Weight] > 0,[LLR Data].[Miles],0)
where [LLR Data].[Commodity] = 'RETL'





Why can't I use the iff in the select I need to capture the miles depending on the field

 

[pbaldy]

What error do you get? Looks okay offhand. Have you tried summing that field too? I suspect you're running into the error about aggregate functions.

 

[cataylor]

Yes, an aggregate functions error.

 

[pbaldy]

Did you try adding the sum?

Select
Sum([LLR Data].Miles) AS Ttl_Miles, Sum([LLR Data].[Direct Weight]) AS Ttl_Weight,
Suum(IIF([LLR Data].[Direct Weight] > 0,[LLR Data].[Miles],0)) As Whatever
where [LLR Data].[Commodity] = 'RETL'