DatePart query returns 1899 for all Years

  • Thread starter Thread starter TJ
  • Start date Start date
T

TJ

I am attempting to create query that returns all records
for 2003 from a short date field.

I have tried several approaches, none of which return the
correct date range. (ie parameter queries, "hardcoding"
date using >=#xx/xx/xx#)

I most recently created a calculated field:
disDATE: DatePart("yyyy",[DischrgDate]=2003)

All records, regardless of year, return a value of 1899.
STill, records from 2000,2001,2002............???


TIA
 
disYear: DatePart("yyyy",[DischrgDate])
set the criteria to
2003
You can also use:
disYear: Year([DischrgDate])
 
I most recently created a calculated field:
disDATE: DatePart("yyyy",[DischrgDate]=2003)

All records, regardless of year, return a value of 1899.
STill, records from 2000,2001,2002............???
Click to expand...

The DatePart function's second argument should be a date. In your
example, you have the second argument as an expression:

[DischargDate] = 2003

Since Access stores Date/Time fields as a count of days since
midnight, December 30, 1899, this expression will be TRUE (which is
represented by -1) if DischargDate is equal to June 25, 1905 and FALSE
(equal to 0) otherwise.

In the first case, the expression will be equal to the date/time value
December 29, 1899; in the latter, December 30, 1899.

The DatePart will be 1899 in either case.

As noted elsethread, just use

DisDate: Year([DischrgDate])

and use a criterion of

2003

to find all dates in that year.
 
Back
Top