Using "iif" statement with dates to get totals. 2

[drussum]

I Have am writing a program that is designed to give me the number of units on back log. The problem i am having is that I am pulling the information from an oracle database using access 97. The [plan del date] is storing the date in a short date format " 02/12/02" I am using the following iif statement to get the dates to appear as Jan, feb, mar, apr, may,......... for the entire year. The other field [Ordered Quan] holds the number of units that will be delivered. How do I get them together? so that I can have the number of units with a planned delivery date between a certain date range?? following is an example of what I am doing to get the dates to show as months : Jan: NZ(Sum(IIf(DatePart("m",[Plan Del Date])=1,1,0))) any ideas

 

[jjonesal]

You should be able to use a crosstab query:

I haven't tested, but it would be something like below.....PIVOT produces the column headings you desired without having to calculate each individually...

TRANSFORM Sum([bounits])
SELECT [proddesc], Sum([bounits]) AS [Total BO Units]
FROM Orders
GROUP BY [proddesc]
PIVOT Format([OrderDate],"mmm&quot IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

Hope this helps...
J. Jones
jjones@cybrtyme.com

 

[drussum]

Thank you for your help. I am trying to use what you suggested but am not able to make qry work. following is what I am working with:

[Item #] Item Number.
[Item Desc] Item Description.
[Plan Del Date] Delivery dte for item.
[Ordered Quan] Quantity of item ordered.
[Delivered Quan] Quantity of item delivered.
[Back Order] Diference between [Ordered Quan]-[Delivered Quan]....

any ideas would really be appeciated.

 

[fullkeel]

I am not sure if I understand your question but you could create a small function to give you the three letter month.
It is not so elegant but works on any date string.

Function monthofyear (date2check as Date) as string
Dim Months as String, MoNum as Integer
Months = "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC"
MoNum = datepart("m",date2check)
MonthoYear = Mid(Months,(MoNum*3)-2,3)
end function

a single line function of the above function follows:
x = Mid("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",(datepart("m",date2check)*3)-2,3)

Hope it is helpful

 

[drussum]

Thank you for your help I would like to try that. Where do I type all that? in the design view window where the field goes?

 

[fullkeel]

Put the function in a global module and you can call it from anywhere, for example if you have a field you want to display the 3 character month name and you have a field on the form named [Plan Del Date]that has the date to check you could do the following.
In the 'Control Source' of the month name field you would enter =MonthOfYear([Plan Del Date])
And don't forget the equal sign.