Results 1 to 7 of 7

Thread: Min (2000)

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Please advice.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Please advice,

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •