Urgent Access Query Help Required!

[Joel84]

Hi,

I am new to access and am slowly teaching myself! I have a questions regarding an access SQL, which you may be able to help with.

Below is the SQL which runs without error, however, does not produce the results I am after:

'SELECT [DC Planning Data].[Month], [DC Planning Data].[Week No], [DC Planning Data].[PO Number], [DC Planning Data].Name, Sum([DC Planning Data].[Kitting Hours]) AS [Approx Kitting Hours By Vendor], [DC Planning Data].[Container Type], [DC Planning Data].[Overseas Local], [DC Planning Data].[Container No], [DC Planning Data].[Expected Arrival Day], [DC Planning Data].[Kitting Putaway Day], Count([DC Planning Data].Arrival) AS CountOfArrival, Sum(IIf([DC Planning Data].[Overseas Local]='L',[DC Planning Data].Due,IIf([DC Planning Data].[Overseas Local]='O',[DC Planning Data].[Shipping Qty],IIf([DC Planning Data].[Shipping Qty]='',[DC Planning Data].Due)))) AS QTY, Sum([DC Planning Data].[SG Direct Putaway Qty]) AS [Put Away QTY], Sum([DC Planning Data].[Packaging Units Qty]) AS [Packaging Units Quantity]
FROM [DC Planning Data]
WHERE [DC Planning Data].[Kit Ind] In ('P','Y')
GROUP BY [DC Planning Data].[Month], [DC Planning Data].[Week No], [DC Planning Data].[PO Number], [DC Planning Data].Name, [DC Planning Data].[Container Type], [DC Planning Data].[Overseas Local], [DC Planning Data].[Container No], [DC Planning Data].[Expected Arrival Day], [DC Planning Data].[Kitting Putaway Day];'

The part of the SQL I having an issue with is as below:

'Sum(IIf([DC Planning Data].[Overseas Local]='L',[DC Planning Data].Due,IIf([DC Planning Data].[Overseas Local]='O',[DC Planning Data].[Shipping Qty],IIf([DC Planning Data].[Shipping Qty]='',[DC Planning Data].Due)))) AS QTY'

Basically, I want it to follow the logic below:

If Overseas/Local = 'L' then 'Due'
If Overseas/Local = 'O' then 'Shipping QTY' but if shipping QTY = 0 then use 'Due'

Where am I going wrong? Should I be using an 'If' statement?

Also, please let me know if you can see anything wrong with the remainder of the SQL!

Thanking you in advance!

Cheers,

 

[jdraw]

Do not use embedded spaces or special characters in your field and object names. You can do it, but the problems you'll have are not worth that naming scheme.

I think you're going to have to describe - in business terms - WHAT you are trying to achieve, and then readers can offer alternatives or solutions and HOW to do it.

I suggest you look at this link(s) regarding clarifyiong wjat you are trying to do.
http://www.access-programmers.co.uk/forums/showthread.php?t=223418

 

[Shado]

The syntax for Iif is: iif(Expression, True Result, False Result) Your last iif only seems to have a True Result but not a false?

Feel free to correct me if I'm being blind again