How to remove the prompt criteria from the report ?

G

Guest

Hi All,
I am using VB 6.0 and Access 2003.
I have a report - data source ia crossTab query with parameters.
crossTab:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks
WHERE TempTicks.IssueDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

VB 6.0 code
I hard coded the date critiria,
How to use dates from DateTimePicker in WhereCondition clause?
dtFrom.Value and dtTo.Value
..DoCmd.OpenReport "SumAll", acViewNormal, , "[Enter Start Date] = #10/1/07#
AND [Enter End Date] = #11/15/07#"

Is it possible to print report from VB 6.0 (data source for report
parameterized crossTab query)?
Please advice,
 
G

Guest

Have you considered creating a one record table:
tblDates
=============
StartDate date/time
EndDate date/time

Update the values based on user input to your starting and ending dates.
Then change SQL to:

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
 
G

Guest

Thank you, Duane
It did the work, now report prints w/o prompt of parameters.
But how can i show the range of dates [StartDate] and [EndDate] on the
header of the report?
Please, advice

Duane Hookom said:
Have you considered creating a one record table:
tblDates
=============
StartDate date/time
EndDate date/time

Update the values based on user input to your starting and ending dates.
Then change SQL to:

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP


elena said:
Hi All,
I am using VB 6.0 and Access 2003.
I have a report - data source ia crossTab query with parameters.
crossTab:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks
WHERE TempTicks.IssueDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

VB 6.0 code
I hard coded the date critiria,
How to use dates from DateTimePicker in WhereCondition clause?
dtFrom.Value and dtTo.Value
.DoCmd.OpenReport "SumAll", acViewNormal, , "[Enter Start Date] = #10/1/07#
AND [Enter End Date] = #11/15/07#"

Is it possible to print report from VB 6.0 (data source for report
parameterized crossTab query)?
Please advice,
Click to expand...
Click to expand...
 
G

Guest

Add the fields to your Row Headings.

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP


elena said:
Thank you, Duane
It did the work, now report prints w/o prompt of parameters.
But how can i show the range of dates [StartDate] and [EndDate] on the
header of the report?
Please, advice

Duane Hookom said:
Have you considered creating a one record table:
tblDates
=============
StartDate date/time
EndDate date/time

Update the values based on user input to your starting and ending dates.
Then change SQL to:

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP


elena said:
Hi All,
I am using VB 6.0 and Access 2003.
I have a report - data source ia crossTab query with parameters.
crossTab:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks
WHERE TempTicks.IssueDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

VB 6.0 code
I hard coded the date critiria,
How to use dates from DateTimePicker in WhereCondition clause?
dtFrom.Value and dtTo.Value
.DoCmd.OpenReport "SumAll", acViewNormal, , "[Enter Start Date] = #10/1/07#
AND [Enter End Date] = #11/15/07#"

Is it possible to print report from VB 6.0 (data source for report
parameterized crossTab query)?
Please advice,
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thnak you, Duane
It works w/o prompting for parameters.

Duane Hookom said:
Add the fields to your Row Headings.

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP


elena said:
Thank you, Duane
It did the work, now report prints w/o prompt of parameters.
But how can i show the range of dates [StartDate] and [EndDate] on the
header of the report?
Please, advice

Duane Hookom said:
Have you considered creating a one record table:
tblDates
=============
StartDate date/time
EndDate date/time

Update the values based on user input to your starting and ending dates.
Then change SQL to:

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP


:

Hi All,
I am using VB 6.0 and Access 2003.
I have a report - data source ia crossTab query with parameters.
crossTab:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(TempTicks.Amount) AS SumOfAmount,
Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks
WHERE TempTicks.IssueDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

VB 6.0 code
I hard coded the date critiria,
How to use dates from DateTimePicker in WhereCondition clause?
dtFrom.Value and dtTo.Value
.DoCmd.OpenReport "SumAll", acViewNormal, , "[Enter Start Date] = #10/1/07#
AND [Enter End Date] = #11/15/07#"

Is it possible to print report from VB 6.0 (data source for report
parameterized crossTab query)?
Please advice,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab with multiple fields 2
Crosstab query for the report 2
crosstab with conditional SUM() 1
Crosstab query with multiple fields 2
Report with parameters 3

Top