asked on

Default value in cross tab report based on For statement

Hi, I have vba statement that adds an OT row to a cross tab report in access. The statement looks like this:
For intX = 2 To intColumnCount
Me("sTot" + Format(intX)) = lngRgColumnTotal(intX) - 40
Next intX

This has both over and undertime answers. What I would like to do is only show the overtime results and set the negative results to 0 on the report. I have tried to use an if statement but I have been unsuccessful.

Thanks for the help.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Another method would be to use the following function:

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

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Open in new window

Copy the function and save it in a standard code module, then use the following expression in your loop:

Me("sTot" + Format(intX)) = fnMax(0, lngRgColumnTotal(intX) - 40)

fnMax will accept an array of numbers, strings, or dates and find the maximum value in that array.

IrogSinta

How about this:

Dim lngTotal As Long
For intX = 2 To intColumnCount
    lngTotal = lngRgColumnTotal(intX) - 40
    If lngTotal < 0 Then lngTotal = 0
    Me("sTot" + Format(intX)) = lngTotal
Next intX

Open in new window

smolbeck367

ASKER

fyed - your first post worked beautifully. Thank for your quick and very useful response.

Dale Fye

Glad to help. I generally prefer to use the function, as I can pass as many arguments as I want, and they can be text or date as well. I have a similar fnMin() function, both are part of my library module that I install in almost every database I create.