How to remove the prompt criteria from the report ?

  • Thread starter Thread starter Guest
  • Start date Start date
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,
 
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");
 
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...
 
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...
 
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...
 
Back
Top