x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 360

# 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.
0
smolbeck367
• 3
1 Solution

Commented:
Me("sTot" + Format(intX)) = IIF(lngRgColumnTotal(intX) - 40 < 0, 0, lngRgColumnTotal(intX)-40)
0

Commented:
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
``````
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.
0

Commented:
``````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
``````
0

Author Commented:
fyed - your first post worked beautifully. Thank for your quick and very useful response.
0

Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.