Change a query by selection 1

[MICKI0220]

I have a query that filters job numbers based on the criteria in the query. I have created a form that gives them a choice of what to assign to that criteria in addition to choose the date range that they want. It would work fine if I didn't have a long list of exclusions that have to remain no matter what their choice is. How can I add my choice criteria to the query without altering the exclusions already in there. For example, all the exclusions are the months of a year, that is twelve exclusions that need to stay, then the user gets to decide if he/she wants to see all the rest of the jobs or just those that start with E or S.

Thank in advance.

Micki

 

[dhookom]

Can you provide any information on table and field names? How about your SQL view? Maybe form and control names?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

here is the sql

SELECT DISTINCTROW JobMaster.JobNo, [Job Name - Master Reference] & [Hours Notes] AS HoursReportJobName, [tbl Subcontractor Schedule].[Hours Notes], JobMaster.[Job Name - Master Reference], JobMaster.[EQ ZONE #], JobMaster.[SHOP HOURS STD], JobMaster.[HIGH WATER LEVEL FT], JobMaster.[FIELD HOURS STD], JobMaster.[EST'D WT], JobMaster.[FINAL COMP DATE], JobMaster.[TANK STYLE], JobMaster.[TANK CAPACITY], JobMaster.[TANK DIAMETER], JobMaster.[TANK # OF BAYS], JobMaster.[ACTUAL SHOP HOURS], [tbl Subcontractor Schedule].[Riverport Hours], [Riverport Hours]+[ACTUAL SHOP HOURS]+[ACTUAL SHOP HOURS NEWNAN] AS [TOTAL SHOP HOURS], JobMaster.[ACTUAL FIELD HOURS], [tbl Subcontractor Schedule].[Date Shop Complete (YY/MM)], JobMaster.[HIGH WATER LEVEL IN], [SHOP HOURS STD]-[TOTAL SHOP HOURS] AS [Variance Shop Hours], [FIELD HOURS STD]-[ACTUAL FIELD HOURS] AS [Variance Field Hours], [tbl Subcontractor Schedule].Month, [tbl Subcontractor Schedule].[Riverport Hours], [tbl Subcontractor Schedule].[Hours Saved Plant Prior Year], JobMaster.[ACTUAL READY TO SHIP DATE], [tbl Percent Hours Saved YTD Last Year].[PERCENT HOURS SAVED YTD LAST YEAR], JobMaster.[SHOP HOURS STD-L], JobMaster.[SHOP HOURS STD-N], JobMaster.[ACTUAL SHOP HOURS NEWNAN], JobMaster.[L / N]
FROM [tbl Percent Hours Saved YTD Last Year], JobMaster INNER JOIN [tbl Subcontractor Schedule] ON JobMaster.JobNo = [tbl Subcontractor Schedule].JobNo
WHERE (((JobMaster.JobNo) Like "W*" Or (JobMaster.JobNo) Like "P*" Or (JobMaster.JobNo) Like "I*" Or (JobMaster.JobNo) Like "E*" Or (JobMaster.JobNo) Like "S*" Or (JobMaster.JobNo) Like "T*") AND ((JobMaster.[ACTUAL READY TO SHIP DATE]) Between [Forms]![FRMHOURSRANGE]![TXTBEGDATE] And [Forms]![FRMHOURSRANGE]![TXTENDDATE]))
ORDER BY JobMaster.JobNo DESC;


The area I am changing by a drop down box on the form would be (JobMaster.JobNO) everything else would stay the same. I have two choices on the drop down box. One is *, which would be to keep the sql query the same. The other choice would be "like E* or S*", which would give me only jobs that start with E or S. I hope this explains it better.

 

[dhookom]

I would have a Row Source of the combo box (cboJobNoStart)like:
"WPIEST";"E";"S"

Then your query would have a where clause of

WHERE Instr(Forms!FRMHOURSRANGE!cboJobNoStart, Left(JobMaster.JobNo,1) >0 AND 
JobMaster.[ACTUAL READY TO SHIP DATE] Between [Forms]![FRMHOURSRANGE]![TXTBEGDATE] And [Forms]![FRMHOURSRANGE]![TXTENDDATE]
ORDER BY JobMaster.JobNo DESC;

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

SELECT DISTINCTROW JobMaster.JobNo, [Job Name - Master Reference] & [Hours Notes] AS HoursReportJobName, [tbl Subcontractor Schedule].[Hours Notes], JobMaster.[Job Name - Master Reference], JobMaster.[EQ ZONE #], JobMaster.[SHOP HOURS STD], JobMaster.[HIGH WATER LEVEL FT], JobMaster.[FIELD HOURS STD], JobMaster.[EST'D WT], JobMaster.[FINAL COMP DATE], JobMaster.[TANK STYLE], JobMaster.[TANK CAPACITY], JobMaster.[TANK DIAMETER], JobMaster.[TANK # OF BAYS], JobMaster.[ACTUAL SHOP HOURS], [tbl Subcontractor Schedule].[Riverport Hours], [Riverport Hours]+[ACTUAL SHOP HOURS]+[ACTUAL SHOP HOURS NEWNAN] AS [TOTAL SHOP HOURS], JobMaster.[ACTUAL FIELD HOURS], [tbl Subcontractor Schedule].[Date Shop Complete (YY/MM)], JobMaster.[HIGH WATER LEVEL IN], [SHOP HOURS STD]-[TOTAL SHOP HOURS] AS [Variance Shop Hours], [FIELD HOURS STD]-[ACTUAL FIELD HOURS] AS [Variance Field Hours], [tbl Subcontractor Schedule].Month, [tbl Subcontractor Schedule].[Riverport Hours], [tbl Subcontractor Schedule].[Hours Saved Plant Prior Year], JobMaster.[ACTUAL READY TO SHIP DATE], [tbl Percent Hours Saved YTD Last Year].[PERCENT HOURS SAVED YTD LAST YEAR], JobMaster.[SHOP HOURS STD-L], JobMaster.[SHOP HOURS STD-N], JobMaster.[ACTUAL SHOP HOURS NEWNAN], JobMaster.[L / N]
FROM [tbl Percent Hours Saved YTD Last Year], JobMaster INNER JOIN [tbl Subcontractor Schedule] ON JobMaster.JobNo=[tbl Subcontractor Schedule].JobNo
WHERE Instr(Forms!FRMHOURSRANGE!cbojobs,Left(JobMaster.JobNo,1)>0 And ((JobMaster.[ACTUAL READY TO SHIP DATE]) Between Forms!FRMHOURSRANGE!TXTBEGDATE And Forms!FRMHOURSRANGE!TXTENDDATE))
ORDER BY JobMaster.JobNo DESC;

The above code is what I used and it says it is too complicated to evaluate.

 

[dhookom]

Try change the where clause to

WHERE Instr(Forms!FRMHOURSRANGE!cbojobs,Left(JobMaster.JobNo,1))>0 And 
JobMaster.[ACTUAL READY TO SHIP DATE] Between Forms!FRMHOURSRANGE!TXTBEGDATE And 
Forms!FRMHOURSRANGE!TXTENDDATE

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It works better, but can we make the S only take jobs that start with "S-". We are showing up with SEP and we want to avoid the months. I haven't been able to figure out how to Separate the "WPIEST" to make it be "W-" or "P-" or "I-" or "E-" or "S-" or "T-". Thanks so much for the help so far.

 

[dhookom]

If I understand your new requirements, you can try set the Row Source to:
"W-P-I-E-S-T-";"E-";"S-"
The use Left(JobMaster.JobNo,2)

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Thank you very much, worked like a charm