DLookup and DMax on a split form filtered sheet

[JustLearning]

Hi all, I was wondering if it would be possible to call DMax and DLookup to find the information on a filtered sheet instead of on the source table? I'm using a split form and need it to look through the information on the split form table, not the source table.

[June7]

AFAIK, domain aggregate functions must reference an Access object (table or query) as the source. Include same criteria as is used for the form RecordSouce.

Can use SQL aggregate functions (Sum, Count, Max, Avg) on form/report and that does work on the form/report data.

[JustLearning]

So for something like this:

x = DMax("[Change Request #]", "Sheet1", "")
Me.Text169 = x
y = DLookup("[Total Work Order Amount]", "Sheet1", "[Change Request #] = " & x)
Me.Text171 = y


Dim AmountWR As Double
Dim FilteredAmount As Double
AmountWR = DSum("[Total Work Order Amount]", "Sheet1", "[Change Request #] = 0")
AmountCR = DSum("[Change Request Amount]", "Sheet1", "[Change Request #] > 0")


FilteredAmount = AmountWR + AmountCR
Me.Filtered = FilteredAmount

I should be able to input SQL into the value boxes of the text boxes that I need the information in?

[June7]

Don't really understand your question.

Can use Domain aggregate functions in textbox ControlSource anywhere on the form/report.

SQL aggregate functions would be in textbox ControlSource in header or footer sections of form/report.

[JustLearning]

Sorry, I'm not too familiar with SQL, I'm just wondering where I should put the code. For example, for the max, would I would just click the textbox and select the ControlSource, inputting this SQL code:

SELECT Max([Change Request #].Form) AS TotalChangeRequests
FROM [Form]

[June7]

ControlSource for textbox in header/footer would be:

=Max([Change Request #])


BTW, advise no spaces, special characters, punctuation (underscore is exception) in names. If used must enclose in []. Better would be ChangeRequestNum.

[JustLearning]

Well I need the Max number for the next part, where I used Lookup to find a matching case. Would something like this work?

Dim test1 As Integer
Dim test2 As Double


test1 = "SELECT Max([Change Request #])"
test2 = "SELECT [Total Work Order Amount] WHERE [Change Request #] = test1"

[June7]

No, can't set a control value to an sql statement. All that does is set the textbox value to a string of characters. Could open a recordset and then reference a field of the recordset. That is a little more complicated than using domain aggregate functions to retrieve a single value. Can use domain aggregate functions in ControlSource property or in VBA code as you show in post 3. What event would you put that code in?

Or use SQL aggregate functions in texbox ControlSource.

ControlSource for Test1:
=Max([Change Request #])

ControlSource for Test2:
= DLookup("[Total Work Order Amount]", "tablename","[Change Request #]=" & [test1])

Note the concatenation of variable (reference to a control is a variable).

[JustLearning]

I did the ControlSource for Test1, but the ControlSource for Test2 won't work because I need it to read from the filtered table on the form, and you said that that wasn't possible. Is it possible for the filtered table in a form to be saved to another table?

Edit: I tried doing the other thing you said with recordsets, but I'm getting an error

Dim test1 As String
Dim test2 As String
Dim test3 As DAO.Recordset
Dim test4 As DAO.Recordset


test1 = "SELECT Max([Change Request #])"


Set test3 = CurrentDb.OpenRecordset(test1) <--- error here
test2 = "SELECT [Total Work Order Amount] WHERE [Change Request #] = test1"


Set test4 = CurrentDb.OpenRecordset(test2)


Me.Text169 = test3
Me.Text171 = test4

The error is 3061: Too few parameters. Expected 1.

[June7]

Could include additional criteria in the DLookup that is the same as the form's filter criteria.

Why save to another table? Build a query object that has the same filter criteria as the form. Use that query as the data source for the DLookup. That query object could even be referenced as the form's RecordSource.

[JustLearning]

The problem is that the filter is different every time. There are multiple fields that can be filtered, and I need it to work for any combination of filters

Edit: Ok, the parameter error was from not having FROM in the test1 query, but that also has the issue of requiring a table or query

Edit2: Looking over this, I was just wondering if there was any way to save the filtered data from the form into another table, then just use DLookup and DSum on that saved table

Edit3: I DID IT! I don't know if anyone else in the world will have the same problem I did, but here's my solution. I basically made the filter into a query, and just ran DLookup and DSum using the query

Dim qry As QueryDef
Dim strSQL As String

strSQL = "SELECT * FROM Sheet1 WHERE " & Me.Filter

CurrentDb.QueryDefs.Delete "QRY_MYQUERY"
Set qry = CurrentDb.CreateQueryDef("QRY_MYQUERY", strSQL)




Dim x As Integer
Dim y As Double


x = DMax("[Change Request #]", "QRY_MYQUERY", "")


Me.Text169 = x
y = DLookup("[Total Work Order Amount]", "QRY_MYQUERY", "[Change Request #] = " & x)


Me.Text171 = y


Dim AmountWR As Double
Dim AmountCR As Double


Dim FilteredAmount As Double


AmountWR = Nz(DSum("[Total Work Order Amount]", "QRY_MYQUERY", "[Change Request #] = 0"), 0)
AmountCR = Nz(DSum("[Change Request Amount]", "QRY_MYQUERY", "[Change Request #] > 0"), 0)


FilteredAmount = AmountWR + AmountCR


Me.Filtered = FilteredAmount

[June7]

Review this tutorial for one method to parameterize a query and allow for any combination of criteria. http://datapigtechnologies.com/flash...earchform.html

[JustLearning]

Awesome, thanks for all your help so far June7!