A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Between" operation on dates



 
 
Thread Tools Display Modes
  #1  
Old August 18th, 2004, 05:45 AM
Nick in Tokyo
external usenet poster
 
Posts: n/a
Default "Between" operation on dates

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?
  #2  
Old August 18th, 2004, 06:43 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in 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?



  #3  
Old August 18th, 2004, 07:35 AM
Nick in Tokyo
external usenet poster
 
Posts: n/a
Default

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" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in 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?




  #4  
Old August 18th, 2004, 08:08 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in message
...
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" wrote:

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" wrote in 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?



  #5  
Old August 18th, 2004, 09:15 AM
Nick in Tokyo
external usenet poster
 
Posts: n/a
Default

nope, it's all dates

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in message
...
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" wrote:

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" wrote in 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?




  #6  
Old August 18th, 2004, 10:54 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Nick in Tokyo" wrote in message
...
nope, it's all dates

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in message
...
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" wrote:

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" wrote in
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?






  #7  
Old August 18th, 2004, 01:46 PM
Gary Walter
external usenet poster
 
Posts: n/a
Default

Hi Nick,

PMFBI

Not to doubt you, but just to be sure,
would you mind trying:

= [Forms]![Licence Search Menu]![SearchDateFrom] And = [Forms]![Licence

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

"Nick in Tokyo" wrote
nope, it's all dates

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in message
...
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" wrote:

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" wrote in 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?






  #8  
Old August 18th, 2004, 03:00 PM
Joan Wild
external usenet poster
 
Posts: n/a
Default

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

Nick in Tokyo wrote:
nope, it's all dates

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick in Tokyo" wrote in
message ...
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" wrote:

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" wrote in
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?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Default values to load up automatically in a form based on value entered in another form Anthony Dowd Using Forms 8 August 12th, 2004 08:53 AM
Projecting dates from dates and times! Suddes General Discussion 5 July 19th, 2004 01:57 PM
Sumproduct search between Dates Robert Christie Worksheet Functions 5 May 22nd, 2004 03:09 PM
Charting woes with hidden dates dustin pockets Charts and Charting 4 April 15th, 2004 04:20 AM
Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the operation John Colling Worksheet Functions 2 February 21st, 2004 04:44 PM


All times are GMT +1. The time now is 06:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.