Query by form

[Stuart Grant]

I have a table of Transactions which have a date TranDate and then
Description, Credit Account, Debit Accounts and Amount. I want to filter
using a form on which there is a text box for the Account number and a
frame with Option Buttons for the four quarters. I can get the account
number working fine. It will filter the table for all transactions either
debited or credited to that account but I do not know how to link the
Quarter criteria to the Option Button on the form.
Stuart

 

[Allen Browne]

Assuming you want the current year, the date for the first quarter would be:
Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),3,31)

You can apply that to the other quarters.

 

[Stuart Grant]

Assuming you want the current year, the date for the first quarter would
be:
Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),3,31)

You can apply that to the other quarters.

Thanks for prompt reply but I don't see how I incorporate that in a form. I
want to fill in one number in a text box, click one option button for the
quarter, then click OK which runs a macro which simply runs the query. How
do you get the query to pick up the quarter from the form?. It is all
current year.
Stuart

 

[Allen Browne]

Remove the criteria from your query. Instead, we will filter the form.

1. Add a text box to your for, and give it these properties:
Name txtQuarter
Control Source {leave this blank}
Format Fixed Number
Decimal Places 0
Validation Rule Between 1 and 4
After Update [Event Procedure]

2. Click the Build button (...) beside the AfterUpdate property.
Access opens a code window.
Set up the event procedure to look like this:

Private Sub txtQuarter_AfterUpdate()
Dim dtStart As Date
Dim dtEnded As Date
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.txtQuarter) Then 'Show all.
Me.FilterOn = False
Else
dtStart = DateSerial(Year(Date), 3 * (Me.txtQuarter - 1) + 1, 1)
dtEnded = DateAdd("m", 3, dtStart) 'First of following quarter.
strWhere = "([MyDate] >= " & Format(dtStart, strcJetDate) & _
") AND ([MyDate] < " & Format(dtEnded, strcJetDate) & ")"
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Notes:
1. Substitute the name of your date field for MyDate.
2. The code uses "less than the first of the next quarter", so as to include
the last day of the quarter even if your date/time field has a time
component.
3. The explicit date formatting avoids errors relating to international
dates.
4. The Debug.Print line is just for debugging.