criteria for date range

[steve goodrich]

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in the
"date from" box and the query runs showing starting and ending dates for
every row.

How should I enter the criteria if I want to select a date range. E.g date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[Joan Wild]

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two dates
then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

 

[Douglas J. Steele]

Change your criteria for

<= [StartDate] and >= [EndDate] respectively.

You'll then get 2 prompts: enter 1/1/07 in the first, and 1/14/07 in the
second.

 

[Guest]

Most data sets have a single date field to select a range from.
The records would look like this with 'x' as the record date --
1 x
2 x
3 x
4 x
5 x
A range would be from --
x - x
It would return records 1, 4, & 5. Records 2 & 3 are out of the range.

Your two dates would look like this ---
1 5 11
2 3 6
3 7 12
4 5 13
5 6 10
If you then enter two dates for selection do you want records that have
their dates inside of your dates?
If you use 4 & 8 as the selection dates do you want to return all the
records as every date is within range of 4 to 8? Or if you use 4 & 10 as the
selection dates do you want to return only record 5 as it overlays your
selection?

 

[steve goodrich]

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there would
be one row added to the table with the "date from" being 1st June and the
"date to" being 30th June.
We then run a query daily to pull off the days visitors, using the following
criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain days,
which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query for
say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[Tom Lake]

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Date From >= [Enter Date] and Date To <= [Enter Date]

Tom Lake

 

[Joan Wild]

Thanks for the clarification. It is a bit more complicated than it first
appears:

We also get asked to see if a visitor was in the building on certain days,
which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

You want to see if either the date from or the date to lies within this
range. So if you have
Mr. Jones June9 to July 15
Mr. Smith June 1 to June 30
Mr. Goodrich May 20 to June 7
you would want all these listed, because they were 'in the building' between
those two dates.
<=6/7/06 And >=6/14/06
would only capture Mr. Smith and miss the other two
Using Or wouldn't work either
<=6/7/06 Or >=6/14/06
as that would capture
Mr Cooper May 1 to May 18
Mr Golding June 17 to Jun 28

What you really want is where date from <= June 14 AND date to is >= June 7
and you need to prompt the user for these two dates.

Date From
<=[Enter last date]

Date To

=[Enter start date]

--
Joan Wild
Microsoft Access MVP

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there
would be one row added to the table with the "date from" being 1st June
and the "date to" being 30th June.
We then run a query daily to pull off the days visitors, using the
following criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain days,
which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[steve goodrich]

Thanks Joan (think I emailed you in error - sorry)

I was using the same criteria as you suggested but I got it the wrong way
round. I was entering the start date in the "date from" and the end date in
the "date to"
Which makes me wonder if the "list by date" query I run is setup correctly.
Would you be so kind as to take a look for me.

I want to be prompted for a single date when I click a command button on my
form "list by date" which will show me all visitors for that day.
the criteria I have setup is as follows

Date from date to
<=[enter date] >=[enter date]

I am only prompted once for a date, so I can't understand the criteria in
the "date to" field. all I know is that it seems to work (although I've been
told by other readers that the results may not be correct)
If I remove the criteria from the "date to" field the results are wrong.

Do you think the query is setup correctly?

Many thanks for your time and patience

Steve

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there
would be one row added to the table with the "date from" being 1st June
and the "date to" being 30th June.
We then run a query daily to pull off the days visitors, using the
following criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain days,
which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[Joan Wild]

Hi Steve, you keep changing your request. First it was 'who was in the
building on this date'; then it was 'who was in the building between these
two dates'; now you are back to a single date.

Date from date to
<=[enter date] >=[enter date]

You are only prompted once because the criteria parameter is the same i.e.
[enter date]. It's working for you when you want to know 'who was in the
building on January 19' because what you are saying is Show me the records
where 'date from is less than or equal to January 19 AND date to is greater
than or equal to January 19' .This will return the correct records for a
single date. Note that when you are prompted for the parameter [enter date]
, you only enter a single date and it is used for both the criteria. (since
it is the same criteria for both fields).

If it were
Date from date to
<=[enter from date] >=[enter to date]
then you would get two parameter prompts and you'd have to enter January 19
two times, because the parameters are different.

If you wanted to, you could run your current query as it is, and just re run
it for all the dates you're interested in, however, that is tedious and
unnecessary. You really want to know who was in the building between two
dates. See my other post as that has the correct criteria.

--
Joan Wild
Microsoft Access MVP

Thanks Joan (think I emailed you in error - sorry)

I was using the same criteria as you suggested but I got it the wrong way
round. I was entering the start date in the "date from" and the end date
in
the "date to"
Which makes me wonder if the "list by date" query I run is setup
correctly.
Would you be so kind as to take a look for me.

I want to be prompted for a single date when I click a command button on
my
form "list by date" which will show me all visitors for that day.
the criteria I have setup is as follows

Date from date to
<=[enter date] >=[enter date]

I am only prompted once for a date, so I can't understand the criteria in
the "date to" field. all I know is that it seems to work (although I've
been
told by other readers that the results may not be correct)
If I remove the criteria from the "date to" field the results are wrong.

Do you think the query is setup correctly?

Many thanks for your time and patience

Steve

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there
would be one row added to the table with the "date from" being 1st June
and the "date to" being 30th June.
We then run a query daily to pull off the days visitors, using the
following criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain
days, which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates
for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[steve goodrich]

Joan
we get asked for all sorts of dates, today's,tomorrows, next week, next
month. between two dates,etc.
you have answered all my questions and I thank you for that
many many thanks
Steve

Thanks Joan (think I emailed you in error - sorry)

I was using the same criteria as you suggested but I got it the wrong way
round. I was entering the start date in the "date from" and the end date
in
the "date to"
Which makes me wonder if the "list by date" query I run is setup
correctly.
Would you be so kind as to take a look for me.

I want to be prompted for a single date when I click a command button on
my
form "list by date" which will show me all visitors for that day.
the criteria I have setup is as follows

Date from date to
<=[enter date] >=[enter date]

I am only prompted once for a date, so I can't understand the criteria in
the "date to" field. all I know is that it seems to work (although I've
been
told by other readers that the results may not be correct)
If I remove the criteria from the "date to" field the results are wrong.

Do you think the query is setup correctly?

Many thanks for your time and patience

Steve

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there
would be one row added to the table with the "date from" being 1st June
and the "date to" being 30th June.
We then run a query daily to pull off the days visitors, using the
following criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain
days, which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with perhaps
some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted in
the
"date from" box and the query runs showing starting and ending dates
for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve

 

[Joan Wild]

You're welcome.

--
Joan Wild
Microsoft Access MVP

Joan
we get asked for all sorts of dates, today's,tomorrows, next week, next
month. between two dates,etc.
you have answered all my questions and I thank you for that
many many thanks
Steve

Thanks Joan (think I emailed you in error - sorry)

I was using the same criteria as you suggested but I got it the wrong way
round. I was entering the start date in the "date from" and the end date
in
the "date to"
Which makes me wonder if the "list by date" query I run is setup
correctly.
Would you be so kind as to take a look for me.

I want to be prompted for a single date when I click a command button on
my
form "list by date" which will show me all visitors for that day.
the criteria I have setup is as follows

Date from date to
<=[enter date] >=[enter date]

I am only prompted once for a date, so I can't understand the criteria in
the "date to" field. all I know is that it seems to work (although I've
been
told by other readers that the results may not be correct)
If I remove the criteria from the "date to" field the results are wrong.

Do you think the query is setup correctly?

Many thanks for your time and patience

Steve

There are two date fields in my table "date from" and "date to"
The database is used to book visitors into our building
So for example, if mr smith was visiting for the month of June, there
would be one row added to the table with the "date from" being 1st June
and the "date to" being 30th June.
We then run a query daily to pull off the days visitors, using the
following criteria

"date from" "date to"
<=date() >=date()

We also run a query for visitors expected tomorrow with the following
criteria

"date from" "date to"
<=date()+1 >=date()+1

We also get asked to see if a visitor was in the building on certain
days, which is what I am trying to do.
So using mr smith above as an example, i.e. one row in my table for the
month of June, How would I enter my criteria if I wanted to run a query
for say, the 7th to 14th of June.

I tried the criteria below which I thought might work but as usual I was
wrong

"date from" "date to"
<=6/7/06 >=6/14/06

Any help would be very much appreciated

Steve

Hi Steve, it's not clear to me whether you have two date fields or
one?

If it's one date field and you want to return all records between two
dates then add your date field to the grid and use
Between [Enter start date] and [Enter end date]
as the criteria.

This will prompt the user for the two dates in the range.

If you have two date fields, please explain in more detail (with
perhaps some sample data) what exactly you want returned.

--
Joan Wild
Microsoft Access MVP

I have 2 date fields on my form

"date from" and "date to"

I have a parameter query setup so I can list by a date

Date From Date To

<=[enter date] >=[enter date]

Not sure how that works but it does (I enter the date when prompted
in the
"date from" box and the query runs showing starting and ending dates
for
every row.

How should I enter the criteria if I want to select a date range. E.g
date
from 1/1/07 to 1/14/07

Many Thanks

Steve