Dates between

On an input form the user can query an item between 2 dates that they infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or nothing at
all.

with thanks

Bob

=format([text20],"short date") and =format([Text18],"short date")

Rick B


"Box 666" wrote in message
...
On an input form the user can query an item between 2 dates that they infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or nothing at
all.

with thanks

Bob

Sorry Rick but your solution still does not work, if I put in both dates as
04/05/2004 then I get no results back, If I put in 04/05/2004 and 05/05/2004
then I get back results for just the 04/05/2004. (and yes there are items on
05/05/2004)
Any other suggestions.

Bob
"Rick B" wrote in message
...
=format([text20],"short date") and =format([Text18],"short date")

Rick B


"Box 666" wrote in message
...
On an input form the user can query an item between 2 dates that they
infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or nothing
at
all.

with thanks

Bob

On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote:

On an input form the user can query an item between 2 dates that they infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or nothing at
all.

with thanks

Bob

Bob,
You are mistaken.
Between 1/1/2004 and 1/31/2004 will return records of the first and
last dates, inclusive.
If, however, your date field includes a time value, i.e. 1/31/2004
10:15 AM, it will not return the last date's records as 1/31/2004
midnight is earlier than 1/31/2004 10:15 AM.
The above will occur if the date field is filled using Now() which
includes a time of day value, instead of Date(), which time value is
always midnight.

You can either change the parameters to
Between [forms].[main menu].[text18] And ([forms].[main menu].[text20]
)+1

(Note the addition of parenthesis in the 2nd parameter)
in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....
or....
use your current parameters but enter a date 1 day after you wish,
i.e. enter 2/1/2004 instead of 1/31/2004.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Fred,
Thank you for your thoughts, You are correct the original date field is
populated from a Now().

I have tried as suggested and added the parenthesis in the 2nd parameter
plus +1, but I now get the answer of "Too complex to run".

The input text boxes were set as short date I have changed that to general
date, in the actual query itself I have also clicked on the criterias and
set that to general date as well. Is that what you meant by "in which case
you must also set the Parameters dialog to include both parameters as
Date/Time "

As it still does not work I feel I have miss understood the above paragraph.

Bob



"fredg" wrote in message
...
On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote:

On an input form the user can query an item between 2 dates that they
infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question
how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or
nothing at
all.

with thanks

Bob

Bob,
You are mistaken.
Between 1/1/2004 and 1/31/2004 will return records of the first and
last dates, inclusive.
If, however, your date field includes a time value, i.e. 1/31/2004
10:15 AM, it will not return the last date's records as 1/31/2004
midnight is earlier than 1/31/2004 10:15 AM.
The above will occur if the date field is filled using Now() which
includes a time of day value, instead of Date(), which time value is
always midnight.

You can either change the parameters to
Between [forms].[main menu].[text18] And ([forms].[main menu].[text20]
)+1

(Note the addition of parenthesis in the 2nd parameter)
in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....
or....
use your current parameters but enter a date 1 day after you wish,
i.e. enter 2/1/2004 instead of 1/31/2004.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

On Tue, 08 Jun 2004 22:23:52 GMT, Box 666 wrote:

See comments interspersed below.
Fred,
Thank you for your thoughts, You are correct the original date field is
populated from a Now().

Give me 2 points. :-)

I have tried as suggested and added the parenthesis in the 2nd parameter
plus +1, but I now get the answer of "Too complex to run".

You missed the point of this part of my reply. :-(

in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....

This is done in the query, not on the form.

Open the Query in Design View.
Click on the Query menu button.
Select Parameters from the drop-down.

Write
[forms].[main menu].[text18]
(with the brackets, as shown) on the left side of the dialog.
Write Date/Time on the right side.
Do the same for the other part of the parameter.
Exit the dialog.

Open the Query SQL window.
The first line should read

PARAMETERS [forms]![main menu]![Text18] DateTime, [forms]![main
menu]![text20] DateTime;

The input text boxes were set as short date I have changed that to general
date,

The date format in the form control is irrelevant.
Set it to whatever your normal date data entry is.

in the actual query itself I have also clicked on the criterias and
set that to general date as well.

Also not relevant.

Is that what you meant by "in which case
you must also set the Parameters dialog to include both parameters as
Date/Time "
Nope.
Explained in the top section.

As it still does not work I feel I have miss understood the above paragraph.

Bob


Keep going. Your almost there.

Fred

"fredg" wrote in message
...
On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote:

On an input form the user can query an item between 2 dates that they
infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question
how
can I set it so that it includes BOTH dates listed. I have tried several
permutations, I either get everything in the database returned or
nothing at
all.

with thanks

Bob

Bob,
You are mistaken.
Between 1/1/2004 and 1/31/2004 will return records of the first and
last dates, inclusive.
If, however, your date field includes a time value, i.e. 1/31/2004
10:15 AM, it will not return the last date's records as 1/31/2004
midnight is earlier than 1/31/2004 10:15 AM.
The above will occur if the date field is filled using Now() which
includes a time of day value, instead of Date(), which time value is
always midnight.

You can either change the parameters to
Between [forms].[main menu].[text18] And ([forms].[main menu].[text20]
)+1

(Note the addition of parenthesis in the 2nd parameter)
in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....
or....
use your current parameters but enter a date 1 day after you wish,
i.e. enter 2/1/2004 instead of 1/31/2004.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

On Tue, 08 Jun 2004 21:24:36 GMT, "Box 666"
wrote:

Sorry Rick but your solution still does not work, if I put in both dates as
04/05/2004 then I get no results back, If I put in 04/05/2004 and 05/05/2004
then I get back results for just the 04/05/2004. (and yes there are items on
05/05/2004)
Any other suggestions.

Since Access was written mostly in Redford, Washington, USA, by
Americans, it is OBLIGATORY to use either American-style mm/dd/yyyy
dates or an unambiguous format such as 04-May-2004.

Try

=Format([Forms]![Main Menu]![txtStartdate], "mm\/dd\/yyyy") AND Format([Forms]![Main Menu]![txtEndDate], "mm\/dd\/yyyy") + 1

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Fred,
Thank you finally got there, I had no idea that "such a simple" change
was going to be so confusing. Next time I will tell the input clerk to add a
day on to the closing date ;-)) Just joking, a good learning point thank
you.

Bob


"fredg" wrote in message
.. .
On Tue, 08 Jun 2004 22:23:52 GMT, Box 666 wrote:

See comments interspersed below.
Fred,
Thank you for your thoughts, You are correct the original date field
is
populated from a Now().

Give me 2 points. :-)

I have tried as suggested and added the parenthesis in the 2nd parameter
plus +1, but I now get the answer of "Too complex to run".

You missed the point of this part of my reply. :-(

in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....

This is done in the query, not on the form.

Open the Query in Design View.
Click on the Query menu button.
Select Parameters from the drop-down.

Write
[forms].[main menu].[text18]
(with the brackets, as shown) on the left side of the dialog.
Write Date/Time on the right side.
Do the same for the other part of the parameter.
Exit the dialog.

Open the Query SQL window.
The first line should read

PARAMETERS [forms]![main menu]![Text18] DateTime, [forms]![main
menu]![text20] DateTime;

The input text boxes were set as short date I have changed that to
general
date,

The date format in the form control is irrelevant.
Set it to whatever your normal date data entry is.

in the actual query itself I have also clicked on the criterias and
set that to general date as well.

Also not relevant.

Is that what you meant by "in which case
you must also set the Parameters dialog to include both parameters as
Date/Time "
Nope.
Explained in the top section.

As it still does not work I feel I have miss understood the above
paragraph.

Bob


Keep going. Your almost there.

Fred

"fredg" wrote in message
...
On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote:

On an input form the user can query an item between 2 dates that they
infill
in two unbound text boxes.

At the moment in the date field of the query criteria field I use
Between [forms].[main menu].[text18] And [forms].[main menu].[text20]

As the name suggests it returns items "between" the dates in question
how
can I set it so that it includes BOTH dates listed. I have tried
several
permutations, I either get everything in the database returned or
nothing at
all.

with thanks

Bob

Bob,
You are mistaken.
Between 1/1/2004 and 1/31/2004 will return records of the first and
last dates, inclusive.
If, however, your date field includes a time value, i.e. 1/31/2004
10:15 AM, it will not return the last date's records as 1/31/2004
midnight is earlier than 1/31/2004 10:15 AM.
The above will occur if the date field is filled using Now() which
includes a time of day value, instead of Date(), which time value is
always midnight.

You can either change the parameters to
Between [forms].[main menu].[text18] And ([forms].[main menu].[text20]
)+1

(Note the addition of parenthesis in the 2nd parameter)
in which case you must also set the Parameters dialog to include both
parameters as Date/Time .... Query + Parameters ....
or....
use your current parameters but enter a date 1 day after you wish,
i.e. enter 2/1/2004 instead of 1/31/2004.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

"John Vinson" wrote in message
Since Access was written mostly in Redford, Washington, USA

Is that a suburb of Redmond of which I'm not aware, John? g

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)