Queries using different date ranges

[Steve Elliott]

I have a query set up to gather together data between two specified dates.
Shown in the query column as:

Between #24/09/2004# And #01/10/2004#

Is it possible to enter several different date ranges, ie between 24/09/2004
and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?

If I enter the "Between" criteria on different lines it returns no data.

Some pointers would be useful - thanks.

I'm using Access 2000 on Win XP Home with SP2 installed.

Steve.

[PC Datasheet]

Pull down your date field twice into the query grid. Put he following in the
criteria under the first one:
Between #24/09/2004# And #01/10/2004#
and put he following in the criteria under the second one:
Between #05/10/2004# And #07/10/2004#

Note: both criteria expressions must be in the same row

When you have criteria set for more than one field in the same row, Access
treats them as AND conditions.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...

I have a query set up to gather together data between two specified dates.
Shown in the query column as:

Between #24/09/2004# And #01/10/2004#

Is it possible to enter several different date ranges, ie between 24/09/2004 and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?

If I enter the "Between" criteria on different lines it returns no data.

Some pointers would be useful - thanks.

I'm using Access 2000 on Win XP Home with SP2 installed.

Steve.

[Steve Elliott]

Hi, thanks for your suggestion, although I still can't get it to work. My
two DATE columns are shown thus:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or:

Same in the next column of the query grid, with the other "between" dates.

Access 2000 returns no data. I have 9 columns within the query grid.

I can't understand why Access wouldn't just return a grand total for these
two sets of dates.

Thanks,

Steve.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Oj***************@newsread2.news.atl.earthlin k.net...

Pull down your date field twice into the query grid. Put he following in
the
criteria under the first one:
Between #24/09/2004# And #01/10/2004#
and put he following in the criteria under the second one:
Between #05/10/2004# And #07/10/2004#

Note: both criteria expressions must be in the same row

When you have criteria set for more than one field in the same row, Access
treats them as AND conditions.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...

I have a query set up to gather together data between two specified
dates.
Shown in the query column as:

Between #24/09/2004# And #01/10/2004#

Is it possible to enter several different date ranges, ie between

24/09/2004

and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?

If I enter the "Between" criteria on different lines it returns no data.

Some pointers would be useful - thanks.

I'm using Access 2000 on Win XP Home with SP2 installed.

Steve.

[PC Datasheet]

Steve,

(from one Steve to another!)

Change your query to a Select query. Be sure to include all the fields you
want in your crosstab as well as the date field twice. Consider this query
as the "data" query. Once you have this query returning the records you
want, create a new crosstab query based on the first query. Only include the
fields you want to report. The criteria will be in the first query so you
don't need any criteria in the crosstab.

Before you do the above however, change the name of the field you named
"Date". Date is a reserved word in Access and you should not use it for the
name of anything.

Steve
PC Datasheet
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@newsg2.svr.pol.co.uk...

Hi, thanks for your suggestion, although I still can't get it to work. My
two DATE columns are shown thus:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or:

Same in the next column of the query grid, with the other "between" dates.

Access 2000 returns no data. I have 9 columns within the query grid.

I can't understand why Access wouldn't just return a grand total for these
two sets of dates.

Thanks,

Steve.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Oj***************@newsread2.news.atl.earthlin k.net...

Pull down your date field twice into the query grid. Put he following in
the
criteria under the first one:
Between #24/09/2004# And #01/10/2004#
and put he following in the criteria under the second one:
Between #05/10/2004# And #07/10/2004#

Note: both criteria expressions must be in the same row

When you have criteria set for more than one field in the same row, Access treats them as AND conditions.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...

I have a query set up to gather together data between two specified
dates.
Shown in the query column as:

Between #24/09/2004# And #01/10/2004#

Is it possible to enter several different date ranges, ie between

24/09/2004

and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?

If I enter the "Between" criteria on different lines it returns no data.
Some pointers would be useful - thanks.

I'm using Access 2000 on Win XP Home with SP2 installed.

Steve.

[Steve Elliott]

Thanks Steve,

This didn't work either. Have changed the query type to a Select Query, but
still no data is returned when the query is run - only the column headings
appear. Could this be caused by other "and" criteria set up as well as the
dates? I'm somewhat confused.

I can send a screen shot if that helps - these things are always difficult
to explain clearly.

many thanks for your help so far.

Steve.

"PC Datasheet" <no****@nospam.spam> wrote in message
news:m9***************@newsread3.news.atl.earthlin k.net...

Steve,

(from one Steve to another!)

Change your query to a Select query. Be sure to include all the fields you
want in your crosstab as well as the date field twice. Consider this query
as the "data" query. Once you have this query returning the records you
want, create a new crosstab query based on the first query. Only include
the
fields you want to report. The criteria will be in the first query so you
don't need any criteria in the crosstab.

Before you do the above however, change the name of the field you named
"Date". Date is a reserved word in Access and you should not use it for
the
name of anything.

Steve
PC Datasheet
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@newsg2.svr.pol.co.uk...

Hi, thanks for your suggestion, although I still can't get it to work.
My
two DATE columns are shown thus:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or:

Same in the next column of the query grid, with the other "between"
dates.

Access 2000 returns no data. I have 9 columns within the query grid.

I can't understand why Access wouldn't just return a grand total for
these
two sets of dates.

Thanks,

Steve.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Oj***************@newsread2.news.atl.earthlin k.net...

> Pull down your date field twice into the query grid. Put he following
> in
> the
> criteria under the first one:
> Between #24/09/2004# And #01/10/2004#
> and put he following in the criteria under the second one:
> Between #05/10/2004# And #07/10/2004#
>
> Note: both criteria expressions must be in the same row
>
> When you have criteria set for more than one field in the same row, Access > treats them as AND conditions.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> re******@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
> news:cl**********@news6.svr.pol.co.uk...
>> I have a query set up to gather together data between two specified
>> dates.
>> Shown in the query column as:
>>
>> Between #24/09/2004# And #01/10/2004#
>>
>> Is it possible to enter several different date ranges, ie between
> 24/09/2004
>> and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?
>>
>> If I enter the "Between" criteria on different lines it returns no data. >>
>> Some pointers would be useful - thanks.
>>
>> I'm using Access 2000 on Win XP Home with SP2 installed.
>>
>> Steve.
>>
>>
>
>

[PC Datasheet]

Go to the database window, queries tab. Right click on your query and make a
copy for testing. In the copy, starting left to right, delete one criteria
and run the query. Repeat until you get records returned. Examine the last
criteria you deleted to determine why that criteria does not return any
records.

Steve
PC Datasheet
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@news8.svr.pol.co.uk...

Thanks Steve,

This didn't work either. Have changed the query type to a Select Query, but still no data is returned when the query is run - only the column headings
appear. Could this be caused by other "and" criteria set up as well as the dates? I'm somewhat confused.

I can send a screen shot if that helps - these things are always difficult
to explain clearly.

many thanks for your help so far.

Steve.

"PC Datasheet" <no****@nospam.spam> wrote in message
news:m9***************@newsread3.news.atl.earthlin k.net...

Steve,

(from one Steve to another!)

Change your query to a Select query. Be sure to include all the fields you want in your crosstab as well as the date field twice. Consider this query as the "data" query. Once you have this query returning the records you
want, create a new crosstab query based on the first query. Only include
the
fields you want to report. The criteria will be in the first query so you don't need any criteria in the crosstab.

Before you do the above however, change the name of the field you named
"Date". Date is a reserved word in Access and you should not use it for
the
name of anything.

Steve
PC Datasheet
"Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
news:cl**********@newsg2.svr.pol.co.uk...

Hi, thanks for your suggestion, although I still can't get it to work.
My
two DATE columns are shown thus:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or:

Same in the next column of the query grid, with the other "between"
dates.

Access 2000 returns no data. I have 9 columns within the query grid.

I can't understand why Access wouldn't just return a grand total for
these
two sets of dates.

Thanks,

Steve.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:Oj***************@newsread2.news.atl.earthlin k.net...
> Pull down your date field twice into the query grid. Put he following
> in
> the
> criteria under the first one:
> Between #24/09/2004# And #01/10/2004#
> and put he following in the criteria under the second one:
> Between #05/10/2004# And #07/10/2004#
>
> Note: both criteria expressions must be in the same row
>
> When you have criteria set for more than one field in the same row,

Access

> treats them as AND conditions.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> re******@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "Steve Elliott" <st***@olympicradio.freeserve.co.uk> wrote in message
> news:cl**********@news6.svr.pol.co.uk...
>> I have a query set up to gather together data between two specified
>> dates.
>> Shown in the query column as:
>>
>> Between #24/09/2004# And #01/10/2004#
>>
>> Is it possible to enter several different date ranges, ie between
> 24/09/2004
>> and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?
>>
>> If I enter the "Between" criteria on different lines it returns no

data.

>>
>> Some pointers would be useful - thanks.
>>
>> I'm using Access 2000 on Win XP Home with SP2 installed.
>>
>> Steve.
>>
>>
>
>

[Steve Elliott]

If I do the date in that format, Access simply changes it to UK date format.
Individual dates and between dates work fine for one set of criteria. It's
when two or more criteria date ranges or individual dates are entered it
returns no result.

#29/09/2004#?!??

When using #'s, dates must be in the American format of MM/DD/YYYY.

[Steve Elliott]

Go to the database window, queries tab. Right click on your query and make
a
copy for testing. In the copy, starting left to right, delete one criteria
and run the query. Repeat until you get records returned. Examine the last
criteria you deleted to determine why that criteria does not return any
records.

Tried this, and still it doesn't work. I have also set up a completely new
query based on different data, and created several date fields, with each
date required in each - this still returns a blank result. I can't see how
Access calculates totals between two sets of dates, but can't work with
individual dates that are not consecutive.

Thanks for all your suggestions though - I'm battling on... there must be a
way.

Steve.

[Salad]

Steve Elliott wrote:

If I do the date in that format, Access simply changes it to UK date format.
Individual dates and between dates work fine for one set of criteria. It's
when two or more criteria date ranges or individual dates are entered it
returns no result.

#29/09/2004#?!??

When using #'s, dates must be in the American format of MM/DD/YYYY.

Have you done a View/SQL from the menu?

If you don't see something like
DateField Between Date1 And Date2 Or _
DateField Between Date3 and Date4
then you are not following the initial instructions...to put each of
your date ranges on separate criteria rows to generate an OR condition,
not an AND.

And if you still can't get it, modify it manually from the SQL view window.

[Steve Elliott]

Success ! If I show dates on SAME line with an OR between each individual
date, Access sums the totals from each of these dates and produces a result.

Logically I would have thought the AND command would do that, but apparently
not.

Thanks for your help.

Steve.

Have you done a View/SQL from the menu?

If you don't see something like
DateField Between Date1 And Date2 Or _
DateField Between Date3 and Date4
then you are not following the initial instructions...to put each of your
date ranges on separate criteria rows to generate an OR condition, not an
AND.

And if you still can't get it, modify it manually from the SQL view
window.

[DenBorg]

"PC Datasheet" wrote:

Pull down your date field twice into the query grid. Put he following in the
criteria under the first one:
Between #24/09/2004# And #01/10/2004#
and put he following in the criteria under the second one:
Between #05/10/2004# And #07/10/2004#

Note: both criteria expressions must be in the same row

When you have criteria set for more than one field in the same row, Access
treats them as AND conditions.

The problem with this is that any given date cannot be between both
ranges at the same time. No records could ever be returned in that
case.

[DenBorg]

"Steve Elliott" wrote:

Logically I would have thought the AND command would do that, but apparently
not.
Think about it this way: By using 'AND', you are saying that the date
must be between both ranges. Any date between 24/9/2004 and 01/10/2004
cannot possible also be between 05/10/2004 and 07/10/2004.

By using 'OR', you are saying, "Give me all records where the date is
either between this range OR that range."

Another analogy would be, "Show me all pets that are both a Cat AND a
Dog" versus "Show me all pets that are either a Cat OR a Dog". If you
asked for the first, it is not likely the pet store would have any
animals that is both a Cat AND a Dog. But if you asked for the second,
you would be shown all cats and dogs in the store.

Make sense?
Success ! If I show dates on SAME line with an OR between each individual
date, Access sums the totals from each of these dates and produces a result.

You would get the same results if you put each Between clause on
separate rows, as in:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or: Between #05/10/2004# And #07/10/2004#

However, if you have criterea in addition to this date range, you
would have to copy that criterea to the second line also.

For example, if your date field must fall between either of those two
ranges and the State must also be 'CA', the column for the state field
would look like:

Field: State
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: 'CA'
Or: 'CA'

Having the 'CA' listed on both lines makes the 'CA' criterea
applicable to both date ranges. In this case, the query would ask the
following 2 questions about each record (1 question per line of
criterea):

1) Does State='CA' and is Date between 24/09/2004 and 01/10/2004?
2) Does State='CA' and is Date between 05/10/2004 and 07/10/2004?

If it answers 'Yes' to either question, then the record is included in
the results. If not, the record is excluded.
However, if you have the 'CA' criterea only on the first line, then
you would be asking for all records where the state is 'CA' and the
date is between the first range, OR records where the date is between
the second range (regardless of the State value). In this case, the
query would ask the following 2 questions about each record:

1) Does State='CA' and is Date between 24/09/2004 and 01/10/2004?
2) Is Date between 05/10/2004 and 07/10/2004?

If it answers 'Yes' to either question, then the record is included in
the results. If not, the record is excluded.

See the difference?
-Dennis Borg

[Steve Elliott]

You're right, that does sound logical - I'm having a bad "Access" week :(

Works fine for several dates, ie: 04/10/2004 or 10/10/2004 or 12/10/1004 in
same query - in fact it works for a very long string of various dates.

Thanks for the advice/suggestions/explanation.

Steve.
"DenBorg" <de******@yahoo.com> wrote in message
news:dc**************************@posting.google.c om...

"Steve Elliott" wrote:

Logically I would have thought the AND command would do that, but
apparently
not.

Think about it this way: By using 'AND', you are saying that the date
must be between both ranges. Any date between 24/9/2004 and 01/10/2004
cannot possible also be between 05/10/2004 and 07/10/2004.

By using 'OR', you are saying, "Give me all records where the date is
either between this range OR that range."

Another analogy would be, "Show me all pets that are both a Cat AND a
Dog" versus "Show me all pets that are either a Cat OR a Dog". If you
asked for the first, it is not likely the pet store would have any
animals that is both a Cat AND a Dog. But if you asked for the second,
you would be shown all cats and dogs in the store.

Make sense?

Success ! If I show dates on SAME line with an OR between each
individual
date, Access sums the totals from each of these dates and produces a
result.

You would get the same results if you put each Between clause on
separate rows, as in:

Field: Date
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: Between #24/09/2004# And #01/10/2004#
Or: Between #05/10/2004# And #07/10/2004#

However, if you have criterea in addition to this date range, you
would have to copy that criterea to the second line also.

For example, if your date field must fall between either of those two
ranges and the State must also be 'CA', the column for the state field
would look like:

Field: State
Table: Transactions
Total: Where
Crosstab:
Sort:
Criteria: 'CA'
Or: 'CA'

Having the 'CA' listed on both lines makes the 'CA' criterea
applicable to both date ranges. In this case, the query would ask the
following 2 questions about each record (1 question per line of
criterea):

1) Does State='CA' and is Date between 24/09/2004 and 01/10/2004?
2) Does State='CA' and is Date between 05/10/2004 and 07/10/2004?

If it answers 'Yes' to either question, then the record is included in
the results. If not, the record is excluded.
However, if you have the 'CA' criterea only on the first line, then
you would be asking for all records where the state is 'CA' and the
date is between the first range, OR records where the date is between
the second range (regardless of the State value). In this case, the
query would ask the following 2 questions about each record:

1) Does State='CA' and is Date between 24/09/2004 and 01/10/2004?
2) Is Date between 05/10/2004 and 07/10/2004?

If it answers 'Yes' to either question, then the record is included in
the results. If not, the record is excluded.

See the difference?
-Dennis Borg