Crosstab Query for Beginning Date and Ending Date

[nms]

I have several crosstab queries were the end user will be selecting a report
by month. I have declared the parameters of [Beginning Date] and [Ending
Date] in the query and it works great but upon opening the report, it asks
for the dates twice. I have read the threads on creating a form for this.
In my case, the date is coming from a SQL Server database for Remedy HelpDesk
that I link to. Also, the field that I want to use [Arrival_Time] is in the
Unix timestamp format and I have successfully converted this timestamp to a
date field. Thus, the form would not be used to input dates. Is there
another way to make it so the report does not ask for the date parameters
twice? The end user is not opposed to manually changing the date in the
query but I would like to automate this for her since there are about 10
crosstab queries to be run every month and I would like to know how to do it
as well. Any help is greatly appreciated.

 

[John Spencer]

One - it would help if you posted the SQL. (Hint: Menu: View: SQL)

Two: I see no reason that you can't use a form to input the dates and then
reference the dates on the open form to run the report.
In the query you would replace
[Beginning Date] with
[Forms]![Name of Your Form]![Name of Beginning Date Control]
And
[Ending Date] with
[Forms]![Name of Your Form]![Name of Ending Date Control]

Then press a button on the form to open the report.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gary Walter]

PMFJI

or Three:

report is based on stored query ("qryRpt")

form ("frmCrit") with 2 textboxes
txtBeginDate
txtEndDate

and command button ("cmdPreviewReport") to open report
with click event code something like...

{aircode--requires DAO reference}

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
Dim lngUnixBegin As Long
Dim lngUnixEnd As Long
Dim strSQL As String

If NOT IsDate(Me!txtBeginDate)
Or NOT IsDate(Me!txtEndDate) Then
MsgBox "You must enter valid beginning and ending dates."
DoCmd.GoToControl "txtBeginDate"
Else
If Me!txtBeginDate > Me!txtEndDate Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtBeginDate"
Else
lngUnixBegin = DateDiff("s",#1/1/1970#, CDate(Me!txtBeginDate))
lngUnixEnd = DateDiff("s",#1/1/1970#, CDate(Me!txtEndDate))

strSQL = "TRANSFORM .... " _
& " SELECT .... FROM ..." _
& " WHERE [UnixTimeStampField] >= " & lngUnixBegin _
& " AND [UnixTimeStampField] < " & lngUnixEnd & " + 1" _
& " GROUP BY ...." _
& " PIVOT ....;"
CurrentDb.QueryDefs("qryRpt").SQL = strSQL

DoCmd.OpenReport "rptXXXX", acViewPreview

End If
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume Exit_cmdPreviewReport_Click
End Sub

Aplogies again for jumping in (especially if misunderstood).

good luck,

gary

One - it would help if you posted the SQL. (Hint: Menu: View: SQL)

Two: I see no reason that you can't use a form to input the dates and
then reference the dates on the open form to run the report.
In the query you would replace
[Beginning Date] with
[Forms]![Name of Your Form]![Name of Beginning Date Control]
And
[Ending Date] with
[Forms]![Name of Your Form]![Name of Ending Date Control]

Then press a button on the form to open the report.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have several crosstab queries were the end user will be selecting a
report
by month. I have declared the parameters of [Beginning Date] and [Ending
Date] in the query and it works great but upon opening the report, it
asks
for the dates twice. I have read the threads on creating a form for
this.
In my case, the date is coming from a SQL Server database for Remedy
HelpDesk
that I link to. Also, the field that I want to use [Arrival_Time] is in
the
Unix timestamp format and I have successfully converted this timestamp to
a
date field. Thus, the form would not be used to input dates. Is there
another way to make it so the report does not ask for the date parameters
twice? The end user is not opposed to manually changing the date in the
query but I would like to automate this for her since there are about 10
crosstab queries to be run every month and I would like to know how to do
it
as well. Any help is greatly appreciated.

 

[nms]

Hi John-thanks for responding-I really need to get this to work as there are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to enter
and returns no data in the query when I try to run it. I must be doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a field
that I created to change the date from the Unix timestamp to a date that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form, called
one Beginning Date and the other Ending Date (I take that I don't have to
enter anything in the control source). When I try to open the report from
the Main Menu with a command button that I created, I get the following error
messge:

The Microsoft Jet database engine does not recognize " as a valid field name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime, [Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between [Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

One - it would help if you posted the SQL. (Hint: Menu: View: SQL)

Two: I see no reason that you can't use a form to input the dates and then
reference the dates on the open form to run the report.
In the query you would replace
[Beginning Date] with
[Forms]![Name of Your Form]![Name of Beginning Date Control]
And
[Ending Date] with
[Forms]![Name of Your Form]![Name of Ending Date Control]

Then press a button on the form to open the report.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have several crosstab queries were the end user will be selecting a
report
by month. I have declared the parameters of [Beginning Date] and [Ending
Date] in the query and it works great but upon opening the report, it asks
for the dates twice. I have read the threads on creating a form for this.
In my case, the date is coming from a SQL Server database for Remedy
HelpDesk
that I link to. Also, the field that I want to use [Arrival_Time] is in
the
Unix timestamp format and I have successfully converted this timestamp to
a
date field. Thus, the form would not be used to input dates. Is there
another way to make it so the report does not ask for the date parameters
twice? The end user is not opposed to manually changing the date in the
query but I would like to automate this for her since there are about 10
crosstab queries to be run every month and I would like to know how to do
it
as well. Any help is greatly appreciated.

 

[John Spencer]

Your SQL statement looks good to me.

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime
, [Forms]![Main Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like "[0]*","Low"
,[priority] Like "[1]*","Medium"
,[Priority] Like "[2]*"
,"High",[Priority] Like "[3]*","Urgent") AS PriorityName
, Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE DateAdd("s",[arrival_time],#1/1/1970#)
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date]
GROUP BY dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

Things to check:
Names of the fields - make sure they are all correct . Normally you will
see the message when Access sees something it can't interpret as a field or
a parameter in a crosstab. So if a field is mistyped then the query
believes it is a parameter. Crosstabs require parameters to be defined.

Is [Arrival_Time] EVER null (Blank)? That can cause an error. Perhaps you
could try
WHERE DateAdd("s",IIF([arrival_time] is Null,0,[Arrival_Time]),#1/1/1970#)

Double check the SQL and make sure Access hasn't done something like adding
extra brackets around the parameters. Sometimes Access will do
PARAMETERS [[Forms]![Main Menu]![Beginning Date]] DateTime
, [[Forms]![Main Menu]![Ending Date]] DateTime;

Things to try to identify the source of the problem
Try Hard Coding the dates and see if the query works.
Try removing the fields one by one
Try removing the where clause. If that solves the problem that try hard
coding the dates. If you still have the problem then look at the
arrival_time field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-thanks for responding-I really need to get this to work as there
are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to
enter
and returns no data in the query when I try to run it. I must be doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a field
that I created to change the date from the Unix timestamp to a date that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form, called
one Beginning Date and the other Ending Date (I take that I don't have to
enter anything in the control source). When I try to open the report from
the Main Menu with a command button that I created, I get the following
error
messge:

The Microsoft Jet database engine does not recognize " as a valid field
name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime, [Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between [Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[nms]

Thanks John but I give up-I did everything you said and it still doesn't
work. It works great when you hard code the dates. I will just let the end
user type in the dates twice for now. Our deadline is coming up soon.
Thanks!!!

Your SQL statement looks good to me.

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime
, [Forms]![Main Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like "[0]*","Low"
,[priority] Like "[1]*","Medium"
,[Priority] Like "[2]*"
,"High",[Priority] Like "[3]*","Urgent") AS PriorityName
, Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE DateAdd("s",[arrival_time],#1/1/1970#)
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date]
GROUP BY dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

Things to check:
Names of the fields - make sure they are all correct . Normally you will
see the message when Access sees something it can't interpret as a field or
a parameter in a crosstab. So if a field is mistyped then the query
believes it is a parameter. Crosstabs require parameters to be defined.

Is [Arrival_Time] EVER null (Blank)? That can cause an error. Perhaps you
could try
WHERE DateAdd("s",IIF([arrival_time] is Null,0,[Arrival_Time]),#1/1/1970#)

Double check the SQL and make sure Access hasn't done something like adding
extra brackets around the parameters. Sometimes Access will do
PARAMETERS [[Forms]![Main Menu]![Beginning Date]] DateTime
, [[Forms]![Main Menu]![Ending Date]] DateTime;

Things to try to identify the source of the problem
Try Hard Coding the dates and see if the query works.
Try removing the fields one by one
Try removing the where clause. If that solves the problem that try hard
coding the dates. If you still have the problem then look at the
arrival_time field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-thanks for responding-I really need to get this to work as there
are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to
enter
and returns no data in the query when I try to run it. I must be doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a field
that I created to change the date from the Unix timestamp to a date that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form, called
one Beginning Date and the other Ending Date (I take that I don't have to
enter anything in the control source). When I try to open the report from
the Main Menu with a command button that I created, I get the following
error
messge:

The Microsoft Jet database engine does not recognize " as a valid field
name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime, [Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between [Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[nms]

Hi John-I tried this again by starting over and creating a new query,
changing the date field from Unix to Access using
DateAdd("s",IIf([arrival_time] Is Null,0,[Arrival_Time]),#1/1/1970#) like you
said for the null values (but it will never be null), created the Main Menu
form and created two unbound text boxes for Beginning Date and Ending Date,
created the parameters in the Query: [Forms]![Main Menu]![Beginning Date] and
[Forms]![Main Menu]![Ending Date], added this as well to the criteria:
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date], ran the query and got the correct data, and then tried to create the
report and it would not let me-none of the fields from the query show up in
the report wizard and thus I can't create the report. I tried all the other
things that you had mentioned about Access to no avail. I have done several
workarounds but that is not great but it works but I wanted to be able to
automate this as much as possible.

In the real database, when I have done all that was listed below and tried
to modify any of the crosstab reports, the parameter value box keeps popping
up over and over again asking for a value and will not go away even with a
value.

What am I missing?! Any more ideas?! This is driving me crazy!!!!

Your SQL statement looks good to me.

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime
, [Forms]![Main Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like "[0]*","Low"
,[priority] Like "[1]*","Medium"
,[Priority] Like "[2]*"
,"High",[Priority] Like "[3]*","Urgent") AS PriorityName
, Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE DateAdd("s",[arrival_time],#1/1/1970#)
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date]
GROUP BY dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

Things to check:
Names of the fields - make sure they are all correct . Normally you will
see the message when Access sees something it can't interpret as a field or
a parameter in a crosstab. So if a field is mistyped then the query
believes it is a parameter. Crosstabs require parameters to be defined.

Is [Arrival_Time] EVER null (Blank)? That can cause an error. Perhaps you
could try
WHERE DateAdd("s",IIF([arrival_time] is Null,0,[Arrival_Time]),#1/1/1970#)

Double check the SQL and make sure Access hasn't done something like adding
extra brackets around the parameters. Sometimes Access will do
PARAMETERS [[Forms]![Main Menu]![Beginning Date]] DateTime
, [[Forms]![Main Menu]![Ending Date]] DateTime;

Things to try to identify the source of the problem
Try Hard Coding the dates and see if the query works.
Try removing the fields one by one
Try removing the where clause. If that solves the problem that try hard
coding the dates. If you still have the problem then look at the
arrival_time field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-thanks for responding-I really need to get this to work as there
are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to
enter
and returns no data in the query when I try to run it. I must be doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a field
that I created to change the date from the Unix timestamp to a date that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form, called
one Beginning Date and the other Ending Date (I take that I don't have to
enter anything in the control source). When I try to open the report from
the Main Menu with a command button that I created, I get the following
error
messge:

The Microsoft Jet database engine does not recognize " as a valid field
name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime, [Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between [Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[nms]

Thanks Gary-I will keep that in mind-I am trying to avoid lots of coding
because the enduser barely know SQL and she will be doing everything once I
turn the database over to her. looks great though and will try it as a last
resort. Thanks again.

PMFJI

or Three:

report is based on stored query ("qryRpt")

form ("frmCrit") with 2 textboxes
txtBeginDate
txtEndDate

and command button ("cmdPreviewReport") to open report
with click event code something like...

{aircode--requires DAO reference}

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
Dim lngUnixBegin As Long
Dim lngUnixEnd As Long
Dim strSQL As String

If NOT IsDate(Me!txtBeginDate)
Or NOT IsDate(Me!txtEndDate) Then
MsgBox "You must enter valid beginning and ending dates."
DoCmd.GoToControl "txtBeginDate"
Else
If Me!txtBeginDate > Me!txtEndDate Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtBeginDate"
Else
lngUnixBegin = DateDiff("s",#1/1/1970#, CDate(Me!txtBeginDate))
lngUnixEnd = DateDiff("s",#1/1/1970#, CDate(Me!txtEndDate))

strSQL = "TRANSFORM .... " _
& " SELECT .... FROM ..." _
& " WHERE [UnixTimeStampField] >= " & lngUnixBegin _
& " AND [UnixTimeStampField] < " & lngUnixEnd & " + 1" _
& " GROUP BY ...." _
& " PIVOT ....;"
CurrentDb.QueryDefs("qryRpt").SQL = strSQL

DoCmd.OpenReport "rptXXXX", acViewPreview

End If
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume Exit_cmdPreviewReport_Click
End Sub

Aplogies again for jumping in (especially if misunderstood).

good luck,

gary

One - it would help if you posted the SQL. (Hint: Menu: View: SQL)

Two: I see no reason that you can't use a form to input the dates and
then reference the dates on the open form to run the report.
In the query you would replace
[Beginning Date] with
[Forms]![Name of Your Form]![Name of Beginning Date Control]
And
[Ending Date] with
[Forms]![Name of Your Form]![Name of Ending Date Control]

Then press a button on the form to open the report.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have several crosstab queries were the end user will be selecting a
report
by month. I have declared the parameters of [Beginning Date] and [Ending
Date] in the query and it works great but upon opening the report, it
asks
for the dates twice. I have read the threads on creating a form for
this.
In my case, the date is coming from a SQL Server database for Remedy
HelpDesk
that I link to. Also, the field that I want to use [Arrival_Time] is in
the
Unix timestamp format and I have successfully converted this timestamp to
a
date field. Thus, the form would not be used to input dates. Is there
another way to make it so the report does not ask for the date parameters
twice? The end user is not opposed to manually changing the date in the
query but I would like to automate this for her since there are about 10
crosstab queries to be run every month and I would like to know how to do
it
as well. Any help is greatly appreciated.

 

[nms]

Hi John-I was able to figure it out and fix it without having to use a form
(I had to declare as well the column headings in the query under View, Query,
Column headings):

here is the SQL (using CDate)

TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",IIf([arrival_time] Is
Null,0,[Arrival_Time]),#1/1/1970#)) Between CDate([Enter Beginning Date]) And
CDate([Enter Ending Date])))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status In (1,2,3,4,5);

Thanks for all your help-you guys are truly amazing and I have learned a
lot. Once I got one query working it was easy to get all the others working
as well. So not only do the reports work but I export the reports to Excel to
create different kinds of charts and the beginning date and ending date work
great and everything is working great!!!

Hi John-I tried this again by starting over and creating a new query,
changing the date field from Unix to Access using
DateAdd("s",IIf([arrival_time] Is Null,0,[Arrival_Time]),#1/1/1970#) like you
said for the null values (but it will never be null), created the Main Menu
form and created two unbound text boxes for Beginning Date and Ending Date,
created the parameters in the Query: [Forms]![Main Menu]![Beginning Date] and
[Forms]![Main Menu]![Ending Date], added this as well to the criteria:
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date], ran the query and got the correct data, and then tried to create the
report and it would not let me-none of the fields from the query show up in
the report wizard and thus I can't create the report. I tried all the other
things that you had mentioned about Access to no avail. I have done several
workarounds but that is not great but it works but I wanted to be able to
automate this as much as possible.

In the real database, when I have done all that was listed below and tried
to modify any of the crosstab reports, the parameter value box keeps popping
up over and over again asking for a value and will not go away even with a
value.

What am I missing?! Any more ideas?! This is driving me crazy!!!!

Your SQL statement looks good to me.

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime
, [Forms]![Main Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like "[0]*","Low"
,[priority] Like "[1]*","Medium"
,[Priority] Like "[2]*"
,"High",[Priority] Like "[3]*","Urgent") AS PriorityName
, Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE DateAdd("s",[arrival_time],#1/1/1970#)
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main Menu]![Ending
Date]
GROUP BY dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

Things to check:
Names of the fields - make sure they are all correct . Normally you will
see the message when Access sees something it can't interpret as a field or
a parameter in a crosstab. So if a field is mistyped then the query
believes it is a parameter. Crosstabs require parameters to be defined.

Is [Arrival_Time] EVER null (Blank)? That can cause an error. Perhaps you
could try
WHERE DateAdd("s",IIF([arrival_time] is Null,0,[Arrival_Time]),#1/1/1970#)

Double check the SQL and make sure Access hasn't done something like adding
extra brackets around the parameters. Sometimes Access will do
PARAMETERS [[Forms]![Main Menu]![Beginning Date]] DateTime
, [[Forms]![Main Menu]![Ending Date]] DateTime;

Things to try to identify the source of the problem
Try Hard Coding the dates and see if the query works.
Try removing the fields one by one
Try removing the where clause. If that solves the problem that try hard
coding the dates. If you still have the problem then look at the
arrival_time field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-thanks for responding-I really need to get this to work as there
are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to
enter
and returns no data in the query when I try to run it. I must be doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a field
that I created to change the date from the Unix timestamp to a date that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form, called
one Beginning Date and the other Ending Date (I take that I don't have to
enter anything in the control source). When I try to open the report from
the Main Menu with a command button that I created, I get the following
error
messge:

The Microsoft Jet database engine does not recognize " as a valid field
name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime, [Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between [Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[Gary Walter]

Ok...all I ask is that sometime in the far future,
please get out a print-out of this post
and reread it...

....in the far future, when you have 10 years of data
and you are converting *every single timestamp*
to see if the record is in your little x-months time interval...

good luck,

gary

Thanks Gary-I will keep that in mind-I am trying to avoid lots of coding
because the enduser barely know SQL and she will be doing everything once
I
turn the database over to her. looks great though and will try it as a
last
resort. Thanks again.

PMFJI

or Three:

report is based on stored query ("qryRpt")

form ("frmCrit") with 2 textboxes
txtBeginDate
txtEndDate

and command button ("cmdPreviewReport") to open report
with click event code something like...

{aircode--requires DAO reference}

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
Dim lngUnixBegin As Long
Dim lngUnixEnd As Long
Dim strSQL As String

If NOT IsDate(Me!txtBeginDate)
Or NOT IsDate(Me!txtEndDate) Then
MsgBox "You must enter valid beginning and ending dates."
DoCmd.GoToControl "txtBeginDate"
Else
If Me!txtBeginDate > Me!txtEndDate Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtBeginDate"
Else
lngUnixBegin = DateDiff("s",#1/1/1970#,
CDate(Me!txtBeginDate))
lngUnixEnd = DateDiff("s",#1/1/1970#, CDate(Me!txtEndDate))

strSQL = "TRANSFORM .... " _
& " SELECT .... FROM ..." _
& " WHERE [UnixTimeStampField] >= " & lngUnixBegin _
& " AND [UnixTimeStampField] < " & lngUnixEnd & " +
1" _
& " GROUP BY ...." _
& " PIVOT ....;"
CurrentDb.QueryDefs("qryRpt").SQL = strSQL

DoCmd.OpenReport "rptXXXX", acViewPreview

End If
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume Exit_cmdPreviewReport_Click
End Sub

Aplogies again for jumping in (especially if misunderstood).

good luck,

gary

One - it would help if you posted the SQL. (Hint: Menu: View: SQL)

Two: I see no reason that you can't use a form to input the dates and
then reference the dates on the open form to run the report.
In the query you would replace
[Beginning Date] with
[Forms]![Name of Your Form]![Name of Beginning Date Control]
And
[Ending Date] with
[Forms]![Name of Your Form]![Name of Ending Date Control]

Then press a button on the form to open the report.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have several crosstab queries were the end user will be selecting a
report
by month. I have declared the parameters of [Beginning Date] and
[Ending
Date] in the query and it works great but upon opening the report, it
asks
for the dates twice. I have read the threads on creating a form for
this.
In my case, the date is coming from a SQL Server database for Remedy
HelpDesk
that I link to. Also, the field that I want to use [Arrival_Time] is
in
the
Unix timestamp format and I have successfully converted this timestamp
to
a
date field. Thus, the form would not be used to input dates. Is
there
another way to make it so the report does not ask for the date
parameters
twice? The end user is not opposed to manually changing the date in
the
query but I would like to automate this for her since there are about
10
crosstab queries to be run every month and I would like to know how to
do
it
as well. Any help is greatly appreciated.

 

[John Spencer]

HOORAY! I am happy for you. Access is a good program, but the learning
curve can be steep and long. I am still learning things about Access after
many years of using it.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-I was able to figure it out and fix it without having to use a
form
(I had to declare as well the column headings in the query under View,
Query,
Column headings):

here is the SQL (using CDate)

TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",IIf([arrival_time] Is
Null,0,[Arrival_Time]),#1/1/1970#)) Between CDate([Enter Beginning Date])
And
CDate([Enter Ending Date])))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status In (1,2,3,4,5);

Thanks for all your help-you guys are truly amazing and I have learned a
lot. Once I got one query working it was easy to get all the others
working
as well. So not only do the reports work but I export the reports to Excel
to
create different kinds of charts and the beginning date and ending date
work
great and everything is working great!!!

Hi John-I tried this again by starting over and creating a new query,
changing the date field from Unix to Access using
DateAdd("s",IIf([arrival_time] Is Null,0,[Arrival_Time]),#1/1/1970#) like
you
said for the null values (but it will never be null), created the Main
Menu
form and created two unbound text boxes for Beginning Date and Ending
Date,
created the parameters in the Query: [Forms]![Main Menu]![Beginning Date]
and
[Forms]![Main Menu]![Ending Date], added this as well to the criteria:
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date], ran the query and got the correct data, and then tried to create
the
report and it would not let me-none of the fields from the query show up
in
the report wizard and thus I can't create the report. I tried all the
other
things that you had mentioned about Access to no avail. I have done
several
workarounds but that is not great but it works but I wanted to be able to
automate this as much as possible.

In the real database, when I have done all that was listed below and
tried
to modify any of the crosstab reports, the parameter value box keeps
popping
up over and over again asking for a value and will not go away even with
a
value.

What am I missing?! Any more ideas?! This is driving me crazy!!!!

Your SQL statement looks good to me.

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime
, [Forms]![Main Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like "[0]*","Low"
,[priority] Like "[1]*","Medium"
,[Priority] Like "[2]*"
,"High",[Priority] Like "[3]*","Urgent") AS PriorityName
, Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE DateAdd("s",[arrival_time],#1/1/1970#)
Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]
GROUP BY dbo_HPD_HelpDesk.Priority
, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

Things to check:
Names of the fields - make sure they are all correct . Normally you
will
see the message when Access sees something it can't interpret as a
field or
a parameter in a crosstab. So if a field is mistyped then the query
believes it is a parameter. Crosstabs require parameters to be
defined.

Is [Arrival_Time] EVER null (Blank)? That can cause an error. Perhaps
you
could try
WHERE DateAdd("s",IIF([arrival_time] is
Null,0,[Arrival_Time]),#1/1/1970#)

Double check the SQL and make sure Access hasn't done something like
adding
extra brackets around the parameters. Sometimes Access will do
PARAMETERS [[Forms]![Main Menu]![Beginning Date]] DateTime
, [[Forms]![Main Menu]![Ending Date]] DateTime;

Things to try to identify the source of the problem
Try Hard Coding the dates and see if the query works.
Try removing the fields one by one
Try removing the where clause. If that solves the problem that try
hard
coding the dates. If you still have the problem then look at the
arrival_time field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John-thanks for responding-I really need to get this to work as
there
are
lots of crosstab reports to complete each month!

I tried what you suggested and it now does not even ask for a date to
enter
and returns no data in the query when I try to run it. I must be
doing
something wrong.

1. I declared the parameters in the query under Query, Parameters:

[Forms]![Main Menu]![Beginning Date]
[Forms]![Main Menu]![Ending Date]

2. in the criteria for the field in the query I entered (this is a
field
that I created to change the date from the Unix timestamp to a date
that
Access will recognize (DateAdd("s",[arrival_time],#1/1/1970#):

Between [Forms]![Main Menu]![Beginning Date] And [Forms]![Main
Menu]![Ending
Date]

3. I created two unbound text box controls on the main menu form,
called
one Beginning Date and the other Ending Date (I take that I don't
have to
enter anything in the control source). When I try to open the report
from
the Main Menu with a command button that I created, I get the
following
error
messge:

The Microsoft Jet database engine does not recognize " as a valid
field
name
or expression

Here is the SQL Code:

PARAMETERS [Forms]![Main Menu]![Beginning Date] DateTime,
[Forms]![Main
Menu]![Ending Date] DateTime;
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between
[Forms]![Main
Menu]![Beginning Date] And [Forms]![Main Menu]![Ending Date]))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[Gary Walter]

Maybe you've moved on...

but looking back I realize I probably
did not make my point as well as I
would have liked if someone was helping me...

sorry...

look at your WHERE clause:

FROM dbo_HPD_HelpDesk
WHERE
(
(
(DateAdd("s",
IIf([arrival_time] Is Null,0,[Arrival_Time]),#1/1/1970#))
Between
CDate([Enter Beginning Date])
And
CDate([Enter Ending Date])
)
)


If you have a lot of data in table dbo_HPD_HelpDesk,
the above WHERE clause will have to look at every
record, plus do a conversion on every [arrival_time].

If you had an index on dbo_HPD_HelpDesk.Arrival_Time,
it cannot take advantange of that index.

Whereas, your query should be able to use that index if...

FROM dbo_HPD_HelpDesk
WHERE
[arrival_time]
BETWEEN
DateDiff("s",#1/1/1970#, CDate([Enter Beginning Date]))
And
DateDiff("s",#1/1/1970#, CDate([Enter Ending Date]))

the conversion of your interval dates to Unix timestamp is done
only once at start of query, and from then it uses the index on
[arrival_time] to "slice-and-dice."

Maybe your table will never grow large, but it is something
one should be aware of (I think).

apologies again if was not clear (or too snarky) before,

gary