Improve company productivity with a Business Account.Sign Up

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

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
Asked:
smolbeck367
  • 3
1 Solution
 
Dale FyeCommented:
Me("sTot" + Format(intX)) = IIF(lngRgColumnTotal(intX) - 40 < 0, 0, lngRgColumnTotal(intX)-40)
0
 
Dale FyeCommented:
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.
0
 
IrogSintaCommented:
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

0
 
smolbeck367Author Commented:
fyed - your first post worked beautifully. Thank for your quick and very useful response.
0
 
Dale FyeCommented:
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now