Show only this years data 1

[shaunacol]

This should be a simple one but I am not sure what I am doing wrong. My sales table has info in from the last 3 years. IN my queries I only want to show data from the current year. I tried using like "*09" but that didnt seem to work properly plus I would like the criteria to be dynamic and not have to be updated every year. I also tried using the Date() function but could not get that to work either...any ideas?

 

[SkipVought]

Hi,

" I tried using like "*09"

That's bacause a DATE is a NUMBER, not a string.

Whatever your date field...

 Year([YourDateField])=2009

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jedraw]

Here's a couple of things that may help.

I don't think you vcan use the like operator with a Date type field.

For Dates always use [bold]# [/bold] signs as follows
>#12/31/2008#

If you use >#12/31/2005# as the criteria on your SalesDate field, then I think you'll get the answer you're looking for.

You'll have to play with this info to get a generic solution for all years. Something like,

select the highest year in your table, subtract 1 from that and get last year, then use >12/31/ last years value. You'll probably need a small routine to do this.

Good luck.

 

[dhookom]

Actually if you don't want to update it every year, modify Skip's suggestion so part of the WHERE clause of your query is:

  WHERE Year([YourDateField])=Year(Date())

Duane
Hook'D on Access
MS Access MVP

 

[shaunacol]

That WHERE clause works wonderfully - many thanks for that!!