Query Criteria Advice

[Sean75db]

I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.

Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:

This month we did 250 units
Last month we did 300 units
so we did 15% less
"Yes my math is not exact"

They don't need to see last months data but i need a way to query the previous record to compare the data

This is the criteria code i use in the query that "Form B" is based on.

[Forms]![Form A]![Report_Start_Date]

I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.

How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.

Thanks for any advice

 

[AOB]

Something like this...

DateAdd('d',-1,[Report_Start_Date])

...will return the previous day? You can change the interval to 'ww' or 'm' for weeks and months (see this link for the full list)

So you should be able to pull both values in the same query, or perform a calculation on both (i.e. division) to get a percentage change

But only if values are definitely available for both dates - make use of Nz to account for possible null values

 

[Sean75db]

Perfect, thank you that is exactly what i needed. I am always forgetting about the simple functions that i can use.

 

[AOB]

No problem - that's why this wonderful forum exists!!