how to find date between two date in query

[Jon]

Greeting,
I have a form(search form) has two textboxes , one of them for start date
and the other for end date. Also, I have a table which has many fields one of
them is Expiry date. I create a form (filter by advisor form) has a subform
for displaying the all records locating between the range (Start txt box and
end txt box). The subform has a query which has all fields and the Expiry
date field has criteria as follows:
Between [Forms]![filter by advisor form]![ Start txt box] And
[Forms]![filter by advisor form]![ end txt box] . Now when I put the two date
in start and end text box and click on a button to open (filter by advisor
form) form the form open but the sub form dose not have any records. Can any
body help me or tell me a better idea??
Thanks

 

[Allen Browne]

Possible issues here:

1. Access may not understand that the text boxes contain dates.
In form design view, set the Format property of both text boxes to:
General Date
It now knows they are meant to be dates (and won't accept bad dates.)

2. JET may not interpret the text boxes as dates.
In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog, like this:
[Forms]![filter by advisor form]![ Start txt box] Date/Time
[Forms]![filter by advisor form]![ end txt box] Date/Time
The query will then interpret the dates correctly.
(Do you really have leading spaces in the name of those text boxes?)

3. Move the cursor out of the text boxes before you run the query.
The Value of the text box does not get updated until the cursor moves out of
the box.

If you are ready to move up to creating a more involved search form with
boxes for several fields, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Jon]

Thank you Allen but the problem is still when i try to run the query through
the subform. When I put the two date and click to open the form to display
the result the form open and the sub form does not have a data. The query is
running ok

Possible issues here:

1. Access may not understand that the text boxes contain dates.
In form design view, set the Format property of both text boxes to:
General Date
It now knows they are meant to be dates (and won't accept bad dates.)

2. JET may not interpret the text boxes as dates.
In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog, like this:
[Forms]![filter by advisor form]![ Start txt box] Date/Time
[Forms]![filter by advisor form]![ end txt box] Date/Time
The query will then interpret the dates correctly.
(Do you really have leading spaces in the name of those text boxes?)

3. Move the cursor out of the text boxes before you run the query.
The Value of the text box does not get updated until the cursor moves out of
the box.

If you are ready to move up to creating a more involved search form with
boxes for several fields, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Greeting,
I have a form(search form) has two textboxes , one of them for start date
and the other for end date. Also, I have a table which has many fields one
of
them is Expiry date. I create a form (filter by advisor form) has a
subform
for displaying the all records locating between the range (Start txt box
and
end txt box). The subform has a query which has all fields and the Expiry
date field has criteria as follows:
Between [Forms]![filter by advisor form]![ Start txt box] And
[Forms]![filter by advisor form]![ end txt box] . Now when I put the two
date
in start and end text box and click on a button to open (filter by advisor
form) form the form open but the sub form dose not have any records. Can
any
body help me or tell me a better idea??
Thanks

 

[Allen Browne]

You said you had a form with the text boxes for the dates, and you then open
another form with a subform.

Does the original form stay open? It needs to.

Is there anything in the Link Master Fields or Link Child Fields property of
your subform? If so, this might prevent it showing the records.


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen but the problem is still when i try to run the query
through
the subform. When I put the two date and click to open the form to display
the result the form open and the sub form does not have a data. The query
is
running ok

Possible issues here:

1. Access may not understand that the text boxes contain dates.
In form design view, set the Format property of both text boxes to:
General Date
It now knows they are meant to be dates (and won't accept bad dates.)

2. JET may not interpret the text boxes as dates.
In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog, like this:
[Forms]![filter by advisor form]![ Start txt box] Date/Time
[Forms]![filter by advisor form]![ end txt box] Date/Time
The query will then interpret the dates correctly.
(Do you really have leading spaces in the name of those text boxes?)

3. Move the cursor out of the text boxes before you run the query.
The Value of the text box does not get updated until the cursor moves out
of
the box.

If you are ready to move up to creating a more involved search form with
boxes for several fields, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Greeting,
I have a form(search form) has two textboxes , one of them for start
date
and the other for end date. Also, I have a table which has many fields
one
of
them is Expiry date. I create a form (filter by advisor form) has a
subform
for displaying the all records locating between the range (Start txt
box
and
end txt box). The subform has a query which has all fields and the
Expiry
date field has criteria as follows:
Between [Forms]![filter by advisor form]![ Start txt box] And
[Forms]![filter by advisor form]![ end txt box] . Now when I put the
two
date
in start and end text box and click on a button to open (filter by
advisor
form) form the form open but the sub form dose not have any records.
Can
any
body help me or tell me a better idea??
Thanks