Text Date Parameter Query

[Guest]

I am trying to use a parameter query to get results between a start and end
date. The problem is that the date field is stored as a text field, and I
cannot change this. I added a new field in my query, which translates the
text into a date field, using cdate (ex: Date: cdate([reviewdate]). I then
added "Between [forms]![frmMonthlyReport]![startdate] And
[forms]![frmmonthlyReport]![enddate]" to the criteria row of the cdate field,
but this is not working. When I try using specific dates instead of the
parameter, such as "between #1/1/2004# and #6/1/2005#", it works perfect.
I've tried several other modifications to the criteria, but nothing with the
parameter seems to be working. It will only give me a few dates in the
result set. Does anyone know what's happening?

 

[Duane Hookom]

Is the form open when you run the query?

 

[Van T. Dinh]

If making sure the frmMonthlyReport is open and the Controls [startdate] and
[enddate] as Duane advised doesn't solve the problem, try also declaring the
data type of the Parameters.

I found in some cases, Access doesn't guess the data type correctly when
unbound Controls are used as Parameters in the Queries.

 

[Guest]

It worked! I changed the format on the unbound control to mm/yyyy and that
did it. Thank you both so much!!

If making sure the frmMonthlyReport is open and the Controls [startdate] and
[enddate] as Duane advised doesn't solve the problem, try also declaring the
data type of the Parameters.

I found in some cases, Access doesn't guess the data type correctly when
unbound Controls are used as Parameters in the Queries.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to use a parameter query to get results between a start and end
date. The problem is that the date field is stored as a text field, and I
cannot change this. I added a new field in my query, which translates the
text into a date field, using cdate (ex: Date: cdate([reviewdate]). I then
added "Between [forms]![frmMonthlyReport]![startdate] And
[forms]![frmmonthlyReport]![enddate]" to the criteria row of the cdate field,
but this is not working. When I try using specific dates instead of the
parameter, such as "between #1/1/2004# and #6/1/2005#", it works perfect.
I've tried several other modifications to the criteria, but nothing with the
parameter seems to be working. It will only give me a few dates in the
result set. Does anyone know what's happening?