Solved: Zero values not listed

[jwise]

I admit I have no Access experience or training. I have written some Excel macros.

The Access query is for real estate management. There are two similar queries. The relevant query is "By agent by week". This means the number of apartments leased by the agent in the last week.

I was asked to update this code. When the query is run, the results omit people who have written zero leases. The boss wants to see all leasing agents. I found this code after pecking around in Access:


Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim stDocName As String
stDocName = "By Agent"
DoCmd.OpenReport stDocName, acPreview
Exit_Command8_Click:
Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub



The code works perfectly... it just doesn't list the names of those agents who had zero leases for the week. Any idea what I must change to list the zero lease agents? I guessed that "DoCmd" was running a subroutine named "By agent", but I can find no code with anything like that name.

TIA

[CreganTur]

I guessed that "DoCmd" was running a subroutine named "By agent", but I can find no code with anything like that name.

Actually that DoCmd is opening a report with the name "By Agent". The entire code you posted does nothing more than launch a report when the button is clicked.

Notice that the variable stDocName is set to the value "By Agent". DoCmd.OpenReport means "Do this Command: Open A Report"
-Then stDocName is in the place for the report's name: "By Agent"
acPreview tells Access how the report should look when it is opened- in this case it's opened in preview mode.

You need to change the report itself to be able to see agents with zero rentals. Take a look at the report in design view and see if you can discover if the report is being fed from a Query.

If the report comes from a Query then just open that query in design view and see if there's any criteria that excludes agents with rentals = Null (or something similar).

If the report is not fed from a query, then you need to look at the format(design) of the report itself.

[jwise]

I appreciate your response. I think you've put me on the trail.

I believe this code is option # 2, i.e. there is no code but a "form". I am probably using the wrong terminology, so I apologize in advance.



When I chased this as described, I found the following:

=SUM([SumOfCode])


I replaced this line by adding the "Nz" function. My reasoning was that zero leases would probably result in a null value in the data field for a "number of leases" that the person did in the week. So here's what it looked like after my change:


=SUM(Nz([SumOfCode]))


When I ran it again, I got no change. I have no clue whether "SumOfCode" is a user variable or something Access related. Am I on the right trail? I am also assuming the sqaure brackets "[]" have some special Access meaning.

[jwise]

After a lot of testing, I've decided there is a fundamental issue controlling this, namely that the database has "lease records" in it and not "absence of lease records". Thus, the inclusion of "0 lease" records is impossible due to the database design.

If the database were designed such that a new lease updated the weekly count of a particular person, then a particular week would have some people with records, and some with nulls since the latter sold no leases. The "0 lease" people would have a missing value that the "Nz" function could turn into "0" in the lease count field.

Thanks for the help.

[CreganTur]

If your boss wants to see a list of agents who haven't been selling any leases (naughty, naughty agents:whip ), then you could create a LEFT OUTER JOIN query between the employees table and the leasing table, with the criteria of (tblLease.Leases) Is Null.

[jwise]

I appreciate the tip.

Your suggestion would work really well since the employee base doesn't change very often. It would require updating as people leave/move to new offices, but since the turnover rate is low, this is doable.

Thanks again for the suggestion and the insight.