changing Error# generated by count() to zero

[Sandy]

Hi!
I'm using a form using a list box to select name and 2 text boxes to
select a date range. Once the information is selected, it produces a
report listing the completed projects. I have a project type header
that counts the number of projects in each project type. Project type
is ALWAYS filled in and has NO null values - it is also a character
field. As the name and dates are selected, everything is fine - until
there is no project completed within the dates specified for that name.
Then, I get an Error# where there count is supposed to be on the
report. Also, because the name in the title pulls from the query as
well, the name is blank. What I would be ideal is for the name to
populate even though there are no values for the name and the Error# to
be replaced with a zero.
I've tried using the formula (in the control source), which I found
from someone having a similar problem years ago.
IIF(IsNull([CourseID]=True),0,Count([CourseID])) (of course,
replacing courseID with projecttype)
However, this does not seem to solve either of my issues.
I'm hesitant to use too much VBA, since my knowledge is very limited at
this time.

Does anyone have any other suggestions?

Thanks!
Sandy

 

[Steve Schapel]

Sandy,

Can you please post back with the SQL view of the query that the report
is based on? Thanks. You probably need a Left Join in the place of an
Inner Join in your query, along with the use of a Nz() function, but it
is difficult to advise specifically without knowing what you are working
with.

I am not sure where you tried to use the expression with the IIf()
function, but in any case the syntax is not correct. Anyway, it
probably does not apply to your situation.

 

[Sandy]

Steve,
Since I'm pulling all my data from 1 table, I don't think I need to
join anything. I tried the If statement in my initital message in the
Control Source section of the Text Box.

In addition, I have a form that I am using to enter the Client Name and
Start/End Dates. From this, it creates a report.

Below is the information you requested....

Private Sub Report_Open(Cancel As Integer)

Reports!CompletedCalcs.RecordSource = "SELECT
ProjectTable.[ClientName], ProjectTable.[ProjectType],
ProjectTable.[ProjectName], ProjectTable.[DateCompleted],
ProjectTable.[DateReviewed] , ProjectTable.[DateAssigned],
ProjectTable.[Comments] FROM ProjectTable where
((ProjectTable.[ClientName]= '" & ReportClientName & "') and
((ProjectTable.[ProjectType]= 'Ben Payment Update') or
(ProjectTable.[ProjectType]= 'Ben Payment Processing') or
(ProjectTable.[ProjectType]= 'Benefit Calculation')) and
((ProjectTable.DateReviewed) between #" & StartDate & "# AND #" &
EndDate & "#)) ORDER BY ProjectTable.[DateAssigned];"

End Sub

Thanks for your help!
Sandy

Sandy,

Can you please post back with the SQL view of the query that the report
is based on? Thanks. You probably need a Left Join in the place of an
Inner Join in your query, along with the use of a Nz() function, but it
is difficult to advise specifically without knowing what you are working
with.

I am not sure where you tried to use the expression with the IIf()
function, but in any case the syntax is not correct. Anyway, it
probably does not apply to your situation.

--
Steve Schapel, Microsoft Access MVP

Hi!
I'm using a form using a list box to select name and 2 text boxes to
select a date range. Once the information is selected, it produces a
report listing the completed projects. I have a project type header
that counts the number of projects in each project type. Project type
is ALWAYS filled in and has NO null values - it is also a character
field. As the name and dates are selected, everything is fine - until
there is no project completed within the dates specified for that name.
Then, I get an Error# where there count is supposed to be on the
report. Also, because the name in the title pulls from the query as
well, the name is blank. What I would be ideal is for the name to
populate even though there are no values for the name and the Error# to
be replaced with a zero.
I've tried using the formula (in the control source), which I found
from someone having a similar problem years ago.
IIF(IsNull([CourseID]=True),0,Count([CourseID])) (of course,
replacing courseID with projecttype)
However, this does not seem to solve either of my issues.
I'm hesitant to use too much VBA, since my knowledge is very limited at
this time.

Does anyone have any other suggestions?

Thanks!
Sandy

 

[Steve Schapel]

Sandy,

Thanks for that clarification. Do I understand you correctly?... We
are talking about the ClientName? So if the selected client has no
projects with the DateReviewed within the specified date reange, you
still want the report generated for that client, to show 0 projects.
Right? Well, you can't rely on the Nz() function, or a IIf(IsNull()...)
type of process, because we're not dealing with Null, we're dealing with
"non-existent". There is no data, which is a different kettle of fish
from there is data with Null value.

So, the report needs a record for the selected client, even if there are
no projects. Which is where my Left Join idea came from, but this
assumed you would have a Clients table where all potential clients are
listed. Do you? How to handle the situation will largely depend on the
answer to this question, so I won't go any further down the track until
I know that.

 

[Sandy]

Steve,
Yes, sort of. The user selects Client Name and a date range. However,
I'm counting project type, since I'm providing subtotals of each type
of project we have worked on.

So if the selected client has no

projects with the DateReviewed within the specified date reange, you
still want the report generated for that client, to show 0 projects.
Right? Yes! Exactly!

Yes, there are two tables - the project table and the one with just a
list of Clients. They are currently joined by Client Name.

Sandy

Sandy,

Thanks for that clarification. Do I understand you correctly?... We
are talking about the ClientName? So if the selected client has no
projects with the DateReviewed within the specified date reange, you
still want the report generated for that client, to show 0 projects.
Right? Well, you can't rely on the Nz() function, or a IIf(IsNull()...)
type of process, because we're not dealing with Null, we're dealing with
"non-existent". There is no data, which is a different kettle of fish
from there is data with Null value.

So, the report needs a record for the selected client, even if there are
no projects. Which is where my Left Join idea came from, but this
assumed you would have a Clients table where all potential clients are
listed. Do you? How to handle the situation will largely depend on the
answer to this question, so I won't go any further down the track until
I know that.

--
Steve Schapel, Microsoft Access MVP

Steve,
Since I'm pulling all my data from 1 table, I don't think I need to
join anything. I tried the If statement in my initital message in the
Control Source section of the Text Box.

In addition, I have a form that I am using to enter the Client Name and
Start/End Dates. From this, it creates a report.

Below is the information you requested....

Private Sub Report_Open(Cancel As Integer)

Reports!CompletedCalcs.RecordSource = "SELECT
ProjectTable.[ClientName], ProjectTable.[ProjectType],
ProjectTable.[ProjectName], ProjectTable.[DateCompleted],
ProjectTable.[DateReviewed] , ProjectTable.[DateAssigned],
ProjectTable.[Comments] FROM ProjectTable where
((ProjectTable.[ClientName]= '" & ReportClientName & "') and
((ProjectTable.[ProjectType]= 'Ben Payment Update') or
(ProjectTable.[ProjectType]= 'Ben Payment Processing') or
(ProjectTable.[ProjectType]= 'Benefit Calculation')) and
((ProjectTable.DateReviewed) between #" & StartDate & "# AND #" &
EndDate & "#)) ORDER BY ProjectTable.[DateAssigned];"

End Sub

 

[Steve Schapel]

Sandy,

Sorry to labour the point, but I'm afraid I'm still not sure what we're
after here. Is the problem related to a client having no projects at
all, during the selected period, or is the problem related to no
projects within a particular project type? I am not sure what you mean
by "counting project type". If it's just a simple matter of a client
with no projects at all, then I meant something like this...
"SELECT Clients.[ClientName], ProjectTable.[ProjectType],
ProjectTable.[ProjectName], ProjectTable.[DateCompleted],
ProjectTable.[DateReviewed] , ProjectTable.[DateAssigned],
ProjectTable.[Comments] FROM Clients LEFT JOIN ProjectTable ON
Clients.[ClientName]=ProjectTable.[ClientName] WHERE
((ProjectTable.[ClientName]= '" & ReportClientName & "') AND
((ProjectTable.[ProjectType]= 'Ben Payment Update') or
(ProjectTable.[ProjectType]= 'Ben Payment Processing') or
(ProjectTable.[ProjectType]= 'Benefit Calculation')) AND
((ProjectTable.DateReviewed) Between #" & StartDate & "# And #" &
EndDate & "#)) ORDER BY ProjectTable.[DateAssigned];"

 

[Sandy]

Steve,
The problem is related to a client having no projects within a certain
timeframe. If a client has projects, but not all types, it works fine
- just doesn't show that type at all.

Within the project type header, I have a formula to count the number of
projects within each type =count([projecttype]) . This is what I ment
by "counting project type".

I've copied/pasted what you had below within the SQL report code, but
it doesn't seem to work. I am still getting the Error# message instead
of zero as a result of the formula above ( =count([projecttype]) ).

Is there another place this code should go? I've got a query that
reads 2 tables, a report that feeds off the query and a form that feeds
in the report. Most of the code for the form is in VB (which I know
little of).

Thanks!
Sandy

Sandy,

Sorry to labour the point, but I'm afraid I'm still not sure what we're
after here. Is the problem related to a client having no projects at
all, during the selected period, or is the problem related to no
projects within a particular project type? I am not sure what you mean
by "counting project type". If it's just a simple matter of a client
with no projects at all, then I meant something like this...
"SELECT Clients.[ClientName], ProjectTable.[ProjectType],
ProjectTable.[ProjectName], ProjectTable.[DateCompleted],
ProjectTable.[DateReviewed] , ProjectTable.[DateAssigned],
ProjectTable.[Comments] FROM Clients LEFT JOIN ProjectTable ON
Clients.[ClientName]=ProjectTable.[ClientName] WHERE
((ProjectTable.[ClientName]= '" & ReportClientName & "') AND
((ProjectTable.[ProjectType]= 'Ben Payment Update') or
(ProjectTable.[ProjectType]= 'Ben Payment Processing') or
(ProjectTable.[ProjectType]= 'Benefit Calculation')) AND
((ProjectTable.DateReviewed) Between #" & StartDate & "# And #" &
EndDate & "#)) ORDER BY ProjectTable.[DateAssigned];"

--
Steve Schapel, Microsoft Access MVP

Steve,
Yes, sort of. The user selects Client Name and a date range. However,
I'm counting project type, since I'm providing subtotals of each type
of project we have worked on.

So if the selected client has no

Yes, there are two tables - the project table and the one with just a
list of Clients. They are currently joined by Client Name.