Not showing up when I try to do By date

[uapak90]

Hey Everyone,
Have a slight problem sorting my data through the date:
I have a table that have the following field
Date
Lot#
Room/Location
Count
Pre/Post

I want to make a query that if the Room/Location persay says "compounding" ill add a column that will say number of samples and if its compounding, there will be 9, if ointment, 11, if liquid 10, etc...
I did that already by making a query and adding a new field using 6 if statements.
And then I used group by for Room/Location and Sum for the new field.
I now wanted to add the Date column so I can specify between what dates I wnat this data for.. and when I add the Date field from the original table, and I pick the "where" option from Totals, I do not get a value at all, even if I specify between [this date] and [this date]
How can I add the date field with a spceified criteria?

 

[Remou]

Please post your SQL.

 

[uapak90]

SELECT [RCS (2008)].[Room/Location], Sum((((((IIf([Room/Location]="Liquid",10,(IIf([Room/Location]="Compounding",11,(IIf([Room/Location]="Ointment",9,(IIf([Room/Location]="Airlock",1,(IIf([Room/Location]="Corridor",2,(IIf([Room/Location]="Gowning (Prod)",1,""))))))))))))))))) AS TotalNumberOfSamples
FROM [RCS (2008)]
GROUP BY [RCS (2008)].[Room/Location];

 

[Remou]

How about:

SELECT [RCS (2008)].[Room/Location], Sum((((((IIf([Room/Location]="Liquid",10,(IIf([Room/Location]="Compounding",11,(IIf([Room/Location]="Ointment",9,(IIf([Room/Location]="Airlock",1,(IIf([Room/Location]="Corridor",2,(IIf([Room/Location]="Gowning (Prod)",1,""))))))))))))))))) AS TotalNumberOfSamples
FROM [RCS (2008)]
GROUP BY [RCS (2008)].[Room/Location]
WHERE [DateField] Between [Enter Start Date] AND [Enter End Date]

If this is not what you were thinking of, please post the SQL with the date even if it does not work.

You may wish to consider a table to simplify that if statement.

 

[PHV]

Remou, I'd put the WHERE clause before the GROUP BY ...

 

[uapak90]

Well I had to revise your code a little but at the end this is what it looked like:
SELECT [RCS (2008)].[Room/Location], Sum((((((IIf([Room/Location]="Liquid",10,(IIf([Room/Location]="Compounding",11,(IIf([Room/Location]="Ointment",9,(IIf([Room/Location]="Airlock",1,(IIf([Room/Location]="Corridor",2,(IIf([Room/Location]="Gowning (Prod)",1,""))))))))))))))))) AS TotalNumberOfSamples
FROM [RCS (2008)]
WHERE ((([RCS (2008)].Date) Between [Enter Beginning Date:] And [Enter Ending Date:]))
GROUP BY [RCS (2008)].[Room/Location];

But when I enter the dates, nothing shows up.

 

[uapak90]

If I take the date field out, everything is good, it groups the room by the name and shows me the total samples from the room, but it does that for all the data in there. Thats the reason I wanted to specify from what to what dates I wanted the data to sort through

 

[Remou]

Thanks, PHV.

What happens if you enter dates that you know exist? Use yyyy-mm-dd format:

SELECT [RCS (2008)].[Room/Location], Sum((((((IIf([Room/Location]="Liquid",10,(IIf([Room/Location]="Compounding",11,(IIf([Room/Location]="Ointment",9,(IIf([Room/Location]="Airlock",1,(IIf([Room/Location]="Corridor",2,(IIf([Room/Location]="Gowning (Prod)",1,""))))))))))))))))) AS TotalNumberOfSamples
FROM [RCS (2008)]
WHERE ((([RCS (2008)].[Date]) Between #1990/01/01# And #2009/12/31#))
GROUP BY [RCS (2008)].[Room/Location];

I guess the date field is date format?

As an aside, date is a reserved word and should not be used to name fields.

 

[uapak90]

I found my error, it was originally in my table. When I put the date field in there, the field name was Date and the data type was text, with the input mask designed for a date. When I changed the data type from text to Date/Time, it worked.
Thanks for all your help!
I will definitely remember this next time