"Between" operation on dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to return all the records between two dates that are entered in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are there as
well. However the query draws a blank. Is there something else I have to do?
 
There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html
 
That's already done - still no result. Japan uses the same date formatting as
the US so that's OK too. I have another query that returns all the records
before a specific date and it works great.

The query is pointing at the textboxes and the output form is pointing at
the query. This is very strange.

Allen Browne said:
There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

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

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

Nick in Tokyo said:
I'm trying to return all the records between two dates that are entered in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are there
as
well. However the query draws a blank. Is there something else I have to
do?
Click to expand...
Click to expand...
 
Any chance that the field has a time component which is preventing the
recors from being included?

For example, if you ask for records between
Between 8/15/04 And 8/16/04
then the record that contains
8/16/04 1am
is not matched because it is after 8/16/04

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

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

Nick in Tokyo said:
That's already done - still no result. Japan uses the same date formatting
as
the US so that's OK too. I have another query that returns all the records
before a specific date and it works great.

The query is pointing at the textboxes and the output form is pointing at
the query. This is very strange.

Allen Browne said:
There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html


Nick in Tokyo said:
I'm trying to return all the records between two dates that are entered
in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are
there
as
well. However the query draws a blank. Is there something else I have
to
do?
Click to expand...
Click to expand...
Click to expand...
 
nope, it's all dates

Allen Browne said:
Any chance that the field has a time component which is preventing the
recors from being included?

For example, if you ask for records between
Between 8/15/04 And 8/16/04
then the record that contains
8/16/04 1am
is not matched because it is after 8/16/04

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

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

Nick in Tokyo said:
That's already done - still no result. Japan uses the same date formatting
as
the US so that's OK too. I have another query that returns all the records
before a specific date and it works great.

The query is pointing at the textboxes and the output form is pointing at
the query. This is very strange.

Allen Browne said:
There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html


I'm trying to return all the records between two dates that are entered
in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are
there
as
well. However the query draws a blank. Is there something else I have
to
do?
Click to expand...
Click to expand...
Click to expand...
 
Cluching at straws, but is the date field indexed?

If so, you could try temporarily dropping the index, and compacting, just in
case the index is corrupt.

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

Reply to group, rather than allenbrowne at mvps dot org.
Nick in Tokyo said:
nope, it's all dates

Allen Browne said:
Any chance that the field has a time component which is preventing the
recors from being included?

For example, if you ask for records between
Between 8/15/04 And 8/16/04
then the record that contains
8/16/04 1am
is not matched because it is after 8/16/04

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

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

Nick in Tokyo said:
That's already done - still no result. Japan uses the same date
formatting
as
the US so that's OK too. I have another query that returns all the
records
before a specific date and it works great.

The query is pointing at the textboxes and the output form is pointing
at
the query. This is very strange.

:

There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html


message
I'm trying to return all the records between two dates that are
entered
in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are
there
as
well. However the query draws a blank. Is there something else I
have
to
do?
Click to expand...
Click to expand...
Click to expand...
 
Hi Nick,

PMFBI

Not to doubt you, but just to be sure,
would you mind trying:
= [Forms]![Licence Search Menu]![SearchDateFrom] And <= [Forms]![Licence
Click to expand...
Search Menu]![SearchDateTo]

Also...is this "purely" Access?

I have seen posts talking about "BETWEEN"
needing "values" reversed to work correctly
in non-Access context.

Apologies again for butting in.

Good luck,

Gary Walter

nope, it's all dates

Allen Browne said:
Any chance that the field has a time component which is preventing the
recors from being included?

For example, if you ask for records between
Between 8/15/04 And 8/16/04
then the record that contains
8/16/04 1am
is not matched because it is after 8/16/04

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

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

Nick in Tokyo said:
That's already done - still no result. Japan uses the same date formatting
as
the US so that's OK too. I have another query that returns all the records
before a specific date and it works great.

The query is pointing at the textboxes and the output form is pointing at
the query. This is very strange.

:

There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html


I'm trying to return all the records between two dates that are entered
in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields are
there
as
well. However the query draws a blank. Is there something else I have
to
do?
Click to expand...
Click to expand...
Click to expand...
 
When you run the query without the form open, you'll get parameter prompts.
If you fill these in, do you get the records you want?

--
Joan Wild
Microsoft Access MVP
nope, it's all dates

Allen Browne said:
Any chance that the field has a time component which is preventing
the recors from being included?

For example, if you ask for records between
Between 8/15/04 And 8/16/04
then the record that contains
8/16/04 1am
is not matched because it is after 8/16/04

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

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

Nick in Tokyo said:
That's already done - still no result. Japan uses the same date
formatting as
the US so that's OK too. I have another query that returns all the
records before a specific date and it works great.

The query is pointing at the textboxes and the output form is
pointing at the query. This is very strange.

:

There is a good chance that Access is misinterpreting the dates.

To help it get it right:
1. Format the text boxes.
Open the Licence Search menu in design view.
Right-click SearchDateFrom, and choose Properties.
On the Format tab, set the Format property to:
Short Date
Access then knows that the control is a date.
Repeat for SearchDateTo.

2. Declare your parameters in the query.
Open your query in design view.
Choose Parameters from the Query menu.
In the dialog box, enter two rows:
[Forms]![Licence Search Menu]![SearchDateFrom] Date/Time
[Forms]![Licence Search Menu]![SearchDateTo] Date/Time
In the context of the query, Access knows these values are dates.

More information:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html


message I'm trying to return all the records between two dates that are
entered in
text boxes.

here's what I have in there....

Between [Forms]![Licence Search Menu]![SearchDateFrom] And
[Forms]![Licence
Search Menu]![SearchDateTo]

The form exists and is named correctly, and the two date fields
are there
as
well. However the query draws a blank. Is there something else I
have to
do?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top