Splitting up a Query by a single field

[dfaulkner1]

Hi, I am trying to split my query using VBA, I have got the following code but I'm just struggling to split the query up by the ledger field, would anybody be able to see what needs adding to the below script for it to do it

Private Sub Command0_click()

'Set record set from Ledger
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT Ledger FROM Ledger_No")
atick = rs!Ledger
'Start looping through all ledgers
While Not (rs.EOF)
While atick

strPathNm = "\\buzz2\Office on Buzz2\Newonlineusers\"
ledgernm = rs!Ledger
strFileNm = strPathNm & ledgernm & "New Online Users" & ".xlsx"

'Split the Query by Ledger
DoCmd.OpenQuery "Newly_Created_Accounts", acViewNormal
DoCmd.OutputTo acOutputQuery, "Newly_created_accounts", acFormatXLSX, "strFileNm"

DoCmd.Close acQuery, "Newly_created_accounts"
Wend
rs.MoveNext



Wend
End Sub

Thanks
Dan

[ranman256]

you wouldnt via VBA
you split a query using different queries.

Q1 to ledger,
Q2 to other

[June7]

This is the 3rd thread on this topic by this poster.

Options:

1. multiple queries or maybe one query with dynamic parameters but not sure those would work with the export wizard

2. Excel automation as recommend in the first thread https://www.accessforums.net/access/...ets-53923.html

3. export filtered report

Private Sub Command0_click()
Dim strPathFile As String
'Set recordset from Ledger
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT Ledger FROM Ledger_No")
'Start looping through all ledgers
While Not rs.EOF
strPathFile = "\\buzz2\Office on Buzz2\Newonlineusers\" & rs!Ledger & "New Online Users" & ".xlsx"
DoCmd.OpenReport "LedgerExport", acViewPreview, , "Ledger=" & rs!Ledger
DoCmd.OutputTo acOutputReport, , acFormatXLSX, strPathFile
DoCmd.Close acReport, "LedgerExport", acSaveNo
rs.MoveNext
Wend
End Sub

4. VBA code that modifies query object with QueryDefs

The other thread https://www.accessforums.net/access/...ode-54031.html

[orange]

Thanks june.

I have not seen this "Split the Query" term before. Seems OP would get more interest by telling readers the issue in business terms.
Congrats to ranman for interpreting the post.