Web Development



Access07 filter report from multiple combo boxes

By greg ·
I have a form that has an unbound combo box that retreives all the filtered data from the choice I made then I click the report button and all works well. Problem, I need a second combo box that I can use on the same form to choose by date (within last 30 days, 90 days, etc.) that would then filter even more. I'm new to VBA so be kind. How can I filter those different date ranges in the same combo box or on the same query? I can put in the criteria field Between (Now()-30) And Now() and this works but not for 90 or 180 days.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

you using SQL in the query to filter, or another method?

by jerry~Beans&Bytes In reply to Access07 filter report fr ...

'cause if you are writing the query in SQL, you could just use a WHERE([target field] Between [Textbox1] And [Textbox2])
Textbox 1 & 2 can be in any handy table.
doesn't restrict you to multiples of 30 days, tho'

Collapse -

Here is the SQL of the query and more!

by greg In reply to you using SQL in the quer ...

******Begin SQL*****
SELECT Names.FirstName, Names.LastName, Time.Area, Last(Time.Date) AS LastOfDate
FROM (Contact_Interest INNER JOIN [Names] ON Contact_Interest.ContactID = Names.ContactID) INNER JOIN ON Contact_Interest.ContactID = Time.ContactID
GROUP BY Names.FirstName, Names.LastName, Time.Area, Contact_Interest.ContactID, Contact_Interest.CIBS, Contact_Interest.CIC, Contact_Interest.CIK, Contact_Interest.CITS, Contact_Interest.CISE, Contact_Interest.CICC, Contact_Interest.Mentoring, Contact_Interest.aUX, Contact_Interest.CIADMIN
HAVING (((Time.Area)=[Forms].[Form2].[Combo6]))
****End SQL*****
The only area that I need to filter from my first Cbo is the Time.Area AND another Cbo to choose Time.date. When the user chooses 30 days in the combo it returns only those from the last 30 days and so on. Do I need to write a macro, place vba somewhere else?

The OnClick event for the button on the form is:

Private Sub AOSReport_Click()
On Error GoTo Err_AOSReport_Click

Dim stDocName As String

stDocName = "Area Of Service"
DoCmd.OpenReport stDocName, acPreview

Exit Sub

MsgBox Err.Description
Resume Exit_AOSReport_Click

End Sub

If there is any other info you need

Collapse -

no experience with "HAVING", but

by jerry~Beans&Bytes In reply to Here is the SQL of the qu ...

is there a reason why you can't create another Cbo ([Forms].[Form2].[Combo7]), and modify your HAVING statement to:
"HAVING ((((Time.Area)=[Forms].[Form2].[Combo6])) AND ((Time.Date)=[Forms].[Form2].[Combo7]))" ??
it works with the WHERE command (i'm in Access 02), so if HAVING doesn't allow you to add the AND statement, you might try using WHERE, and see if A'07 will accept it.

(edit-some more coffee later) actually, your statement might end up something like "WHERE(HAVING((Time.Area)=[Forms].[Form2].[Combo6]) AND HAVING((Time.Date)=[Forms].[Form2].[Combo7]))"

Collapse -

Undefined function HAVING

by greg In reply to no experience with "HAVIN ...

WHERE(HAVING((Time.Area)=[Forms].[Form2].[Combo6]) AND HAVING((Time.Date)=[Forms].[Form2].[Combo7]))... My second combo box oddly is Combo11 it also is unbound. How do I pass the criteria (Between (Now()-180) And Now())with changing the -?days depending on choice of combo box.

Collapse -

me, i'm a KISS-er, not a coder

by jerry~Beans&Bytes In reply to Undefined function HAVING

you could do what i usually do-go back to the basics. change the combo boxes to text boxes (format as Dates), then use a BETWEEN, i.e. "WHERE (([Table1].[Date] Between [Forms].[ReportDatesForm].[Text1] And [Forms].[ReportDatesForm].[Text2]))"

simple, flexible, intuitive. but it doesn't restrict the date range to multiples of 30-day intervals; how important is this restriction to you?

if you are comfortable with VBA, you could define a variable and value it, then use your HAVING on the variable, i guess.
i'm just an accountant who learned SQL so i could do custom reports. the details of such a thing are a little over my head, so i can't be much help if you take that road.

Collapse -

Back to the basics!

by greg In reply to me, i'm a KISS-er, not a ...

Well, I don't know VBA, SQL or any of those other fancy acronyms and getting these records by specific dates is important to the user. They have to track differnt types of providers and the times they volunteered within certain periods. I thought of doing something like "by quarter" but don't even know how to begin doing that. Thanks for all your help I guess I'll have to keep searching to find the right solution.

Related Discussions

Related Forums