PoP up boxes

  • Thread starter Thread starter Vern
  • Start date Start date
V

Vern

I have a form named print_screen, in this form I have a
set of controls named start_date and another named
end_date. These two control boxes are referenced in a
cross tab query. When I want to print a report that uses
this query, and I insert the start date and end date and
click on the print button, another window pops up and ask
for the start date and then the end date again. Any way to
stop this from happening and get Access to use the first
two inputs on the print_screen form??? Thanks for your
support. Vern
 
Duane, Sorry, no attachments allowed in this formate. The
pop up message is "ENTER PARAMETER VALUE" and then below -
it states, "start date" for the beginning of the report
and "end date" for the ending. These dates are then used
to run the report. Vern
 
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
 
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date]) as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report expressions.


--
Duane Hookom
MS Access MVP


Vern said:
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date]) as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report expressions.


--
Duane Hookom
MS Access MVP


Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


Duane, Thanks for your reply questions.... The PRINT
MENU form stays open. Attached is a picture of what the
screen looks like and the pop up window that Access sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the dates in
the Print Menu to work and not have these pop up windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in the
window pop-ups?

--
Duane Hookom
MS Access MVP


message
I have a form named print_screen, in this form I have a
set of controls named start_date and another named
end_date. These two control boxes are referenced in a
cross tab query. When I want to print a report
Click to expand...
that
uses
this query, and I insert the start date and end
Click to expand...
date
and
click on the print button, another window pops up and
ask
for the start date and then the end date again.
Click to expand...
Any
way
to
stop this from happening and get Access to use the first
two inputs on the print_screen form??? Thanks
Click to expand...
for
your
support. Vern


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date]) as StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


Vern said:
Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date]) as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report expressions.


--
Duane Hookom
MS Access MVP


Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


message
Duane, Thanks for your reply questions.... The PRINT
MENU form stays open. Attached is a picture of what the
screen looks like and the pop up window that Access
sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the dates in
the Print Menu to work and not have these pop up windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


message
I have a form named print_screen, in this form I
have a
set of controls named start_date and another named
end_date. These two control boxes are referenced in a
cross tab query. When I want to print a report that
uses
this query, and I insert the start date and end date
and
click on the print button, another window pops up and
ask
for the start date and then the end date again. Any
way
to
stop this from happening and get Access to use the
first
two inputs on the print_screen form??? Thanks for
your
support. Vern


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date]) as StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End
Click to expand...
Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report expressions.


--
Duane Hookom
MS Access MVP


Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


message
Duane, Thanks for your reply questions.... The PRINT
MENU form stays open. Attached is a picture of
Click to expand...
what
the
screen looks like and the pop up window that Access
sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the
Click to expand...
dates
in
the Print Menu to work and not have these pop up windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


"Vern" <[email protected]>
Click to expand...
wrote
in
message
I have a form named print_screen, in this form I
have a
set of controls named start_date and another named
end_date. These two control boxes are
Click to expand...
referenced
in a
cross tab query. When I want to print a report that
uses
this query, and I insert the start date and end date
and
click on the print button, another window pops
Click to expand...
up
and
ask
for the start date and then the end date again. Any
way
to
stop this from happening and get Access to use the
first
two inputs on the print_screen form??? Thanks for
your
support. Vern


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Vern said:
Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date]) as StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of what
the
screen looks like and the pop up window that Access
sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the dates
in
the Print Menu to work and not have these pop up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


in
message
I have a form named print_screen, in this form I
have a
set of controls named start_date and another named
end_date. These two control boxes are referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date and end
date
and
click on the print button, another window pops up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access to use the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
To test this syntax, I created a query in the Northwind.mdb with a sql of
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT [Start Date] AS StartDate, [End Date] AS EndDate, [Order
Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]))
GROUP BY [Start Date], [End Date], [Order Details].ProductID
PIVOT Orders.CustomerID;


--
Duane Hookom
MS Access MVP


Vern said:
Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date]) as StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of what
the
screen looks like and the pop up window that Access
sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the dates
in
the Print Menu to work and not have these pop up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


in
message
I have a form named print_screen, in this form I
have a
set of controls named start_date and another named
end_date. These two control boxes are referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date and end
date
and
click on the print button, another window pops up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access to use the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Vern:
This method works for me: In the top section of the code
for the print_screen form add two variables
Public StartPrintDate as Date, EndPrintDate as Date

In the Click event, before calling the query or report add
StartPrintDate = Start_date
EndPrintDate = End_date

Now go to the source query and instead of using start_date
and end-date as the dates, do this for the criteria:
Between [Forms]![print_screen].[StartPrintDate] and
[Forms]![print_screen].[EndPrintDate]
 
Kevin, thanks for your reply and I am anxious to try it
out. Just one problem, I am not an "ACCESSHEAD" so to
speak, so I need some help in understanding what you mean
by the following, "In the top section of the code
for the print_screen form " do you mean the propery box
for this form or something else? I think you mean
something esle but I don't know what. In fact I need the
whole thing explained a little more, I have inherited this
database and did help some in developement but the down in
the weeds areas I am somewhat behind on. Any input and
comments etc are most welcome. thanks again Vern
-----Original Message-----
Vern:
This method works for me: In the top section of the code
for the print_screen form add two variables
Public StartPrintDate as Date, EndPrintDate as Date

In the Click event, before calling the query or report add
StartPrintDate = Start_date
EndPrintDate = End_date

Now go to the source query and instead of using start_date
and end-date as the dates, do this for the criteria:
Between [Forms]![print_screen].[StartPrintDate] and
[Forms]![print_screen].[EndPrintDate]

wrote
in referenced
in a up
.
Click to expand...
.
Click to expand...
 
This new SQL you sent still did not stop the pop up boxes
asking for the start date and end date. I'm not sure what
you mean by "If this doesn't work then paste your sql into
a reply." My SQL is as below except what you added,in the
SELECT line , [Start Date]) as StartDate, [End Date] and
then in the GROUP BY line , [Start Date],[End Date]
Vern


-----Original Message-----
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date])
Click to expand...
as
StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to save I
got the following error message, Syntax error in Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions?? Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


"Vern" <[email protected]>
Click to expand...
wrote
in
message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of what
the
screen looks like and the pop up window that Access
sends
when I put in dates and then click on the print icon.
This report is a cross tab query. I wanted the dates
in
the Print Menu to work and not have these pop up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


in
message
I have a form named print_screen, in this
Click to expand...
form
I
have a
set of controls named start_date and another named
end_date. These two control boxes are referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date and end
date
and
click on the print button, another window
Click to expand...
pops
up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access to
Click to expand...
use
the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
What I meant by pasting your sql into a reply is: Open your query in SQL
view. Copy the entire text and paste it into an email/reply to the news
group.

--
Duane Hookom
Microsoft Access MVP


Daune said:
This new SQL you sent still did not stop the pop up boxes
asking for the start date and end date. I'm not sure what
you mean by "If this doesn't work then paste your sql into
a reply." My SQL is as below except what you added,in the
SELECT line , [Start Date]) as StartDate, [End Date] and
then in the GROUP BY line , [Start Date],[End Date]
Vern


-----Original Message-----
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start Date]) as
StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


message
Daune, Thanks for your input and help. I did a copy and
paste of what you sent about changing the SQL. When I
went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried to type
the change directly into the SQL and when I went to
save I
got the following error message, Syntax error in
Transform
statement. This is what I typed into the SQL and at the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this what you
meant by changing the SQL?? Anymore suggestions??
Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End
Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date]
DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


in
message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of
what
the
screen looks like and the pop up window that
Access
sends
when I put in dates and then click on the print
icon.
This report is a cross tab query. I wanted the
dates
in
the Print Menu to work and not have these pop up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact
prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


"Vern" <[email protected]>
wrote
in
message
I have a form named print_screen, in this form
I
have a
set of controls named start_date and another
named
end_date. These two control boxes are
referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date and end
date
and
click on the print button, another window pops
up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access to use
the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane, What do you think of this solution? I would try it
but don't have an understanding of what is meant by,"top
section of the code". Sorry to be such a slow learner but
I inherited this database and it's problems for a fellow
employee who was a Temp and has now moved on to a
permanent job. He and I worked on developing the database
but he was more into it than I was at that time.... Vern

In the top section of the code
for the print_screen form add two variables
Public StartPrintDate as Date, EndPrintDate as Date

In the Click event, before calling the query or report add
StartPrintDate = Start_date
EndPrintDate = End_date

Now go to the source query and instead of using start_date
and end-date as the dates, do this for the criteria:
Between [Forms]![print_screen].[StartPrintDate] and
[Forms]![print_screen].[EndPrintDate]
-----Original Message-----
What I meant by pasting your sql into a reply is: Open your query in SQL
view. Copy the entire text and paste it into an email/reply to the news
group.

--
Duane Hookom
Microsoft Access MVP


This new SQL you sent still did not stop the pop up boxes
asking for the start date and end date. I'm not sure what
you mean by "If this doesn't work then paste your sql into
a reply." My SQL is as below except what you added,in the
SELECT line , [Start Date]) as StartDate, [End Date] and
then in the GROUP BY line , [Start Date],[End Date]
Vern


-----Original Message-----
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start
Click to expand...
Date])
as
StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


message
Daune, Thanks for your input and help. I did a
Click to expand...
copy
and
paste of what you sent about changing the SQL. When I
went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried
Click to expand...
to
type
the change directly into the SQL and when I went to
save I
got the following error message, Syntax error in
Transform
statement. This is what I typed into the SQL and
Click to expand...
at
the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this
Click to expand...
what
you
meant by changing the SQL?? Anymore suggestions??
Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End
Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


"Vern" <[email protected]>
Click to expand...
wrote
in
message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date]
DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


in
message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of
what
the
screen looks like and the pop up window that
Access
sends
when I put in dates and then click on the print
icon.
This report is a cross tab query. I wanted the
dates
in
the Print Menu to work and not have these
Click to expand...
pop
up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact
prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


"Vern"
Click to expand...
wrote
in
message
[email protected]...
I have a form named print_screen, in this form
I
have a
set of controls named start_date and another
named
end_date. These two control boxes are
referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date
Click to expand...
and
end
date
and
click on the print button, another window pops
up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access
Click to expand...
to
use
the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Daune, here is the SQL that runs when I click the print
icon on my print screen form.... Vern


PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
What I meant by pasting your sql into a reply is: Open your query in SQL
view. Copy the entire text and paste it into an email/reply to the news
group.

--
Duane Hookom
Microsoft Access MVP


This new SQL you sent still did not stop the pop up boxes
asking for the start date and end date. I'm not sure what
you mean by "If this doesn't work then paste your sql into
a reply." My SQL is as below except what you added,in the
SELECT line , [Start Date]) as StartDate, [End Date] and
then in the GROUP BY line , [Start Date],[End Date]
Vern


-----Original Message-----
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start
Click to expand...
Date])
as
StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


message
Daune, Thanks for your input and help. I did a
Click to expand...
copy
and
paste of what you sent about changing the SQL. When I
went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried
Click to expand...
to
type
the change directly into the SQL and when I went to
save I
got the following error message, Syntax error in
Transform
statement. This is what I typed into the SQL and
Click to expand...
at
the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this
Click to expand...
what
you
meant by changing the SQL?? Anymore suggestions??
Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End
Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


"Vern" <[email protected]>
Click to expand...
wrote
in
message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date]
DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


in
message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of
what
the
screen looks like and the pop up window that
Access
sends
when I put in dates and then click on the print
icon.
This report is a cross tab query. I wanted the
dates
in
the Print Menu to work and not have these
Click to expand...
pop
up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact
prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


"Vern"
Click to expand...
wrote
in
message
[email protected]...
I have a form named print_screen, in this form
I
have a
set of controls named start_date and another
named
end_date. These two control boxes are
referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date
Click to expand...
and
end
date
and
click on the print button, another window pops
up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access
Click to expand...
to
use
the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Daune, I posted a new message under SQL and pop up boxes
in which i pasted the SQL from my database.... Vern
-----Original Message-----
What I meant by pasting your sql into a reply is: Open your query in SQL
view. Copy the entire text and paste it into an email/reply to the news
group.

--
Duane Hookom
Microsoft Access MVP


This new SQL you sent still did not stop the pop up boxes
asking for the start date and end date. I'm not sure what
you mean by "If this doesn't work then paste your sql into
a reply." My SQL is as below except what you added,in the
SELECT line , [Start Date]) as StartDate, [End Date] and
then in the GROUP BY line , [Start Date],[End Date]
Vern


-----Original Message-----
Try
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS [SumOfHoursWorked]
SELECT [Main Header].[Employee ID], [Start Date]) as StartDate, [End Date]
as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID], [Start Date],[End Date]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

If this doesn't work then paste your sql into a reply.

--
Duane Hookom
MS Access MVP


Daune, I got the following error message on the SQL. "The
Microsoft Jet database engine does not recognize '[End
Date]' as a valid field name of expression. It copied
and pasted and saved OK Thanks for your patients and
persistance in helping solve this problem.... Vern
-----Original Message-----
My bad, should have been
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHoursWorked]
SELECT [Main Header].[Employee ID], Max([Start
Click to expand...
Date])
as
StartDate, Max([End
Date]) as EndDate
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;


--
Duane Hookom
Microsoft Access MVP


message
Daune, Thanks for your input and help. I did a
Click to expand...
copy
and
paste of what you sent about changing the SQL. When I
went
to save it I got the following error message, Invalid
bracketing of namne'[SumOfHoursWorked]'. I tried
Click to expand...
to
type
the change directly into the SQL and when I went to
save I
got the following error message, Syntax error in
Transform
statement. This is what I typed into the SQL and
Click to expand...
at
the
location that you indicated, , Max([Start Date]) as
StartDate, Max([End Date]) as EndDate Is this
Click to expand...
what
you
meant by changing the SQL?? Anymore suggestions??
Thanks
again Vern
-----Original Message-----
Change your sql to:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked], Max([Start Date]) as StartDate, Max([End
Date])
as EndDate
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;

Then you can use StartDate and EndDate in your report
expressions.


--
Duane Hookom
MS Access MVP


"Vern" <[email protected]>
Click to expand...
wrote
in
message
Daune, Here it is.... Vern

PARAMETERS [start date] DateTime, [end date]
DateTime;
TRANSFORM Sum([Main Header].[Hours Worked]) AS
[SumOfHours
Worked]
SELECT [Main Header].[Employee ID]
FROM [Main Header]
WHERE ((([Main Header].[Date/Time]) Between [Start
date]
And [End date]))
GROUP BY [Main Header].[Employee ID]
PIVOT [Main Header].Activity_Type
WITH OWNERACCESS OPTION;
-----Original Message-----
Please share your SQL of the query.

--
Duane Hookom
Microsoft Access MVP


in
message
Duane, Thanks for your reply questions.... The
PRINT
MENU form stays open. Attached is a picture of
what
the
screen looks like and the pop up window that
Access
sends
when I put in dates and then click on the print
icon.
This report is a cross tab query. I wanted the
dates
in
the Print Menu to work and not have these
Click to expand...
pop
up
windows
to occur...


Vern


-----Original Message-----
Does the form stay open? What is the exact
prompt in
the
window pop-ups?

--
Duane Hookom
MS Access MVP


"Vern"
Click to expand...
wrote
in
message
[email protected]...
I have a form named print_screen, in this form
I
have a
set of controls named start_date and another
named
end_date. These two control boxes are
referenced
in a
cross tab query. When I want to print a report
that
uses
this query, and I insert the start date
Click to expand...
and
end
date
and
click on the print button, another window pops
up
and
ask
for the start date and then the end date again.
Any
way
to
stop this from happening and get Access
Click to expand...
to
use
the
first
two inputs on the print_screen form??? Thanks
for
your
support. Vern


.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top