MS Access Query help: Entered Date and Prior Year 2

[herkiefan]

I am very new to Access. I have a query that allows the user to enter a date and it will pull revenue numbers in three formats: Current, MTD, YTD. I want to also pull the same information from the prior year. However, I want the user to only enter in the date once. For instance, when they enter 2/12/07, the query will pull the 3 types of revenue numbers from both 2/12/07 and 2/12/06.

Below is the SQL view of my Access Query:

SELECT dbo_PB_REVENUE.AuditDate, dbo_PB_LOCATION.TableGame_ID, Sum(dbo_PB_REVENUE.CashDrop) AS SumOfCashDrop, Sum(dbo_PB_REVENUE.StatisticalWin) AS SumOfStatisticalWin, dbo_PB_REVENUE.Period_ID
FROM dbo_PB_LOCATION INNER JOIN dbo_PB_REVENUE ON dbo_PB_LOCATION.Location_ID = dbo_PB_REVENUE.Location_ID
GROUP BY dbo_PB_REVENUE.AuditDate, dbo_PB_LOCATION.TableGame_ID, dbo_PB_REVENUE.Period_ID, dbo_PB_LOCATION.ParentLocation_ID
HAVING (((dbo_PB_REVENUE.AuditDate)=[ENTER DATE]) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8)))
ORDER BY dbo_PB_LOCATION.TableGame_ID;


I have searched through the threads in this forum and, probably b/c of my inexperience, I am unable to get the advice to work.

I am using Access 2003 to query our OASIS database.

Please help,

And thank you in advance.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

Hi,

Check out DateAdd using year.

Also DateSerial(Year([Your Date])-1, month([Your Date]), day([Your Date]))

Skip,

_[red][/red]

 

[herkiefan]

I used the Expression Builder and tried both the DateAdd and the DateSerial functions. I think I used them correctly, however, the query only returns information for the current date. Below are the SQL views of both attempts:

DateSerial:

HAVING (((dbo_PB_REVENUE.AuditDate)=[ENTER DATE]) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8))) OR (((dbo_PB_REVENUE.AuditDate)=DateSerial(Year([AuditDate])-1,Month([AuditDate]),Day([AuditDate]))) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8)))

DateAdd:

HAVING (((dbo_PB_REVENUE.AuditDate)=[ENTER DATE]) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8))) OR (((dbo_PB_REVENUE.AuditDate)=DateAdd("yyyy",-1,[AuditDate])) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8)))

What am I doing wrong? Also, the query now takes much longer than before to run.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

HAVING (((dbo_PB_REVENUE.AuditDate)=[ENTER DATE]) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8))) OR (((dbo_PB_REVENUE.AuditDate)=DateAdd("yyyy",-1,[[!]ENTER DATE[/!]])) AND ((dbo_PB_REVENUE.Period_ID) In (4,6,8)))

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

 

[herkiefan]

That works awesome!!!!!

This is exactly what I need in the same format I need it. Thanks for the help guys

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein