Using form controls for a parameter

[LeAnn]

Ok, I'm very frustrated. I have never been able to get this technique to
work. I have Access 2003.

I have followed the instructions and examples in this forum as well as from
this site: http://www.fontstuff.com/access/acctut08.htm

I have a form called frmDate. On it I have 2 controls: BDte and EDte for
users to enter a date range.

In my query under the appropriate date field I have:
Between [Forms]![frmDate]![BDte] And [Forms]![frmDate]![EDte]

To test this I left the query open in design view, open the frmDate form and
enter valid dates. I leave the form open and run my query. I get NO
records. I tried a more simple exampe with another field and control and get
the same result.

What am I doing wrong? This seems like it should be very simple (at this
stage anyway - I have much more do after I get this part to work).

Thanks
LeAnn

 

[fredg]

Ok, I'm very frustrated. I have never been able to get this technique to
work. I have Access 2003.

I have followed the instructions and examples in this forum as well as from
this site: http://www.fontstuff.com/access/acctut08.htm

I have a form called frmDate. On it I have 2 controls: BDte and EDte for
users to enter a date range.

In my query under the appropriate date field I have:
Between [Forms]![frmDate]![BDte] And [Forms]![frmDate]![EDte]

To test this I left the query open in design view, open the frmDate form and
enter valid dates. I leave the form open and run my query. I get NO
records. I tried a more simple exampe with another field and control and get
the same result.

What am I doing wrong? This seems like it should be very simple (at this
stage anyway - I have much more do after I get this part to work).

Thanks
LeAnn

Let's run a test.
Close the Form.
Close the query.
Run the query. You should get a parameter prompt for
[Forms]![frmDate]![BDte] then another prompt for
[Forms]![frmDate]![EDte].
Enter valid dates when prompted.
Do you get the correct records returned?
If yes, the query is OK.
Close the query (completely).

If you do not get the correct records, the problem may be that your
date field has a time value stored and no records are within the
midnight to midnight of the 2 dates entered.

Open the frmDate.
Enter the same dates in the 2 text controls.
With the form open, open the query.
Do you get the same records?
If so all is correct between Form and Query.

If not, then your problem is in the form, and you'll need to see if
there is any other code running which will affect the result.

 

[John Spencer]

LeAnn,

Try

Between CDate([Forms]![frmDate]![BDte]) And CDate([Forms]![frmDate]![EDte])

If that works then the problem was that your entry on the form was not being
understood as a date entry. Access was doing math on the entry and
calculating a small fraction and then changing that over to a date.

You may be able to take care of that by assigning a date format to the
control on the form.

More robust would be to declare the parameter type.

Open the query in design mode
Select Query: Parameters from the menu
Fill in the EXACT name of the parameter in column 1
[Forms]![frmDate]![BDte] & [Forms]![frmDate]![EDte]
Select the data type of the parameter in column 2 (DateTime for both)

 

[LeAnn]

Thank you John and Fred.

Fred, your testing suggestion helped me realize it wasn't the query. Thanks.

John, I had to try several times but your CDate suggestion worked. Prior to
posting I did try to format the controls on the form to dates but that did
not have any effect.

Thanks to both for your assistance.

LeAnn,

Try

Between CDate([Forms]![frmDate]![BDte]) And CDate([Forms]![frmDate]![EDte])

If that works then the problem was that your entry on the form was not being
understood as a date entry. Access was doing math on the entry and
calculating a small fraction and then changing that over to a date.

You may be able to take care of that by assigning a date format to the
control on the form.

More robust would be to declare the parameter type.

Open the query in design mode
Select Query: Parameters from the menu
Fill in the EXACT name of the parameter in column 1
[Forms]![frmDate]![BDte] & [Forms]![frmDate]![EDte]
Select the data type of the parameter in column 2 (DateTime for both)

Ok, I'm very frustrated. I have never been able to get this technique to
work. I have Access 2003.

I have followed the instructions and examples in this forum as well as
from
this site: http://www.fontstuff.com/access/acctut08.htm

I have a form called frmDate. On it I have 2 controls: BDte and EDte for
users to enter a date range.

In my query under the appropriate date field I have:
Between [Forms]![frmDate]![BDte] And [Forms]![frmDate]![EDte]

To test this I left the query open in design view, open the frmDate form
and
enter valid dates. I leave the form open and run my query. I get NO
records. I tried a more simple exampe with another field and control and
get
the same result.

What am I doing wrong? This seems like it should be very simple (at this
stage anyway - I have much more do after I get this part to work).

Thanks
LeAnn