[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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