Using DatePart to find last years data

[Guest]

I am using the following to extract this years sales to date for a report,
DatePart("yyyy",[reports]![rptSalesMaster].[Month]), by comparing the last 4
chars of the dates in the table to the year part of a date input as the
report is run.

I also need to use the same input date to extract the sales for last year. I
suspect I need to used DateAdd in the select criteria but cannot work out how
to extract last years data.

Any ideas?

 

[fredg]

I am using the following to extract this years sales to date for a report,
DatePart("yyyy",[reports]![rptSalesMaster].[Month]), by comparing the last 4
chars of the dates in the table to the year part of a date input as the
report is run.

I also need to use the same input date to extract the sales for last year. I
suspect I need to used DateAdd in the select criteria but cannot work out how
to extract last years data.

Any ideas?

Your report recordsource should be a query.
In the query that is the report's recordsource, add a new column:
Exp:Year([DateField])
As criteria for this column, write:
[Enter the year]
You will be prompted to enter the year when the report is run.

Note: Month is a reserved Access/VBA/Jet word and should not be used
as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[John Vinson]

I am using the following to extract this years sales to date for a report,
DatePart("yyyy",[reports]![rptSalesMaster].[Month]), by comparing the last 4
chars of the dates in the table to the year part of a date input as the
report is run.

I also need to use the same input date to extract the sales for last year. I
suspect I need to used DateAdd in the select criteria but cannot work out how
to extract last years data.

Any ideas?

A date is NOT A TEXT STRING. It's stored as a number, a count of days
since midnight, December 30, 1899.

You'll do better to use the DateSerial() function to get a range of
dates:

= DateSerial([Enter year:], 1, 1) AND < DateSerial([Enter year:] + 1, 1, 1)

will get all the dates in the specified year, using any index on the
date/time field in your table. To specifically get last year use

Year(Date()) - 1

in place of the [Enter year:] prompt.

John W. Vinson[MVP]

 

[Guest]

Thanks, this works.

I am using the following to extract this years sales to date for a report,
DatePart("yyyy",[reports]![rptSalesMaster].[Month]), by comparing the last 4
chars of the dates in the table to the year part of a date input as the
report is run.

I also need to use the same input date to extract the sales for last year. I
suspect I need to used DateAdd in the select criteria but cannot work out how
to extract last years data.

Any ideas?

A date is NOT A TEXT STRING. It's stored as a number, a count of days
since midnight, December 30, 1899.

You'll do better to use the DateSerial() function to get a range of
dates:

= DateSerial([Enter year:], 1, 1) AND < DateSerial([Enter year:] + 1, 1, 1)

will get all the dates in the specified year, using any index on the
date/time field in your table. To specifically get last year use

Year(Date()) - 1

in place of the [Enter year:] prompt.

John W. Vinson[MVP]