Find MAX Number

[Bob Quintal]

Hi,
I have 20 fields on a form which shows the current progress of a
project, text boxes are named P1 through P20 to show stages.
I would like to have an unbound textbox which would show a total
percent completed.

Only the numbers are shown in the textboxes. P1 etc is the
textbox name.

i.e. P1=0 P2=10 P3=20 P4=25 P5=30 P6=""
P7="" etc Total = 30%

Obviously there will always be a number of textboxes with no data
entered (15 in the example above)
The form is based on a query.

Would I need to set the parameters in the Query? or How would I
code this on the form?

Regards

John

With a table layout like you have, do it in Excel.

To do the calculations properly, you should have a sub-table linked
to the project table that has 3 fields. Project_ID, Period and
PctCompleted

so
ProjectId Period PctCompleted
A12345 P1 0
A12345 P2 10
A12345 P3 20
A12345 P4 25
A12345 P5 30
B76702 P1 15

Since you have nothing for steps 6 and above you have no record.
All you need to do is return the max(pctCompleted) in a query, form
or report.

 

[John]

Hi,
I have 20 fields on a form which shows the current progress of a project,
text boxes are named P1 through P20 to show stages.
I would like to have an unbound textbox which would show a total percent
completed.

Only the numbers are shown in the textboxes. P1 etc is the textbox name.

i.e. P1=0 P2=10 P3=20 P4=25 P5=30 P6="" P7="" etc
Total = 30%

Obviously there will always be a number of textboxes with no data entered
(15 in the example above)
The form is based on a query.

Would I need to set the parameters in the Query? or How would I code this on
the form?

Regards

John

 

[Dale Fye]

John,

While I agree with Bob about your data structure (usually this type of
structure implies poor normalization), we generally have to work with the
hand we've been dealt (whether through our own fault, or others).

I have a function I use for this type of thing, although in all honesty, I
usually only use it to get the max date when comparing dates, or to compare
2 or 3 values.
It accepts an array of values, so you can dump in as many fields as you
want. The down side is that if you pass it values with different data
types, it will evaluate the values you pass it and will return the
"maximum". In your example below, you show numbers and empty strings (do
you really mean empty strings or are these fields actually NULL values? The
function ignores NULL values and zero length strings. You might use this
function in the control source property of your Total textbox like:

=fnMax([P1], [P2], [P3], [P4], [P5], [P6], [P7])

Public Function fnMax(ParamArray SomeValues() As Variant, Optional IgnoreZLS
as boolean = true) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
elseif SomeValue(intLoop) = "" then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function

HTH
Dale

 

[John]

Guys,
Thanks for all your help.
I will try the code and also work on the structure.
Many thanks

John

 

[John]

Dale,
Would I just copy the code into a new module?

Regards

John

 

[John]

Dale
I copied the code below as is, into a new module and there are errors
apparent.
I am being told of a

"compile error: ) expected"
at the end of
SomeValues() As Variant,

Public Function fnMax(ParamArray SomeValues() As Variant, Optional IgnoreZLS
as boolean = true) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
elseif SomeValue(intLoop) = "" then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function


I am using access 2007, would this matter?

Regards

John

 

[Dale Fye]

John,

Duh! My fault, I was writing this directly into the newsgroup, rather than
cutting it from a code module.

When you pass the function a parameter array, it has to be the last variable
in the function declaration(Access doesn't know where the array ends and the
next variable begins). Since I took out the IgnoreZLS code anyway, you
really didn't even need that variable in the declaration.

Change the function declaration to:

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dale

 

[John]

Dale,
Thanks for the reply, but I still cannot get the code to work.
I used the example you sent =fnMax([P1], [P2], [P3], [P4], [P5], [P6], [P7])
in the record source of the Totals Textbox
and the value shown is #Name?

the code is shown below as per my module called fnMax
Each data field is named P1 P2 etc.
Have you any ideas.

Regards

John

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf SomeValue(intLoop) = "" Then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function

 

[John]

Dale,
Found the error,
Line 7 "SomeValue" should be "SomeValues"

Thanks for you help.

Regards

John

Dale,
Thanks for the reply, but I still cannot get the code to work.
I used the example you sent =fnMax([P1], [P2], [P3], [P4], [P5], [P6],
[P7]) in the record source of the Totals Textbox
and the value shown is #Name?

the code is shown below as per my module called fnMax
Each data field is named P1 P2 etc.
Have you any ideas.

Regards

John

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf SomeValue(intLoop) = "" Then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function

John,

Duh! My fault, I was writing this directly into the newsgroup, rather
than cutting it from a code module.

When you pass the function a parameter array, it has to be the last
variable in the function declaration(Access doesn't know where the array
ends and the next variable begins). Since I took out the IgnoreZLS code
anyway, you really didn't even need that variable in the declaration.

Change the function declaration to:

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dale

 

[John]

Dale,
Yet another question.
Now I have the fnMax field working, how can I set up a sum of fnMax in the
subform footer?

Regards

John

Dale,
Found the error,
Line 7 "SomeValue" should be "SomeValues"

Thanks for you help.

Regards

John

Dale,
Thanks for the reply, but I still cannot get the code to work.
I used the example you sent =fnMax([P1], [P2], [P3], [P4], [P5], [P6],
[P7]) in the record source of the Totals Textbox
and the value shown is #Name?

the code is shown below as per my module called fnMax
Each data field is named P1 P2 etc.
Have you any ideas.

Regards

John

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf SomeValue(intLoop) = "" Then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function

John,

Duh! My fault, I was writing this directly into the newsgroup, rather
than cutting it from a code module.

When you pass the function a parameter array, it has to be the last
variable in the function declaration(Access doesn't know where the array
ends and the next variable begins). Since I took out the IgnoreZLS code
anyway, you really didn't even need that variable in the declaration.

Change the function declaration to:

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

Dale

Dale
I copied the code below as is, into a new module and there are errors
apparent.
I am being told of a

"compile error: ) expected"
at the end of
SomeValues() As Variant,

Public Function fnMax(ParamArray SomeValues() As Variant, Optional
IgnoreZLS
as boolean = true) As Variant

Dim intLoop As Integer
Dim myMax As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
elseif SomeValue(intLoop) = "" then
'do nothing
ElseIf IsEmpty(myMax) Or SomeValues(intLoop) > myMax Then
myMax = SomeValues(intLoop)
End If

Next

fnMax = myMax

End Function


I am using access 2007, would this matter?

Regards

John

Dale,
Would I just copy the code into a new module?

Regards

John


Hi,
I have 20 fields on a form which shows the current progress of a
project, text boxes are named P1 through P20 to show stages.
I would like to have an unbound textbox which would show a total
percent completed.

Only the numbers are shown in the textboxes. P1 etc is the textbox
name.

i.e. P1=0 P2=10 P3=20 P4=25 P5=30 P6="" P7=""
etc Total = 30%

Obviously there will always be a number of textboxes with no data
entered (15 in the example above)
The form is based on a query.

Would I need to set the parameters in the Query? or How would I code
this on the form?

Regards

John