Multiple Variables in Query

[BrokenBiker]

I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.

For this particular report I've broken down the AFSCs down to three different formats. They are as follows:

2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)

The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.

Here's the SQL for it.

SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);


Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.

 

[BrokenBiker]

A list of the AFSCs and their appropriate career field groups


•2A5x1x
o 2A531B
o 2A551J
o 2A571
•2A5x3x
o 2A533A
o 2A535A
o 2A537A
•2A5x3x
o 2A533B
o 2A535B
o 2A537B
•2A5x3x
o 2A533C
o 2A535C
o 2A537C
•2A6x1x
o 2A631B
o 2A651B
o 2A671B
•2A6x4
o 2A634
o 2A654
o 2A674
•2A6x5
o 2A635
o 2A655
o 2A675
•2A6x6
o 2A636
o 2A656
o 2A676
•2A7x1
o 2A731
o 2A751
o 2A771
•2A7x2
o 2A732
o 2A752
o 2A772
•2A7x3
o 2A733
o 2A753
o 2A773
•2A7x4
o 2A734
o 2A754
o 2A774

 

[BrokenBiker]

Here's the attachment. Open the main report.

 

[BrokenBiker]

Hmmm....I was hoping it was a no-brainer and guess what? I feel kinda dumb now.

I didn't know that when entering the search criteria using the [Enter your infor here] brackets that it would take wildcards '*'. I ended up basing it off a form where you can choose the AFSC from a drop down list or enter the basics of the AFSC w/ an asterik or two and it works fine. For instance 2A5*1* brings up all the "APG" career field AFSC (2A531B, 2A551J, & 2A571).