Web Development

Ask something
Question Locked

Access07 filter report from multiple combo boxes

0 helpful
7 replies

by greg

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

7 replies

by jerry~beans&bytes

'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'

by greg

******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 [Time] 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]))
WITH OWNERACCESS OPTION;
****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_AOSReport_Click:
Exit Sub

Err_AOSReport_Click:
MsgBox Err.Description
Resume Exit_AOSReport_Click

End Sub

If there is any other info you need

by jerry~beans&bytes

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]))"

by greg

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.

by jerry~beans&bytes

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.

by greg

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.

This discussion is closed to new replies.