Ms Access Cross Tab Query - Date Range Filtering-Please Help

[paulcherianc]

I am beginner in ms access. I just wanted to know how can i set a parameter on the following cross tab query to filter dates. Date field is [pdate By Day].
Can any one please help me out to sort out this query.


TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;

 

[CazB]

you just need to use a WHERE clause on your date... what you will need to put depends on whether you're looking for an exact date, or you want it to prompt each time you run the query.... or if you're looking for dates between two dates.... or dates before, or dates after... but to give you a start:

for example - if it's always a certain date then just put in the date
WHERE [PettyMaster Query2].[pdate By Day] = #01/01/2013#

or if you want it to prompt for the date
WHERE [PettyMaster Query2].[pdate By Day] = [Specify Date]

this goes just before the line that starts GROUPBY

If you're prompting for the date, it's probably worth adding the following at the TOP of the SQL

PARAMETERS [Specify Date] DateTime;

 

[CazB]

PARAMETERS [Specify Date] DateTime;
TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
WHERE [PettyMaster Query2].[pdate By Day] = [Specify Date]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;

 

[paulcherianc]

Thanks a lot for your advices:

But when i tried to save the query after encoding your suggestions it gives me a message as attached. Please help!

 

[CazB]

oops, my bad, try putting the WHERE before the GROUPBY

(couldn't test it before I posted as I don't have the right 'data' to test it on )

 

[paulcherianc]

Thanks a million CazB!! You made my day!!!

 

[CazB]

no probs - if you need go for a date range rather than a specific date, just tweak the code a bit - if you don't know how, just ask

 

[paulcherianc]

What will be the coding if i wanted to filter the query by date ranges.. Like Date From and Date till. Advice plz..!

 

[paulcherianc]

no probs - if you need go for a date range rather than a specific date, just tweak the code a bit - if you don't know how, just ask

Can you please help me in this regards

 

[CazB]

PARAMETERS [Specify Date FROM] DateTime, [Specify Date TO] DateTime;

WHERE [PettyMaster Query2].[pdate By Day] between [Specify Date FROM] and [Specify Date TO]

is the 'simple' version - as long as they put dates in both prompts it'll work - but if they leave one or both blank, you'll get nothing...

 

[CazB]

If you want it to work whether they put in no dates, just one of the dates, or both dates, then the WHERE part would need to look more like this: (check the field names in case I got any wrong!)



WHERE (IIf(Not IsNull([Specify Date FROM]), [PettyMaster Query2].[pdate By Day]>=[Specify Date FROM],True)=True) AND (IIf(Not IsNull([Specify Date TO]), [PettyMaster Query2].[pdate By Day]<=[Specify Date TO],True)=True)

 

[paulcherianc]

Thanks a lot for your kind advice. Sorry to bother you again: I tried to make a report based on the above cross tab query. But in report wizard, available fields are not showing. Could you please help me in this regards :-(