limit Criteria from a date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that performs a calcualtion based on a date:
Age: ([DATE]-[TR_DATE]).

I need to add to the criteria >50

But the error says Arithmatic overflow error, guess because it was a date
value to begin with.

I tried creating anothe field from that one: Age at Panel: ([Age]>50) which
give me a -1 or 0 but I still cannot make the criteria = -1 or 0.

I tried another query but it just carries over the same issues.

How do I allow only >50 from this calculation????
 
Dan @BCBS said:
I have a field that performs a calcualtion based on a date:
Age: ([DATE]-[TR_DATE]).

I need to add to the criteria >50

But the error says Arithmatic overflow error, guess because it was a date
value to begin with.

I tried creating anothe field from that one: Age at Panel: ([Age]>50) which
give me a -1 or 0 but I still cannot make the criteria = -1 or 0.

I tried another query but it just carries over the same issues.

How do I allow only >50 from this calculation????
Click to expand...


Don't try to do arithmetic calculations on date/time values.
Instead, you should use the date related functions, in this
case the DateDiff function (search VBA Help for details).
Pay careful attention to the details of what the function
do, because a true age calculation needs to take the year,
month and day into account. See
http://www.mvps.org/access/datetime/date0001.htm

Your calculation will result in a value that is
**approximately** the number of days between the two dates.

Once you get that sorted out, the criteria >50 should work
the way you expected it to work.
 
Thanks - thru that information I did it...

Marshall Barton said:
Dan @BCBS said:
I have a field that performs a calcualtion based on a date:
Age: ([DATE]-[TR_DATE]).

I need to add to the criteria >50

But the error says Arithmatic overflow error, guess because it was a date
value to begin with.

I tried creating anothe field from that one: Age at Panel: ([Age]>50) which
give me a -1 or 0 but I still cannot make the criteria = -1 or 0.

I tried another query but it just carries over the same issues.

How do I allow only >50 from this calculation????
Click to expand...


Don't try to do arithmetic calculations on date/time values.
Instead, you should use the date related functions, in this
case the DateDiff function (search VBA Help for details).
Pay careful attention to the details of what the function
do, because a true age calculation needs to take the year,
month and day into account. See
http://www.mvps.org/access/datetime/date0001.htm

Your calculation will result in a value that is
**approximately** the number of days between the two dates.

Once you get that sorted out, the criteria >50 should work
the way you expected it to work.
Click to expand...
 
Back
Top