Trouble with Criteria

[Scott Reed via AccessMonster.com]

I am trying to figure out if there is a way to sort a field in a table by
just the year, when the field in the table is formatted mm/dd/yyyy? I want to
be able (in design mode of a query) under criteria, just show each field that
is greater than 2007! (>2007 doesn't work)

 

[raskew via AccessMonster.com]

Year([mydate])>2007

HTH - Bob

I am trying to figure out if there is a way to sort a field in a table by
just the year, when the field in the table is formatted mm/dd/yyyy? I want to
be able (in design mode of a query) under criteria, just show each field that
is greater than 2007! (>2007 doesn't work)

 

[Scott Reed via AccessMonster.com]

Is "mydate" replaced by the table name? such as Year([DateCertified])>2007?

Year([mydate])>2007

HTH - Bob

I am trying to figure out if there is a way to sort a field in a table by
just the year, when the field in the table is formatted mm/dd/yyyy? I want to
be able (in design mode of a query) under criteria, just show each field that
is greater than 2007! (>2007 doesn't work)

 

[Scott Reed via AccessMonster.com]

Is "mydate" replaced by the table name? such as Year([DateCertified])>2007?

Year([mydate])>2007

HTH - Bob

I am trying to figure out if there is a way to sort a field in a table by
just the year, when the field in the table is formatted mm/dd/yyyy? I want to
be able (in design mode of a query) under criteria, just show each field that
is greater than 2007! (>2007 doesn't work)

 

[Scott Reed via AccessMonster.com]

Is "mydate" replaced by the table name? such as Year([DateCertified])>2007?

Year([mydate])>2007

HTH - Bob

I am trying to figure out if there is a way to sort a field in a table by
just the year, when the field in the table is formatted mm/dd/yyyy? I want to
be able (in design mode of a query) under criteria, just show each field that
is greater than 2007! (>2007 doesn't work)

 

[Bob Barrows [MVP]]

I am trying to figure out if there is a way to sort a field in a

Sorting is different from filtering. Based on the rest of this question,
it appears you actually want to filter the data.

table by just the year, when the field in the table is formatted
mm/dd/yyyy? I want to be able (in design mode of a query) under
criteria, just show each field that is greater than 2007! (>2007
doesn't work)

The most efficient way, especially if the field is indexed, is to use an
actual date: either the first day of the year

= #1/1/2008#

or the last day of the prior year:

#12/31/2007#

However, if performance is not an issue, you can put this expression in
the top row of the grid (where a field name would normally go):

Year(datefieldname)

and in the criteria row, put

 

[Scott Reed via AccessMonster.com]

thank you Mr Barrows, >#01/01/2007# works just fine. I need to make
multiple queries for each year, but that works out okay.

 

[Bob Barrows [MVP]]

No, make a single parameterized query. instead of

#01/01/2007#

, use this instead:

DateSerial([Enter Year],1,1)