Assistance with IIF statement please 1

[kentwoodjean]

I do not normally use IIF statements as I have difficulty setting them up. I believe that I need one now and I am looking for a bit of help. I have a query that combines 3 queries each reporting out a number for "closes" on a daily basis (see SQL below). W/closes, occasionally I will have "1" that show up as a saturday date. Thus, my weekending is on Saturday and does not line up. I want to capture the number however but consider it as Monday rather than Saturday. So, if a close is dated as Saturday, I want it to be considered The following Monday instead. How would I go about making that happen?

SELECT q.[Closed Dt] AS [Closed Date], q.[CountOfClosed Dt] AS TotCloses, Sum(q7.[CountOfClosed Dt]) AS [7 days or less], Sum(q8.[CountOfClosed Dt]) AS [8 days or more]
FROM ([Countof Closed Date] AS q LEFT JOIN [Countof Closed Date7] AS q7 ON q.[Closed Dt] = q7.[Closed Dt]) LEFT JOIN [Countof Closed Date8] AS q8 ON q.[Closed Dt] = q8.[Closed Dt]
GROUP BY q.[Closed Dt], q.[CountOfClosed Dt];

 

[PHV]

SELECT q.[Closed Dt]+IIf(Format(q.[Closed Dt],'w')=7,2,0) AS [Closed Date], Sum(q.[CountOfClosed Dt]) AS TotCloses, ...
GROUP BY q.[Closed Dt]+IIf(Format(q.[Closed Dt],'w')=7,2,0);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

Perfect!!! Thanks so much.