Report/Coding help - Count

[cbearden]

I have a report that shows:

Dlr, Count_of_Delinq, Min Days, Max Days


It shows up fine. But what I'm trying to do is only show those with multiple counts.(leaving off the ones that have only 1 count.)

I've tried coding, here's what i have:

If txtTotalDays > 1 Then
txtTotalDays.Visible = True
Else
txtTotalDays.Visible = False
End If

Don't know if I am going in the right direction w/ coding.

Thanks in advance for the help.

 

[THWatson]

cbearden
From your post, my assumption is that you want to show only those records where Count_of_Delinq is more than 1. Is that correct?

If so, what happens if you just put >1 in the Criteria column in the query?

Or...are you wanting to show all records in the report, but only Count those where the Count_of_Delinq is greater than 1?

If so, in the Report Footer, try an unbound text box with the Control Source as =Sum(Abs(Count_of_Delinq)>1)


Tom

 

[cbearden]

I put that in but it didn't do anything. I went back to design view and the control source was empty.

In my query, I have an expression that gets Total Days. In the criteria, I have [Enter # of Days]. In the report, I have an unbound txt box to Count the number of accounts per Dealer. This txt box is named txtTotalDays. All my info is in the Dealer group footer

 

[THWatson]

cbearden
There a couple of things I'm not clear about...

1. Why do you put criteria [Enter # of Days] in the TotalDays column in the query?

2. You say you went back in Design view and the control source I suggested was empty ?? That would mean that the expression didn't get entered.
The purpose of Sum(Abs) is to disregard any records where there is no count for that field.
In any event, you could try putting the expression I suggested, =Sum(Abs(Count_of_Delinq)>1), in an unbound text box in the Group Footer...let's call that text box txtCount1. Then put an additional text box that references that text box...let's call it txtCount2... and make that second box a Running Sum OverAll. And then in your Report Footer, put an unbound text box with the expression txtCount2
Hide the text boxes you don't want to show.

Tom

 

[THWatson]

cbearden
Know what? I think I got off track somewhere along the line. I sincerely apologize. Let's start again.

Is this the case? If DealerA has 7 records to show, you only want to show it once?

Tom

 

[THWatson]

cbearden
I went back and reviewed everything. I think this is what you want...Say there are two dealers, DealerA and DealerB. If a dealer has multiple records to show, you want it shown, but if only one record you want to show nothing. Correct this time?

You could try this code on the Format event for the Detail section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtTotalDays > 1 Then
Me.Detail.Visible = True
Me.GroupFooter0.Visible = True
Else: Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If

If the name of your GroupFooter is something different than I show here, you will need to change it.

I think I'm having a bad day !! Hope we're getting closer. If I'm still off track, let me know what I'm missing.

Tom

 

[Remou]

Hi cbearden
Do you mean total days as a control in the report detail? If so, you might consider Conditional Formatting. If it is in a footer, coding seems like a good idea.

 

[cbearden]

No that isn't the case.

Here's what my report shows now.

Dealer TotalDelinq MinDay MaxDay TotalCost
------ ----------- ------ ------- ---------
1 4 93 302 4000.00
2 1 103 103 1000.00
3 1 93 93 750.00
4 5 193 408 5000.00
5 3 91 150 4500.00

Here's what I'm trying to get:

Dealer TotalDelinq MinDay MaxDay TotalCost
------ ----------- ------ ------- ---------
1 4 93 302 4000.00
4 5 193 408 5000.00
5 3 91 150 4500.00

The dealers with multiple accounts(count > 1) will show, all others will not. TotalDelinq is the Count of Accounts over [Enter # of Days]. The name of it is txtTotalDays.

Thanks

 

[THWatson]

cbearden
Then the code that I provided for the Detail section should work.

Tom

 

[Remou]

Hi
Would it not be better to simply select the relevant records for the report? That is, change the Record Source to something like:
SELECT * FROM tblTable WHERE (((StatsShort.SentTo)>[Forms]![frmForm]![txtTotalDays]));

 

[cbearden]

Thanks, I will try what you had suggested about the format event.

 

[cbearden]

All my fields are in the intDealerNum Header (GroupHeader0). So I used this code in this section:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtTotalDays > 1 Then
Me.Detail.Visible = True
Me.GroupHeader0.Visible = False
Else
Me.Detail.Visible = False
Me.GroupHeader0.Visible = False
End If
End Sub

When the report was opened, it showed nothing.

Thanks

 

[THWatson]

cbearden
If all of your controls are in the Group Header, then is there nothing in the Detail section?

If that's the case, then wouldn't you want to make the Group Header visible or not visible, rather than the Detail section?

I made up a small test database here, and what I suggested worked for me.

Tom

 

[cbearden]

Changed something on it and it worked. Thanks for the help!

 

[cbearden]

I have a question about the coding...

what does this part do:

(Cancel As Integer, FormatCount As Integer)

Everything works. Just curious.

 

[THWatson]

cbearden
That is put in automatically by Access.

Most things in Access have an Integer value. For example, take a look at the various possibilities in the construction of a Message Box, and you will find that vbOKOnly has an Integer value of 0, vbOKCancel has an Integer value of 1, and so on.
"Cancel As Integer" means the Integer value that is applied if the event is cancelled.

Access keeps track of the number of times that the section (and each section) has been formatted. This is the FormatCount value, and it is an Integer value. This would allow you to put, if you needed to, code that did a particular thing if the FormatCount was >1 or something such as that.
For example...
If FormatCount >1 Then Me.txtWhatever.Visible = True

Hope that helps.

Tom

 

[THWatson]

cbearden
Just to amplify what I said about "Cancel as Integer."

I don't know of a situation where you might use "Cancel as Integer" in a report. However, if you did, the line
Cancel = True
would appear in the code. In Access, True = -1, whereas False = 0

Tom