Report Not working

D

DeCiDeR

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
 
J

Jeff Boyce

We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV
 
D

DeCiDeR

Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);
 
J

Jeff Boyce

I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff said:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV
Click to expand...
Click to expand...
 
D

DeCiDeR

Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff said:
I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff said:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
Click to expand...
Click to expand...
Click to expand...
 
L

lewie

DeCiDeR said:
Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff said:
I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff Boyce wrote:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
Click to expand...
Click to expand...
Click to expand...
 
L

lewie

add a default value of 0 in number field so it will show up
Lewie said:
Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff said:
I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff Boyce wrote:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
Click to expand...
Click to expand...
Click to expand...
 
D

DeCiDeR

It only shows the hospital where the surgery happened on a particular
date. There is no specific field where to add 0 number. The count is
done if it finds facility it adds as 1 on query sum.. for that date...
add a default value of 0 in number field so it will show up
Lewie said:
Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff said:
I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff Boyce wrote:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

If you embed your Sum() within the Nz() function, you can specify a value
("0") when there are no records to be counted for that period.

Regards

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff said:
I see the Nz() function in your SQL statement, but no "alternate value"
for
the Nz() function to use if the BeginDate on the form is Null. Then, in
the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the
simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

DeCiDeR said:
Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff Boyce wrote:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the
query
which will ask me for StartDate and EndDate. If I select StartDate
as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for
Dallas

M. for June coming from table.
Click to expand...
Click to expand...
Click to expand...
 
L

lewie

What I usually do in this case is convert the query to a make table and
make a table for my recordsource for my report. then i add defaults to
the appropriate fileds. then i change the query to an append query.
then i make a delete query then i make a macro to run delete then
append queries before opening the report. Take it for what it is
worth....
Lewie said:
It only shows the hospital where the surgery happened on a particular
date. There is no specific field where to add 0 number. The count is
done if it finds facility it adds as 1 on query sum.. for that date...
add a default value of 0 in number field so it will show up
Lewie said:
Here is simple sql query which i did. Can you tell me what can I do so
if the facility doesn't have any count for either month it will come as
zero count.

SELECT [Surgery by Facility Query 01].Date_, [Surgery by Facility Query
01].[Location Code], [Surgery by Facility Query 01].Facility, [Surgery
by Facility Query 01].Month_Name, [Surgery by Facility Query 01].Day,
Sum([Surgery by Facility Query 01].Surgery_Count) AS SumOfSurgery_Count
FROM [Surgery by Facility Query 01]
GROUP BY [Surgery by Facility Query 01].Date_, [Surgery by Facility
Query 01].[Location Code], [Surgery by Facility Query 01].Facility,
[Surgery by Facility Query 01].Month_Name, [Surgery by Facility Query
01].Day
HAVING ((([Surgery by Facility Query 01].Date_) Between
[Forms]![Surgery Form 02]![BeginDate] And [Forms]![Surgery Form
02]![EndDate] Or ([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)));


Jeff Boyce wrote:
I see the Nz() function in your SQL statement, but no "alternate value" for
the Nz() function to use if the BeginDate on the form is Null. Then, in the
next phrase, you DON'T allow for null values, and you use Year(Now()),
instead of Year(Date()), as you had previously.

If this were mine, I'd back off a step or two. First, I'd get the simplest
underlying query working, returning data. Then I'd build back in
complexity, one step at a time, testing along the way.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

Here is the Cross Tab Query which I use.

PARAMETERS [Forms]![Surgery Form 02]![BeginDate] DateTime,
[Forms]![Surgery Form 02]![EndDate] DateTime;
TRANSFORM Sum([Surgery by Facility Query 01].Surgery_Count) AS
SumOfSurgery_Count
SELECT [Surgery by Facility Query 01].[Location Code] AS Region,
[Surgery by Facility Query 01].Facility, [Surgery by Facility Query
01].Month_Name AS [Month]
FROM [Surgery by Facility Query 01]
WHERE ((([Surgery by Facility Query 01].Date_) Between [Forms]![Surgery
Form 02]![BeginDate] And [Forms]![Surgery Form 02]![EndDate] Or
([Surgery by Facility Query
01].Date_)=DateSerial(Year(Date()),Nz(Month([Forms]![Surgery Form
02]![BeginDate])),Nz(Day([Forms]![Surgery Form 02]![BeginDate]))) Or
([Surgery by Facility Query 01].Date_) Between
DateSerial(Year(Now()),Month([Forms]![Surgery Form 02]![EndDate])-1,1)
And DateSerial(Year(Now()),Month([Forms]![Surgery Form
02]![BeginDate]),0)))
GROUP BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
ORDER BY [Surgery by Facility Query 01].[Location Code], [Surgery by
Facility Query 01].Facility, [Surgery by Facility Query 01].Month_Name
DESC
PIVOT [Surgery by Facility Query 01].Day In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);



Jeff Boyce wrote:
We can't see your query or your tables, so it's a bit tough offering
specific suggestions.

Consider posting the SQL statement your query uses...

Regards

Jeff Boyce
Microsoft Office/Access MPV

I have a query which is not having any facility surgery count for
current month or previous month from table. I currently run the query
which will ask me for StartDate and EndDate. If I select StartDate as
6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for
Facility from the table for the selected date and also for previous
month. Here is how my report pulls right now.

Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3


Dallas M. May 7


Here is how my report should pull.


Facility Name Month TotalCount
U of M June 5
May 7


Fresno June 3
May 0


Dallas M. June 0
May 7


My query doesnt have any data for Fresno for May and nothing for Dallas

M. for June coming from table.
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

Need a Cross Tab Report Fix 5
Mapping strings to integers 6
Combining Data for a Quarterly Report 1
How do I custom sort records in a report? 2
6 month time bracket in report header 2
Creating a Yearly Report 6
Previous Months data 1
extracting data from table for title of report 5

Top