Calculating information based on filtering a split datasheet

[JustLearning]

Question 1: I'm trying to find the value of a specific cell in a split datasheet form, I have the column I need and the value of another item in the row, how would I go about this? (Basically, I have a column that lists the number of requests, and a number that puts a value to those requests. I want to figure out the highest number of the requests, and then figure out the value of that request. The highest request isn't always the latest one, so I can't use max.)

Question 2: I'm trying to add up some numbers based on values in a column. This is a kind of pseudo-code I came up with.

'Dim x As Integer
'Dim money as Integer
'money = 0
'x = Rows.Count


'For loop_count = 1 to x
'If Me.Change_Request.x > 0 Then
'money = money + Me.Change_Amount.Value
'Else


'money = money + Me.Total_Amount.Value
'Next loop_count


'Me.Filtered_Amount = money

Can anyone help me with the actual VBA? xD

Thanks guys!

[June7]

Question 1: You want to return the value of a field for the record that has the maximum number of requests? This can be tricky.

DLookup("ValueField","tablename","NumberRequestsFi eld=" & DMax("NumberRequestsField","tablename","filter clause if needed"))

Otherwise, maybe use RecordsetClone. Something like:

Dim x As Double, y As Double
With Me.RecordsetClone
While Not .EOF
If !NumberRequestsField > x Then y = !ValueField
x = !NumberRequestsField
.MoveNext
Wend
End With

Question 2: Don't understand. Why don't you do a GROUP BY query or a report with Grouping & Sorting and aggregate calcs?

[JustLearning]

I attempted to use the recordsetclone, but could not fully figure it out. What do the ! and . mean? Should they represent the table? If so, does the default name of the split datasheet go to Sheet1, or the name of the form itself?

Dim x As Double, y As Double


With Me.RecordsetClone
While Not .EOF
If Me.Change_Request__ > x Then y = Me.Total_Amount
x = Me.Change_Request__
Me.Next
Wend
End With


Also, would it be possible for me to use DSum for my second question?

[June7]

In the example, I use the bang (!) for referring to fields of recordset. I use the dot (.) for referring to controls, methods, properties. The dot will provoke intellisense popup tips. Your code syntax does not follow my example.

DSum could probably be used.

[JustLearning]

I decided on using a really roundabout way for part 2 using DSum, but can't seem to get it to be the correct amount.

Dim AmountWR As Integer
Dim AmountCR1 As Integer
Dim AmountCR2 As Integer
Dim AmountCR3 As Integer
Dim AmountCR4 As Integer
Dim AmountCR5 As Integer
Dim AmountCR6 As Integer
Dim FilteredAmount As Integer


AmountWR = DSum("Total Work Order Amount", "Sheet1", Me.Change_Request__ = 0)
AmountCR1 = DSum("Year 1 Incremental Change", "Sheet1", Me.Change_Request__ = 1)
AmountCR2 = DSum("Year 2 Incremental Change", "Sheet1", Me.Change_Request__ = 2)
AmountCR3 = DSum("Year 3 Incremental Change", "Sheet1", Me.Change_Request__ = 3)
AmountCR4 = DSum("Year 4 Incremental Change", "Sheet1", Me.Change_Request__ = 4)
AmountCR5 = DSum("Year 5 Incremental Change", "Sheet1", Me.Change_Request__ = 5)
AmountCR6 = DSum("Year 6 Incremental Change", "Sheet1", Me.Change_Request__ = 6)


FilteredAmount = AmountWR + AmountCR1 + AmountCR2 + AmountCR3 + AmountCR4 + AmountCR5 + AmountCR6


Me.Filtered.Text = FilteredAmount

Thanks for all the help btw

[June7]

Why are you referencing control on form in the WHERE criteria? This must be a field in the data source. Also, when names have spaces, special characters, punctuation (underscore is exception) must enclose in [].

AmountWR = DSum("[Total Work Order Amount]", "Sheet1", "fieldname = 0")

[JustLearning]

Ah, I see what you're saying, does this look better?

Dim AmountWR As Integer
Dim AmountCR1 As Integer
Dim AmountCR2 As Integer
Dim AmountCR3 As Integer
Dim AmountCR4 As Integer
Dim AmountCR5 As Integer
Dim AmountCR6 As Integer
Dim FilteredAmount As Integer


AmountWR = DSum("[Total Work Order Amount]", "Sheet1", "[Change Request #] = 0")
AmountCR1 = DSum("[Year 1 Incremental Change]", "Sheet1", "[Change Request #] = 1")
AmountCR2 = DSum("[Year 2 Incremental Change]", "Sheet1", "[Change Request #] = 2")
AmountCR3 = DSum("[Year 3 Incremental Change]", "Sheet1", "[Change Request #] = 3")
AmountCR4 = DSum("[Year 4 Incremental Change]", "Sheet1", "[Change Request #] = 4")
AmountCR5 = DSum("[Year 5 Incremental Change]", "Sheet1", "[Change Request #] = 5")
AmountCR6 = DSum("[Year 6 Incremental Change]", "Sheet1", "[Change Request #] = 6")


FilteredAmount = AmountWR + AmountCR1 + AmountCR2 + AmountCR3 + AmountCR4 + AmountCR5 + AmountCR6


Me.Filtered.Text = FilteredAmount

[June7]

Should work.

Still advise against the spaces and special characters in names.

[JustLearning]

Yeah, I realized that afterwards xD

I put in the code, got a run-time error 6 Overflow on the AmountWR = DSum("[Total Work Order Amount]", "Sheet1", "[Change Request #] = 0") line. When I comment out the line, I get a runtime error 94 Invalid use of Null, would a debug help me figure out what's going on?

[June7]

You explicitely declare the variables as Integer and the sum returned must be too large for that type. Use Double type.

The variables can't hold a Null (only Variant type can hold Null). If no records meet the criteria of the DSum then Null is returned.

Also, if there is possibility [Change Request #] could be Null in the data source, the expression will fail because can't test for Null = something.

Need to handle possible Null. Review http://allenbrowne.com/casu-12.html

Text is the wrong property for a textbox control. The default property of data controls in Access/VBA is Value so don't even state the property:

Me.Filtered = AmountWR + AmountCR1 + AmountCR2 + AmountCR3 + AmountCR4 + AmountCR5 + AmountCR6

[JustLearning]

Thank you so much for all your help! That worked perfectly!