CrossTab query w/param

[elena]

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

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]

Does your IssueDate field contain a time element also? Do your results vary
if you use:
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 DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

 

[elena]

Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Does your IssueDate field contain a time element also? Do your results vary
if you use:
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 DateValue(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
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

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]

Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Does your IssueDate field contain a time element also? Do your results vary
if you use:
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 DateValue(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
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

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");

 

[elena]

Duane, thank you for reply,
I found the problem, it was tblDates
I populated this table from VB 6.0 from datetime picker control
dtPicker.Value property return date and time, in the table itself datatype of
the fields are short date.
Thank you, again now it return the same amount of tickets with different
param.

Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Does your IssueDate field contain a time element also? Do your results vary
if you use:
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 DateValue(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
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

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]

elena,
It's good to hear you got this sorted out. It seems you are still confusing
the "format" of a field with the actual value/precision of a field.
Formatting is generally for display only. Lots of Access developers think
that if they set the Format of a date/time field to "Short Date", it will
only store the date. This is not true. A date/time always stores the time
portion. The time might be midnight if the value is entered with only the
date part.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Duane, thank you for reply,
I found the problem, it was tblDates
I populated this table from VB 6.0 from datetime picker control
dtPicker.Value property return date and time, in the table itself datatype of
the fields are short date.
Thank you, again now it return the same amount of tickets with different
param.

Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

:

Does your IssueDate field contain a time element also? Do your results vary
if you use:
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 DateValue(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
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

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");