Search data by Due Dates and Locations for 3 X 4 matrix

[Belinda]

I’d like to say Thank You in advance for anyone who will take this call for
help; I really appreciate what you all do here.
--My dilemma is that I have written queries in ACCESS 2003 to find upcoming
“dueDates†within 30, 45, and 60 days. They all work fine. Now I would like
to further filter these dates to a specific location, (there are currently 4
different buildings in all) to identify equipment that is in need of an
inspection.
What is the easiest and fastest way to do this? (I do not want to have to
make 12+ different queries like 30 day for bldg. A, and a 45 day for bldg. B,
and a 60 day for bldg C…) I also need to print reports for such. I do have
reports but only for the dates, not the specific locations for the dates.
I do not have a lot of experience with ACCESS of VBA, but I’m willing to
learn! I was trying to research how to do this with an option group but I
haven’t been able to wrap my brain around it. Here are SQL statements from my
queries:

Example of 30Day SQL:
SELECT eqDates.ID, location.location, eqDates.equipId,
description.description, make.make, make.model, eqDates.dueDate,
eqDates.daysTilNext, eqDates.notes, eqDates.audit
FROM ((eqDates LEFT JOIN (base LEFT JOIN location ON base.location_ID =
location.ID) ON eqDates.base_ID = base.ID) LEFT JOIN poc ON eqDates.poc_ID =
poc.ID) LEFT JOIN (description LEFT JOIN make ON description.make_ID =
make.ID) ON eqDates.description_ID = description.ID
WHERE (((eqDates.daysTilNext)<=31))
ORDER BY location.location;

Example of Location/bldg. SQL:
SELECT location.location
FROM location
WHERE (((location.location) Like "A*" Or (location.location) Like "DAF*"))
ORDER BY location.location;

The only difference in my other queries are the number of days and bldg.
locations. Thank You!

 

[Belinda]

Update: I just learned how to combine the queries into one, and it worked but
that's not the effect that I wanted. I would like the user to be able to
select the # of days, 30, 45, or 60, and the location/building (separately)
by way of list or option group. Thanks.

 

[ThriftyFinanceGirl]

Belinda,

Are you using a form and allowing the user to choose the building? If so,
then you can just reference that control in your query....

 

[Belinda]

Hi ThriftyFinanceGirl, I have explored with a form but it was not coming
together for me on how to get the user input as I don't know much VBA. Could
you explain how to capture the user input from a list or option button? Thank
you!

 

[Dale_Fye via AccessMonster.com]

Belinda,

Personally, I prefer to use a form to initiate this query. Depending on what
combinations you want of days and locations, I'd probably put a combo box
(cbo_Days) with values of 30, 45, and 60 and a default value of one of these.
I'd then add a multi-select listbox for the group of buildings, so the user
can select one or more buildings.

Then, use a command button to open the query or open the report that is based
upon the query.

The command buttons code might look something like:

Private Sub cmd_UpdateQuery

Dim strSQL as string
Dim strLocations as string
Dim varItem as Variant

strSQL = "SELECT eqDates.ID, location.location, eqDates.equipId, " _
& "description.description, make.make, make.model, " _
& "eqDates.dueDate, eqDates.daysTilNext, eqDates.notes, " _
& "eqDates.audit " _
& "FROM ((eqDates LEFT JOIN (base LEFT JOIN location " _
& "ON base.location_ID = location.ID) " _
& "ON eqDates.base_ID = base.ID) " _
& "LEFT JOIN poc ON eqDates.poc_ID = poc.ID) " _
& "LEFT JOIN (description LEFT JOIN make " _
& "ON description.make_ID = make.ID) " _
& "ON eqDates.description_ID = description.ID " _
& "WHERE eqDates.daysTilNext <=" & me.cbo_Dates

strLocation = ""
for each varItem IN me.lst_Locations.ItemsSelected
strLocation = strLocation & " OR Location.[Location] Like '" _
& me.lst_Locations.column(1,
varItem) & "*'"
Next
'Remove the first 'OR'
if len(strLocation) > 0 then
strLocation = mid(strLocation, 5)
strSQL = strSQL & " AND (" & strLocation & ")"
end if

strSQL = strSQL & " ORDER BY location.location"

currentdb.querydefs("YourQueryName").SQL = strSQL
Docmd.openReport "YourReportName"

End if

HTH
Dale