Need to say which year to pull

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi there! Using A02 on XP. Have a table of historical
records and my query needs to feed a report that compares
current year to prior year. On my grid is a field: Year
([Date Recd]) with criteria: Year(Now()) Or Year(Now())-1

While this works great, I just know the next lament will
be, "But what if I want to run it to compare 2 prior
years?" I'd love to stave it off by taking care of it now.

I'd like to utilize the following: One grid has the field
RecYear: Year([Date Recd]) with criteria: Eval("InputBox
('What year do you want to use for current data?','Select
Year',Year(Date()))")

My inquiry is this: How can I get the criteria to also
pull the data for the year supplied by the input box PLUS
data for that year minus one? What I'm trying is not
working. Need help.

Thanks in advance for any help or advice, I really love
working with the access newsgroup. You guys are great!
 
Hi Bonnie,

If I understand you correctly, this is more flexible solution.
You need to ask 2 questions. One is which year to start and the other is
something like how many previous years. The start year could be 2004. The
previous year could be 1, 2, 3, or etc (anything greater than 0).

This is what you need.
with criteria: between [StartYear] and [Enter StartYear] - [Enter # of
PreviousYear]

Help this helps.
 
Try this in the Criteria row under the [Date Recd] field:
= DateSerial([What Year] - 1, 1,1) And < DateSerial([What Year] + 1, 1,
Click to expand...
1)

This relies on:
- A parameter named What Year.

- The DateSerial() function converting the value to dates.

- Using less than one the day after the year finishes (so that Access
returns any record that includes a time component on the final day).

- Not taking the Year() of the field, so that Access can use any index you
have on the date fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bonnie said:
Hi there! Using A02 on XP. Have a table of historical
records and my query needs to feed a report that compares
current year to prior year. On my grid is a field: Year
([Date Recd]) with criteria: Year(Now()) Or Year(Now())-1

While this works great, I just know the next lament will
be, "But what if I want to run it to compare 2 prior
years?" I'd love to stave it off by taking care of it now.

I'd like to utilize the following: One grid has the field
RecYear: Year([Date Recd]) with criteria: Eval("InputBox
('What year do you want to use for current data?','Select
Year',Year(Date()))")

My inquiry is this: How can I get the criteria to also
pull the data for the year supplied by the input box PLUS
data for that year minus one? What I'm trying is not
working. Need help.

Thanks in advance for any help or advice, I really love
working with the access newsgroup. You guys are great!
Click to expand...
 
EXACTAMUNDO! Thanks bunches Allen!
-----Original Message-----
Try this in the Criteria row under the [Date Recd] field:
= DateSerial([What Year] - 1, 1,1) And < DateSerial
Click to expand...
([What Year] + 1, 1,
1)

This relies on:
- A parameter named What Year.

- The DateSerial() function converting the value to dates.

- Using less than one the day after the year finishes (so that Access
returns any record that includes a time component on the final day).

- Not taking the Year() of the field, so that Access can use any index you
have on the date fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi there! Using A02 on XP. Have a table of historical
records and my query needs to feed a report that compares
current year to prior year. On my grid is a field: Year
([Date Recd]) with criteria: Year(Now()) Or Year(Now())- 1

While this works great, I just know the next lament will
be, "But what if I want to run it to compare 2 prior
years?" I'd love to stave it off by taking care of it now.

I'd like to utilize the following: One grid has the field
RecYear: Year([Date Recd]) with criteria: Eval("InputBox
('What year do you want to use for current data?','Select
Year',Year(Date()))")

My inquiry is this: How can I get the criteria to also
pull the data for the year supplied by the input box PLUS
data for that year minus one? What I'm trying is not
working. Need help.

Thanks in advance for any help or advice, I really love
working with the access newsgroup. You guys are great!
Click to expand...


.
Click to expand...
 
Back
Top