Building query parameters on a field derived from a calculation.

[Guest]

I'm running Access 2002 with a program built in Acess 2000
Using a Select Query, I have two fields brought together for a calculation.
[StartDate] is a Date/Time field and [RunDays] is a number field

Calculation
CalcDate: DateAdd("d",[Rundays],Format([StartDate],"mm/dd/yyyy"))

Parameter
Between Format([Begin],"mm/dd/yyyy") And Format([End],"mm/dd/yyyy")

The query runs and accepts the parameter selections but the results come out
strange. I get the expected results for the most part but also get records
with the year 2004 even though I request 2005. The Day/Month range seem to
match but the year is where I seems to have the problem.
Any ideas??

 

[John Spencer (MVP)]

You are forcing items into text strings. Do not do this.


Calculation
CalcDate: DateAdd("d",[Rundays],[StartDate])

Parameter
Between CDate([Begin]) And CDate([End])

If you are having a problem with the calculated date containg time, you can wrap
the calcdate in the datevalue function to strip off the time.
CalcDate: DateValue(DateAdd("d",[Rundays],[StartDate]))

Note that if you have specified your parameter types in the query then you won't
need to wrap Begin and End in the CDate function. And you might not need that
anyway, but it won't hurt.

 

[Guest]

That did it, Thank you very much!

You are forcing items into text strings. Do not do this.


Calculation
CalcDate: DateAdd("d",[Rundays],[StartDate])

Parameter
Between CDate([Begin]) And CDate([End])

If you are having a problem with the calculated date containg time, you can wrap
the calcdate in the datevalue function to strip off the time.
CalcDate: DateValue(DateAdd("d",[Rundays],[StartDate]))

Note that if you have specified your parameter types in the query then you won't
need to wrap Begin and End in the CDate function. And you might not need that
anyway, but it won't hurt.

I'm running Access 2002 with a program built in Acess 2000
Using a Select Query, I have two fields brought together for a calculation.
[StartDate] is a Date/Time field and [RunDays] is a number field

Calculation
CalcDate: DateAdd("d",[Rundays],Format([StartDate],"mm/dd/yyyy"))

Parameter
Between Format([Begin],"mm/dd/yyyy") And Format([End],"mm/dd/yyyy")

The query runs and accepts the parameter selections but the results come out
strange. I get the expected results for the most part but also get records
with the year 2004 even though I request 2005. The Day/Month range seem to
match but the year is where I seems to have the problem.
Any ideas??