How to find privious month,day,week,qtr data

[sushmitha]

I have a table with date and amount values. We are running history reports.

I need to calculate Daily, Weekly, Monthly, Quartely data for current year and prior year for last 12 days, weeks, months and quarters

Please find the attachement Date Conversion to see my requirement
How to manipulate date for the required criteria

I know that we can use Dateadd function to go to previous month and previous year. But how can I go to previous 12months, weeks, days, qtrs etc

 

[The_Doc_Man]

Look at Access Help on DateAdd more closely, then think a bit.

DateAdd( interval, number, date ) returns a date that is the given number of intervals greater (or less if "number" is negative) than the given date.

Interval of "yyyy" and number = 1 gives you one calendar year difference.
Interval of "q" and number = 1 gives you one calendar quarter.
Interval of "m" and number = 1 gives you one calendar month.

You named DateAdd but then asked that question anyway. I have to think that you either didn't read the Access Help for DateAdd or you don't understand it or you didn't ask your question clearly enough. DateAdd will give you exactly what date you want for every case you mentioned. Then just a query with all data between two reference dates. Is that your problem, how to filter? Because your question came off more about dates than data filtering.

Just in case filtering is your problem, look up the syntax of "BETWEEN ... AND ..." (which is SQL syntax) for how to select data in a specific range. That range CAN be date-oriented if required.

 

[sushmitha]

Look at Access Help on DateAdd more closely, then think a bit.

DateAdd( interval, number, date ) returns a date that is the given number of intervals greater (or less if "number" is negative) than the given date.

Interval of "yyyy" and number = 1 gives you one calendar year difference.
Interval of "q" and number = 1 gives you one calendar quarter.
Interval of "m" and number = 1 gives you one calendar month.

You named DateAdd but then asked that question anyway. I have to think that you either didn't read the Access Help for DateAdd or you don't understand it or you didn't ask your question clearly enough. DateAdd will give you exactly what date you want for every case you mentioned. Then just a query with all data between two reference dates. Is that your problem, how to filter? Because your question came off more about dates than data filtering.

Just in case filtering is your problem, look up the syntax of "BETWEEN ... AND ..." (which is SQL syntax) for how to select data in a specific range. That range CAN be date-oriented if required.

Thank you for your reply. I am able to display month values. But I am having problem in filtering between Current year and prior year.

For Ex: If calculating Monthly values, Current year should give only upto 11months data where as prior should give for the entire previous year

I am confused in finding right syntax when using crateria for these queries

 

[sushmitha]

Look at Access Help on DateAdd more closely, then think a bit.

DateAdd( interval, number, date ) returns a date that is the given number of intervals greater (or less if "number" is negative) than the given date.

Interval of "yyyy" and number = 1 gives you one calendar year difference.
Interval of "q" and number = 1 gives you one calendar quarter.
Interval of "m" and number = 1 gives you one calendar month.

You named DateAdd but then asked that question anyway. I have to think that you either didn't read the Access Help for DateAdd or you don't understand it or you didn't ask your question clearly enough. DateAdd will give you exactly what date you want for every case you mentioned. Then just a query with all data between two reference dates. Is that your problem, how to filter? Because your question came off more about dates than data filtering.

Just in case filtering is your problem, look up the syntax of "BETWEEN ... AND ..." (which is SQL syntax) for how to select data in a specific range. That range CAN be date-oriented if required.

I am having difficulty in selecting crateria. I need to display last 12 moths, weeks, days, qts data for current year as well as prior year.

Can anybody helps me on this ??