Probably more than you wanted to know, but below is how your query was integrated into the current app. The period date bracketing form is common to several reports of which the first two are under development. The beginning and ending dates of the report periods are captured and saved in global variables, as opposed to accessing them from the form's controls as you did in your post, as they are used a few times elsewhere in the app when the form has been closed.
Here is what the user sees:
Here's the code behind the period bracketing form: (See "Sub Categories")
Code:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Set the defaults for the beginning and ending of the report period.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Me.tbBegDate = DMin("tdate", "tblregister") 'Get the oldest date in the register as default starting
curRptPerBeg = Me.tbBegDate
Me.tbEndDate = Date 'Set ending date to today
curRptPerEnd = Me.tbEndDate
End Sub
Private Sub cmdOK_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Setting period dates for which report?
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Select Case Me.OpenArgs
Case "Activity"
Call Activity
Case "Categories"
Call Categories
End Select
End Sub
Private Sub Activity()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Report of receipts and disbursements for the specified period. Both are displayed as sub-reports, so we
' modify the RecordSource queries per the specified period.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strQReceipts As String
Dim strQExpences As String
Dim strQLastReconcile As String
strQReceipts = "SELECT TDate, Description, Credit FROM tblRegister WHERE ((([TDate])>=#" & curRptPerBeg & "# And ([TDate])<= #" & curRptPerEnd & _
"#) AND (([Credit])>0) AND (([TTypeID])<>3)) ORDER BY TDate;"
Call ModifyQuery("QReceipts", strQReceipts)
strQExpences = "SELECT TDate, Description, Debit FROM tblRegister WHERE (((TDate) >= #" & curRptPerBeg & "#)" & _
" And ((Debit) > 0) And (IsNumeric(TType)) ) ORDER BY TDate;"
Call ModifyQuery("QExpences", strQExpences)
strQLastReconcile = "SELECT TDate, Description, Balance FROM tblRegister WHERE (TRecID=" & GetTranID & ");"
Call ModifyQuery("QLastReconcile", strQLastReconcile)
DoCmd.OpenReport "rptTresReport", acViewPreview
End Sub
Private Sub Categories()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Set the category report RecordSourc according to a beginning and ending date. curRptPerBeg and curRptPerEnd
' are global, as they are used in report headers and also needed and saved elsewhere.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strQCategories As String
strQCategories = "SELECT CategoryName, Sum(Debit) AS SumOfDebit FROM tblCategories " & _
"INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID " & _
"WHERE (((TDate) >= #" & curRptPerBeg & "# And (TDate) <= #" & curRptPerEnd & "#)) GROUP BY CategoryName, tblRegister.CatID " & _
"HAVING (((tblRegister.CatID)>0));"
Call ModifyQuery("QCatTotals", strQCategories)
DoCmd.OpenReport "rptByCategory", acViewPreview
End Sub
Private Sub tbBegDate_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' To launch Allen Browne's pop-up calendar adjacent to the date control that's located
' in the footer section, we take the overall height minus the height of the footer
' minus half the height of the calendar as our "top of calendar" coordinate.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
intCalTop = Me.Form.WindowTop + Me.cmdOk.top
intCalLeft = Me.Form.WindowLeft + Me.cmdOk.left
dummy = CalendarFor([tbBegDate], "Report Starting Date")
If Not IsNull(Me.tbBegDate) Then
Me.cmdOk.Visible = True
Me.cmdOk.SetFocus
curRptPerBeg = tbBegDate
End If
End Sub
Private Sub tbPerEnd_Click()
intCalTop = Me.Form.WindowTop + Me.cmdOk.top
intCalLeft = Me.Form.WindowLeft + Me.cmdOk.left
dummy = CalendarFor([tbPerEnd], "Report Period End Date")
If Not IsNull(Mee) Then
curRptPerEnd = tbPerEnd
End If
End Sub
Public Sub ModifyQuery(strName As String, strNewSQL As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Modify the specified (strName) query that's being adjusted in accordance with the specified period.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
'Modify the QueryDef's properties
dbs.QueryDefs(strName).SQL = strNewSQL
Set dbs = Nothing
End Sub
(You might notice I use Allen's calendar. That's because the front-end mdb/mde still needs updating in the A2003 environment.)
And finally what the user sees in the report (under development):
BTW, I seem to remember in years past that Steven Lebans wrote some code to prefix and append leader dots to text box controls strings. Was that facility or property ever advanced in subsequent releases of Access?