1. ## Min (2000)

Edited by HansV to prevent <!t>[b]<!/t> from being interpreted as the 'bold' tag

Hi,

I am working on a weekly summary report. There are four fields(Name: a,b,c,d) on the report with each department work orders number by category. I would like create another filed (e) to shows the min number of those four fields (a to d). I tried to use the following code in the Expression Builder of the report:

=Min([a],<!t>[b],[c],[d])

And it's doesn't work. So if anybody can help, I really appreciate.

Regards

2. ## Re: Min (2000)

Min works only across records in Access, not across fields. You'll find a field aggregate function FMin in <post:=579,888>post 579,888</post:>. Copy this function into a standard module. You can then use

=FMin([a],<!t>[b],[c],[d])

in the control source of your text box.

3. ## Re: Min (2000)

Thanks, Hans.

But the four fields (a,b,c,d) are all numbers. However the FMin function are dates not number. I tried the revise the function and it doesn't works. Please see below:

Public Function FMin(ParamArray s() As Variant) As Variant
' Returns the minimum date in the list supplied
Dim dtmMin As Integer
Dim i As Integer
Dim varValue As Variant
Dim intCount As Integer
For i = LBound(s) To UBound(s)
varValue = s(i)
If IsDate(varValue) Then
If CDate(varValue) < dtmMin Then
dtmMin = CDate(varValue)
End If
intCount = intCount + 1
End If
Next i
If intCount = 0 Then
FMin = Null
Else
FMin = dtmMin
End If
End Function

Thanks

4. ## Re: Min (2000)

Try this version

Public Function FMin(ParamArray s() As Variant) As Variant
' Returns the minimum number in the list supplied
Dim lngMin As Long
Dim i As Integer
Dim varValue As Variant
Dim intCount As Integer
' Dummy initial value
lngMin = 2147483647#
For i = LBound(s) To UBound(s)
varValue = s(i)
If IsNumeric(varValue) Then
If CLng(varValue) < lngMin Then
lngMin = CLng(varValue)
End If
intCount = intCount + 1
End If
Next i
If intCount = 0 Then
FMin = Null
Else
FMin = lngMin
End If
End Function

5. ## Re: Min (2000)

Thanks, Hans.

I tried the code and I got the Compile Error: Syatax error message on the below line:

If CLng(varValue) < lngMin Then

Regards

6. ## Re: Min (2000)

I have tested the code - it doesn't contain syntax errors. To make sure you have the correct version, I have attached a text file containing the function. Download it to your PC, then copy and paste the code into your database (replacing the version you have now). Do you still get a syntax error?

7. ## Re: Min (2000)

Yes. Now it's works.

Thank you so much. It's really big help.

Thanks

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•