Dates between

[Box 666]

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

 

[Rick B]

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

Rick B


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

 

[Box 666]

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

 

[fredg]

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.

 

[Box 666]

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

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.

 

[fredg]

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. :-(

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

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.

 

[John Vinson]

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

 

[Box 666]

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

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. :-(

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

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.

 

[Douglas J. Steele]

Since Access was written mostly in Redford, Washington, USA

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