New Function question

H

HD87glide

Hello Group! I am looking for some help. In researching a problem I found a
post that had some code for a function. When I built the function and
modified the code to produce what I needed and use the 'debug' feature it
seems to work, but the values are not produced on my form.

The function should take a group of numbers and return the smallest number
in the array. An example would be:
LowestValue (1, 10, 100, 1000, 10000)

Function LowestValue(ParamArray LowArray() As Variant)

Dim dtmLowestValue As Integer
Dim intLoop As Integer

dtmLowestValue = LowArray(0)
For intLoop = 1 To UBound(LowArray())
If IsNull(LowArray(intLoop)) = False Then
If LowArray(intLoop) < dtmLowestValue Then
dtmLowestValue = LowArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function


The function seems to run as I can walk through it with debug and can see
the values change. How do I get the final value to show up on my form? The
input numbers for the function will be calculated from other fields on the
form so I cannot use a table to produce the results.

Thanks for your help!
Jeff
 
K

Ken Snell [MVP]

Is the function in the control source for a textbox that is on your form?
are you writing the value of the function into a textbox on your form?
 
H

HD87glide

Hello and thanks for the reply!

Yes, the field is a textbox on the form, and yes, the control source =
'=LowestValue(1,10,100,1000,10000)'. I am not sure if I follow you about the
value of the function. Should the control source have something else?
Thanks,
Jeff
 
M

M.L. Sco Scofield

There is a missing line in the function. After the processing is done, you
have to get the value out of the function.

Next intLoop

LowestValue = dtmLowestValue ' Missing line

End Function

BTW, it looks like this function might have started life finding the lowest
date in a series of dates.

If dtmLowestValue is really an integer as the Dim line indicates, the
correct tag is int. So as not to confuse other people reading your code, all
occurrences of "dtmLowestValue" should be changed to "intLowestValue".

Keep in mind that integers only go to 32767. If you need larger values,
change the Dim line to "Dim lngLowestValue As Long" and all occurrences of
"dtmLowestValue" to "lngLowestValue".

I made a form with six text boxes, five named txtValue1 through txtValue5.

Then I put:

=LowestValue([txtValue1],[txtValue2],[txtValue3],[txtValue4],[txtValue5])

in the control source of the 6th.

This works just find except when the first text box is empty because the
function does not check for a zero length string and/or a null. You need to
either add some null testing or change everything to variants.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
K

Ken Snell [MVP]

Good eyes, Sco! < g >

--

Ken Snell
<MS ACCESS MVP>

M.L. Sco Scofield said:
There is a missing line in the function. After the processing is done, you
have to get the value out of the function.

Next intLoop

LowestValue = dtmLowestValue ' Missing line

End Function

BTW, it looks like this function might have started life finding the
lowest
date in a series of dates.

If dtmLowestValue is really an integer as the Dim line indicates, the
correct tag is int. So as not to confuse other people reading your code,
all
occurrences of "dtmLowestValue" should be changed to "intLowestValue".

Keep in mind that integers only go to 32767. If you need larger values,
change the Dim line to "Dim lngLowestValue As Long" and all occurrences of
"dtmLowestValue" to "lngLowestValue".

I made a form with six text boxes, five named txtValue1 through txtValue5.

Then I put:

=LowestValue([txtValue1],[txtValue2],[txtValue3],[txtValue4],[txtValue5])

in the control source of the 6th.

This works just find except when the first text box is empty because the
function does not check for a zero length string and/or a null. You need
to
either add some null testing or change everything to variants.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


HD87glide said:
Hello Group! I am looking for some help. In researching a problem I found a
post that had some code for a function. When I built the function and
modified the code to produce what I needed and use the 'debug' feature it
seems to work, but the values are not produced on my form.

The function should take a group of numbers and return the smallest
number
in the array. An example would be:
LowestValue (1, 10, 100, 1000, 10000)

Function LowestValue(ParamArray LowArray() As Variant)

Dim dtmLowestValue As Integer
Dim intLoop As Integer

dtmLowestValue = LowArray(0)
For intLoop = 1 To UBound(LowArray())
If IsNull(LowArray(intLoop)) = False Then
If LowArray(intLoop) < dtmLowestValue Then
dtmLowestValue = LowArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function


The function seems to run as I can walk through it with debug and can see
the values change. How do I get the final value to show up on my form? The
input numbers for the function will be calculated from other fields on
the
form so I cannot use a table to produce the results.

Thanks for your help!
Jeff
Click to expand...
Click to expand...
 
M

M.L. Sco Scofield

Thank you Ken.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


Ken Snell said:
Good eyes, Sco! < g >

--

Ken Snell
<MS ACCESS MVP>

M.L. Sco Scofield said:
There is a missing line in the function. After the processing is done, you
have to get the value out of the function.

Next intLoop

LowestValue = dtmLowestValue ' Missing line

End Function

BTW, it looks like this function might have started life finding the
lowest
date in a series of dates.

If dtmLowestValue is really an integer as the Dim line indicates, the
correct tag is int. So as not to confuse other people reading your code,
all
occurrences of "dtmLowestValue" should be changed to "intLowestValue".

Keep in mind that integers only go to 32767. If you need larger values,
change the Dim line to "Dim lngLowestValue As Long" and all occurrences of
"dtmLowestValue" to "lngLowestValue".

I made a form with six text boxes, five named txtValue1 through txtValue5.

Then I put:

=LowestValue([txtValue1],[txtValue2],[txtValue3],[txtValue4],[txtValue5])

in the control source of the 6th.

This works just find except when the first text box is empty because the
function does not check for a zero length string and/or a null. You need
to
either add some null testing or change everything to variants.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


HD87glide said:
Hello Group! I am looking for some help. In researching a problem I found a
post that had some code for a function. When I built the function and
modified the code to produce what I needed and use the 'debug' feature it
seems to work, but the values are not produced on my form.

The function should take a group of numbers and return the smallest
number
in the array. An example would be:
LowestValue (1, 10, 100, 1000, 10000)

Function LowestValue(ParamArray LowArray() As Variant)

Dim dtmLowestValue As Integer
Dim intLoop As Integer

dtmLowestValue = LowArray(0)
For intLoop = 1 To UBound(LowArray())
If IsNull(LowArray(intLoop)) = False Then
If LowArray(intLoop) < dtmLowestValue Then
dtmLowestValue = LowArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function


The function seems to run as I can walk through it with debug and can see
the values change. How do I get the final value to show up on my form? The
input numbers for the function will be calculated from other fields on
the
form so I cannot use a table to produce the results.

Thanks for your help!
Jeff
Click to expand...
Click to expand...
Click to expand...
 
Top