Parameter Query - Before a certain Date

[Carol Frangipane]

I have an EndTerm field (this is a calculated field); I want to set up a parameter query that enables me to type in a date which will retrieve any records BEFORE that date. I've used < [Enter date] and results were not right. i.e., I entered 1/1/2007 in the parameter and the results came back with records dated 1/1/yyyy instead of anything before 2007.

 

[boblarson]

I just tested < [Enter date] with a date field of mine and it worked fine. What does EndTerm return (what values)?

 

[Carol Frangipane]

It is a calculated field in my query which adds the No. of years in the field "TERM" to the "CONTREFF" field. So if a contract effective date is 1/1/2000 and has a term of 5 years, the EndTerm field would produce 1/1/2005. This is how I wrote it and that is working fine.

EndTerm: DateSerial(Year([CONTREFF])+[TERM],Month([CONTREFF]),Day([CONTREFF]))

I just can't seem to do the parameter query and get the right result.

 

[boblarson]

Okay, that's the problem. But, the EASY solution is:

Use this instead:

< CDate([Enter Date])

 

[ajetrumpet]

Carol,

I am wondering if there is a problem with your criteria vs. calculation. I've done the "< [ENTER DATE]" function as well and it works fine if you have entered date strings. I am thinking that Access is searching for a string and not a calculation.

 

[ajetrumpet]

CDate??
Hey Bob, is this a universal code for functions??
ADD "C" for calculated objects or controls??

 

[boblarson]

CDate??
Hey Bob, is this a universal code for functions??
ADD "C" for calculated objects or controls??

No, they are conversion functions:

CDate - convert to date
CStr - to string
CDbl - to double
CSng - to single
CLng - to long
etc.

 

[Carol Frangipane]

Bob Larson...You're the Greatest!

That did the trick. I will look that one up and read more about it. Thanks Again...CAROL