Crosstab query by date range

[joseph]

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[Jerry Whittle]

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.

 

[joseph]

Jerry,

Thank you for the reply. (I'm using 2007 btw)

so I added the parameter and now my SQL looks as follows:

PARAMETERS [Enter Beginning Date Range] DateTime, [Enter Ending Date Range]
DateTime;
TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];


Problem I encounter is ACCESS asks me to input the Beginning Date and Ending
Date 3 times and then it returns all possible values

I must have done something wrong, right?
tya

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[joseph]

I found another post and have converted my SQL to look as follows:

PARAMETERS [Date Received by DOS]![Enter Beginning Date Range] DateTime,
[Date Received by DOS]![Enter Ending Date Range] DateTime;
TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
WHERE (((tblDefendants.[Date Received by DOS])>=[Date Received by
DOS]![Enter Beginning Date Range] And (tblDefendants.[Date Received by
DOS])<=[Date Received by DOS]![Enter Ending Date Range]))
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

This works!!!!!!!!!!!!!!!! with a couple issues, I have to enter in the
Beginning and Ending Dates THREE TIMES ... whenever I close the query from
design view I have to enter the dates and when I enter the parameters the
user will get the following in the popup box .... Date Received by DOS!Enter
Beginning Date Range

I hate to mess with it is I actually got the results I wanted, with the
exception of how the parameter box is displayed and the issue with having to
type the date range 3x ...

I'm so close!

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[joseph]

follow up - i got rid of the message but still have to enter the beginning
and end dates 3 times ... other than that it works ...

getting closer still


thank you

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[Jerry Whittle]

Try building a select query with the parameters in it first. Then base the
crosstab on this initial query.

Or build a form with two text boxes and a button to run the crosstab query.
Put the dates in the text boxes and reference them in the criteria of the
query something like:

[Forms]![TheFormName]![TheTextBoxName]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

follow up - i got rid of the message but still have to enter the beginning
and end dates 3 times ... other than that it works ...

getting closer still


thank you

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[joseph]

Jerry,

Thank you first off for your assistance

I created a select query to get the date range and then I created the
crosstab query that pulls data from the select query. I get the same error
message:

"The Microsoft Office Access engine does not recorgnize '[Enter Beginning
Date Range (mm/dd/yyyy)]' as a valid field name or expression

Try building a select query with the parameters in it first. Then base the
crosstab on this initial query.

Or build a form with two text boxes and a button to run the crosstab query.
Put the dates in the text boxes and reference them in the criteria of the
query something like:

[Forms]![TheFormName]![TheTextBoxName]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

follow up - i got rid of the message but still have to enter the beginning
and end dates 3 times ... other than that it works ...

getting closer still


thank you

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jerry,

Thank you first off for your assistance

I created a select query to get the date range and then I created the
crosstab query that pulls data from the select query. I get the same error
message:

"The Microsoft Office Access engine does not recorgnize '[Enter Beginning
Date Range (mm/dd/yyyy)]' as a valid field name or expression

Try building a select query with the parameters in it first. Then base the
crosstab on this initial query.

Or build a form with two text boxes and a button to run the crosstab query.
Put the dates in the text boxes and reference them in the criteria of the
query something like:

[Forms]![TheFormName]![TheTextBoxName]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

follow up - i got rid of the message but still have to enter the beginning
and end dates 3 times ... other than that it works ...

getting closer still


thank you

:

Later versions of Access require that the parameters be defined when used in
a crosstab query. While in design view go up to Query, Parameters and type in
[Enter Beginning Date Range] exactly as it's in the criteria including the
[]. Make the data type Date/time.

Actually it's better to make a plain old select query first with the columns
and criteria that you want (including parameters). Then create a crosstab
query off of this select query.

In either case you need to define the data type of the parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Good afternoon everyone,

I was hoping someone could provide me some assistance. I have the following
crosstab query:

TRANSFORM Count(tblDefendants.[Defendant ID Number]) AS
CountOfDependantIDNumber
SELECT tblDefendants.[Date Received by DOS], Count(tblDefendants.[Defendant
ID Number]) AS [Total Of Defendant ID Number]
FROM tblDefendants
GROUP BY tblDefendants.[Date Received by DOS]
ORDER BY tblDefendants.[Date Received by DOS] DESC
PIVOT tblDefendants.[Defendant ID Number];

Which provides me with a total number of waivers received by date. Which is
great, however, once this database is completely constructed there will be
10k records over 10+ years which would involve alot of scrolling to narrow
down specific dates to be reviewed. I would like to be able to prompt the
user for a date range that will produce results in the exact format as listed
above. If i attempt to use the Where statement in the query I get the message
that reads "The Microsoft Office Access database engine does not recognize
'[Enter Beginning Date Range]' as a valid field name or expression ...

I tried to run a crosstab query that was based on a date range query and got
the same message when I tried to run it ...

I'm looking for results that appear as follows: User clicks action button to
search waivers received by date range, enters date range of 1/15/1999 through
1/16/1999

Date Received Waivers Received
1/15/1999 15
1/16/1999 12

Total Received
for Date Range Selected 27

The best i've come up w/ so far is getting a report in that format for my
entire database, can't get it to allow me to only report a user-defined date
range.

Any help would be most appreciated
I love this place ... I found some similar scenarios in previous posts but
the extent of those has got me this far

thank you again