By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Max Value of Field (with a twist)

P: n/a
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.

is this possible?

TIA,

KO

Mar 19 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.

is this possible?

TIA,

KO
Greetings Turtle.

Air Code:

Dim varMax As Variant
Dim ctl As Control

varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:

For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl

James A. Fortune
CD********@FortuneJames.com

Mar 19 '07 #2

P: n/a
On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO

Greetings Turtle.

Air Code:

Dim varMax As Variant
Dim ctl As Control

varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl

You can also filter out some control names by enclosing constructions
such as:

For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl

For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl

James A. Fortune
CDMAPos...@FortuneJames.com
Thanks James,

I am really a beginner with VB and am getting erorrs on the code.

A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.

Thanks,

KO

Mar 20 '07 #3

P: n/a
On Mar 20, 11:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:


On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO
Greetings Turtle.
Air Code:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:
For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl
James A. Fortune
CDMAPos...@FortuneJames.com

Thanks James,

I am really a beginner with VB and am getting erorrs on the code.

A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.

Thanks,
1) I did some testing of the basic loop before posting. I placed the
code in the Report_Open event.

2) ctl.name in the ctl loop will go through all controls on the report
unless they are excluded by your code. Thus, the control where you
want to put the results should be excluded. If you use a Tag for a
column just don't put the Tag value in the totals textbox.

For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": 'Code for summing the values
End Select
Next ctl

or maybe:

Dim varMax As Variant
Dim ctl As Control

varMax = Null
For Each ctl In Me.Controls
If ctl.Name <"txtSumOfVisible" Then
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
End If
Next ctl

Do you use code to hide controls? If so, where is it located? If
that code is in the OnFormat event then the code above may well have
to be changed and placed after that code. I didn't try this from the
OnFormat event. Which event you use depends on the level where you
are setting the Visible property. I tried an example and found that
the OnFormat event can be used to set the Visible property of a
textbox separately for individual rows. The same textbox blinking in
and out by record is a different problem. I think I have used the
syntax Me.Controls(ctl.Name).Value before on forms but Access doesn't
seem to like it on reports. The code above in the Report_Open event
assumes that the controls set with the Visible property True stay
visible throughout the report. I hope you followed most of that.
Anyway, post back with more details, particularly whether the Visible
property is set using code or not, and I'll try to work it out for you
since what you have left to do is more than I thought and definitely
too much for a beginner to finish.

James A. Fortune
CD********@FortuneJames.com

Mar 20 '07 #4

P: n/a
On Mar 20, 4:47 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 20, 11:59 am, "turtle" <kol...@vistacontrols.comwrote:


On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO
Greetings Turtle.
Air Code:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:
For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl
James A. Fortune
CDMAPos...@FortuneJames.com
Thanks James,
I am really a beginner with VB and am getting erorrs on the code.
A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.
Thanks,

1) I did some testing of the basic loop before posting. I placed the
code in the Report_Open event.

2) ctl.name in the ctl loop will go through all controls on the report
unless they are excluded by your code. Thus, the control where you
want to put the results should be excluded. If you use a Tag for a
column just don't put the Tag value in the totals textbox.

For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": 'Code for summing the values
End Select
Next ctl

or maybe:

Dim varMax As Variant
Dim ctl As Control

varMax = Null
For Each ctl In Me.Controls
If ctl.Name <"txtSumOfVisible" Then
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
End If
Next ctl

Do you use code to hide controls? If so, where is it located? If
that code is in the OnFormat event then the code above may well have
to be changed and placed after that code. I didn't try this from the
OnFormat event. Which event you use depends on the level where you
are setting the Visible property. I tried an example and found that
the OnFormat event can be used to set the Visible property of a
textbox separately for individual rows. The same textbox blinking in
and out by record is a different problem. I think I have used the
syntax Me.Controls(ctl.Name).Value before on forms but Access doesn't
seem to like it on reports. The code above in the Report_Open event
assumes that the controls set with the Visible property True stay
visible throughout the report. I hope you followed most of that.
Anyway, post back with more details, particularly whether the Visible
property is set using code or not, and I'll try to work it out for you
since what you have left to do is more than I thought and definitely
too much for a beginner to finish.

James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -

- Show quoted text -
James,

I am amazed by your kindness in this. Thank you for taking so much
time to help with this.
I have used code to hide a text box on the report using the OnFormat
event.
Here is the code:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As
Integer)
If Me.Text63 0 Then
Me.Text107.Visible = True
Me.Label96.Visible = True

Else:
Me.Text107.Visible = False
Me.Label96.Visible = False
End If
End Sub

does that help you at all?
Once again, thanks for your time on this.

Mar 21 '07 #5

P: n/a
On Mar 21, 10:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 20, 4:47 pm, CDMAPos...@FortuneJames.com wrote:


On Mar 20, 11:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO
Greetings Turtle.
Air Code:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:
For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl
James A. Fortune
CDMAPos...@FortuneJames.com
Thanks James,
I am really a beginner with VB and am getting erorrs on the code.
A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.
Thanks,
1) I did some testing of the basic loop before posting. I placed the
code in the Report_Open event.
2) ctl.name in the ctl loop will go through all controls on the report
unless they are excluded by your code. Thus, the control where you
want to put the results should be excluded. If you use a Tag for a
column just don't put the Tag value in the totals textbox.
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": 'Code for summing the values
End Select
Next ctl
or maybe:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If ctl.Name <"txtSumOfVisible" Then
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
End If
Next ctl
Do you use code to hide controls? If so, where is it located? If
that code is in the OnFormat event then the code above may well have
to be changed and placed after that code. I didn't try this from the
OnFormat event. Which event you use depends on the level where you
are setting the Visible property. I tried an example and found that
the OnFormat event can be used to set the Visible property of a
textbox separately for individual rows. The same textbox blinking in
and out by record is a different problem. I think I have used the
syntax Me.Controls(ctl.Name).Value before on forms but Access doesn't
seem to like it on reports. The code above in the Report_Open event
assumes that the controls set with the Visible property True stay
visible throughout the report. I hope you followed most of that.
Anyway, post back with more details, particularly whether the Visible
property is set using code or not, and I'll try to work it out for you
since what you have left to do is more than I thought and definitely
too much for a beginner to finish.
James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -
- Show quoted text -

James,

I am amazed by your kindness in this. Thank you for taking so much
time to help with this.
I have used code to hide a text box on the report using the OnFormat
event.
Here is the code:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As
Integer)
If Me.Text63 0 Then
Me.Text107.Visible = True
Me.Label96.Visible = True

Else:
Me.Text107.Visible = False
Me.Label96.Visible = False
End If
End Sub

does that help you at all?
Once again, thanks for your time on this>
Yes, that helps a lot. I'll see what I can do.

James A. Fortune
CD********@FortuneJames.com

Mar 23 '07 #6

P: n/a
On Mar 21, 10:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 20, 4:47 pm, CDMAPos...@FortuneJames.com wrote:


On Mar 20, 11:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO
Greetings Turtle.
Air Code:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:
For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl
James A. Fortune
CDMAPos...@FortuneJames.com
Thanks James,
I am really a beginner with VB and am getting erorrs on the code.
A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.
Thanks,
1) I did some testing of the basic loop before posting. I placed the
code in the Report_Open event.
2) ctl.name in the ctl loop will go through all controls on the report
unless they are excluded by your code. Thus, the control where you
want to put the results should be excluded. If you use a Tag for a
column just don't put the Tag value in the totals textbox.
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": 'Code for summing the values
End Select
Next ctl
or maybe:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If ctl.Name <"txtSumOfVisible" Then
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
End If
Next ctl
Do you use code to hide controls? If so, where is it located? If
that code is in the OnFormat event then the code above may well have
to be changed and placed after that code. I didn't try this from the
OnFormat event. Which event you use depends on the level where you
are setting the Visible property. I tried an example and found that
the OnFormat event can be used to set the Visible property of a
textbox separately for individual rows. The same textbox blinking in
and out by record is a different problem. I think I have used the
syntax Me.Controls(ctl.Name).Value before on forms but Access doesn't
seem to like it on reports. The code above in the Report_Open event
assumes that the controls set with the Visible property True stay
visible throughout the report. I hope you followed most of that.
Anyway, post back with more details, particularly whether the Visible
property is set using code or not, and I'll try to work it out for you
since what you have left to do is more than I thought and definitely
too much for a beginner to finish.
James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -
- Show quoted text -

James,

I am amazed by your kindness in this. Thank you for taking so much
time to help with this.
I have used code to hide a text box on the report using the OnFormat
event.
Here is the code:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As
Integer)
If Me.Text63 0 Then
Me.Text107.Visible = True
Me.Label96.Visible = True

Else:
Me.Text107.Visible = False
Me.Label96.Visible = False
End If
End Sub

does that help you at all?
Once again, thanks for your time on this
Here's the experiment I ran:

MyTable
ID AutoNumber
VCount Long
Glr Text
Ctm Text
NCount Long

ID VCount Glr Ctm NCount
1 22 ACR A 200
2 600 DWN A 300
3 400 ACR A 400
4 0 ACR B 50
5 500 DWN B 25
6 50 ACR A 250
7 22 ACR A 500

Report1
+Glr Header
lblGlr txtGlr
+VCount Header
lblVCount txtBox
+Detail
txtCtm lblNCount txtNCount txtID
+VCountFooter
lblMaxVisibleNCount txtVCountSum
+Glr Footer
lblSumIncludingInvisible txtSumIncludingInvisible

Report1 RecordSource = "SELECT * FROM MyTable;"
txtGlr ControlSource = Glr
txtBox ControlSource = VCount
txtCtl ControlSource = Ctm
txtNCount ControlSource = NCount
txtID ControlSource = ID
txtVCountSum ControlSource = =MaxVisibleNCount()
txtSumIncludingInvisible = =Sum([NCount])

'Code behind Report1
Option Compare Database
Option Explicit
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As
Integer)

If Me!txtBox.Value 0 Then
Me!lblNCount.Visible = True
Me!txtNCount.Visible = True
Else
Me!lblNCount.Visible = False
Me!txtNCount.Visible = False
End If
End Sub

Public Function MaxVisibleNCount() As Long
Dim varMax As Variant
Dim ctl As Control
Dim strCtlName As String

varMax = Null
For Each ctl In Me.Controls
strCtlName = ctl.Name
'If ctl.Name <"txtSumOfVisible" Then
If ctl.Properties("Visible") = True Then
If strCtlName = "txtNCount" Then
'Only add the txtNCount values that are currently visible
If Not IsNull(Controls.Item(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Controls.Item(ctl.Name).Value
Else
If Controls.Item(ctl.Name).Value varMax Then varMax =
Controls.Item(ctl.Name).Value
End If
End If
End If
End If
'End If
Next ctl
MaxVisibleNCount = Nz(varMax)
End Function

Results:

....
Ctm: A NC: 500 ID: 7
Ctm: A NC: 200 ID: 1
Max Visible NCount: 200

This is obviously incorrect. I suspect that each 'Format Event' of
the values in the Detail section causes Access to forget about the
visibility of the previous Detail values. Thus, the last one
formatted is selected as the 'Maximum.' Instead of playing around
with trying to save or accumulate values in the Detail section as they
are made visible or not, I think a better option is available. The
control in the footer that is to show the maximum can use the DLookup
function using either the first header value or both header values
along with the same condition that is used to make the control visible
or not. I will post again later with an example.

James A. Fortune
CD********@FortuneJames.com

Mar 26 '07 #7

P: n/a
On Mar 21, 10:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 20, 4:47 pm, CDMAPos...@FortuneJames.com wrote:


On Mar 20, 11:59 am, "turtle" <kol...@vistacontrols.comwrote:
On Mar 19, 3:55 pm, CDMAPos...@FortuneJames.com wrote:
On Mar 19, 12:40 pm, "turtle" <kol...@vistacontrols.comwrote:
I want to find out the max value of a field on a report if the field
is not hidden. I have formatting on the report and if the field
doesn't meet a certain criteria then it is hidden. I want to get a
max of the field for the ones that are not hidden.
is this possible?
TIA,
KO
Greetings Turtle.
Air Code:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
Next ctl
You can also filter out some control names by enclosing constructions
such as:
For Each ctl In Me.Controls
If Right(ctl.Name, 3) = "Amt" Then
...
End If
Next ctl
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": ...
End Select
Next ctl
James A. Fortune
CDMAPos...@FortuneJames.com
Thanks James,
I am really a beginner with VB and am getting erorrs on the code.
A couple of questions:
1) Do i put this code in the "OnFormat" event?
2) The places in your code where you put (ctl.name) is that the name
of my field where i have hidden or the name of the field where i want
to put the results.
Thanks,
1) I did some testing of the basic loop before posting. I placed the
code in the Report_Open event.
2) ctl.name in the ctl loop will go through all controls on the report
unless they are excluded by your code. Thus, the control where you
want to put the results should be excluded. If you use a Tag for a
column just don't put the Tag value in the totals textbox.
For Each ctl In Me.Controls
Select Case Me.Controls(ctl.Name).Tag
Case "ColumnName": 'Code for summing the values
End Select
Next ctl
or maybe:
Dim varMax As Variant
Dim ctl As Control
varMax = Null
For Each ctl In Me.Controls
If ctl.Name <"txtSumOfVisible" Then
If Me.Controls(ctl.Name).Visible = True Then
If Not IsNull(Me.Controls(ctl.Name).Value) Then
If IsNull(varMax) Then
varMax = Me.Controls(ctl.Name).Value
Else
If Me.Controls(ctl.Name).Value varMax Then varMax =
Me.Controls(ctl.Name).Value
End If
End If
End If
End If
Next ctl
Do you use code to hide controls? If so, where is it located? If
that code is in the OnFormat event then the code above may well have
to be changed and placed after that code. I didn't try this from the
OnFormat event. Which event you use depends on the level where you
are setting the Visible property. I tried an example and found that
the OnFormat event can be used to set the Visible property of a
textbox separately for individual rows. The same textbox blinking in
and out by record is a different problem. I think I have used the
syntax Me.Controls(ctl.Name).Value before on forms but Access doesn't
seem to like it on reports. The code above in the Report_Open event
assumes that the controls set with the Visible property True stay
visible throughout the report. I hope you followed most of that.
Anyway, post back with more details, particularly whether the Visible
property is set using code or not, and I'll try to work it out for you
since what you have left to do is more than I thought and definitely
too much for a beginner to finish.
James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -
- Show quoted text -

James,

I am amazed by your kindness in this. Thank you for taking so much
time to help with this.
I have used code to hide a text box on the report using the OnFormat
event.
Here is the code:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As
Integer)
If Me.Text63 0 Then
Me.Text107.Visible = True
Me.Label96.Visible = True

Else:
Me.Text107.Visible = False
Me.Label96.Visible = False
End If
End Sub

does that help you at all?
Once again, thanks for your time on this
I changed the txtVCount ControlSource (all one line) to:

=DLookUp("Max([NCount])","MyTable","[Glr] = " & Chr(34) &
CStr([txtGlr].[Value]) & Chr(34) & " AND [VCount] = " & CStr([txtBox].
[Value]) & " AND txtBox.Value 0")

The DMax function could have been used of the DLookup function. The
expression above produces a blank when there are no visible NCount
values. It produced the correct values for the sample data. If you
possibly expect txtGlr or txtBox to contain a Null value then perhaps
use the Nz() function on them -- txtGlr to an empty string "" when
Null and maybe 0 for txtBox.Value when Null in both spots. The 0 for
txtBox.Value will prevent any records matching the '0' criterion
when txtBox.Value is Null so a blank should show up. Post back if you
have any questions.

James A. Fortune
CD********@FortuneJames.com

Mar 27 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.