Search query to return All records or Selection 2

[dbar10]

I have done this before, but cannot remember for the life of me how I did it. I have the query below:

SELECT [Worker table].WorkerID, [Worker table].WorkStatusID, [LastName] & ", " & [FirstName] & " " & [Middle] AS WorkerFullName, [Worker table].Phone1, [Job Description].JobDescID, [Job Description].JobDesc, [Job Description].Primary
FROM Jobs INNER JOIN ([Job Description] INNER JOIN [Worker table] ON [Job Description].WorkerID=[Worker table].WorkerID) ON Jobs.JobID=[Job Description].JobDesc
WHERE ((([Worker table].WorkerID)<>188) And (([Worker table].WorkStatusID)=Forms!WorkerReports!ComboStatus) And (([Job Description].Primary)=On) And ((Jobs.JobID)=Forms!WorkerReports!ComboJob Or Forms!WorkerReports!ComboJob="<Select>"))
ORDER BY [Worker table].LastName, [Worker table].FirstName;

I have a combo box that I can select a particular JobID or <Select>. If choosing Select, I want to return all the JobIDs. Can you please help?

 

[kjv1611]

All the JobIDs? Where do these JobIDs come from? Are you saying all JobIDs in a certain table? If so, does every record have a JobID?

If so, you can leave off the WHERE clause in that instance. If not all records have a JobID, but you want all JobIDs in a particular table, you can compare against NULLS.

I suggest you do this within VBA, and build the queryies (SQL code) dynamically. Here's my suggestion so far:

Private Sub MyComboBox_AfterUpdate()
  Dim strSQL1 As String
  Dim strSQL2 As String
  Dim strSQL3 As String
  Dim strSQL_Complete As String

  strSQL1 = "SELECT [Worker table].WorkerID, [Worker table].WorkStatusID, " & _
			 "[LastName] & ", " & [FirstName] & " " & [Middle] AS WorkerFullName, " & _
			 "[Worker table].Phone1, [Job Description].JobDescID, " & _
			 "[Job Description].JobDesc, [Job Description].Primary " & _
			 "FROM Jobs INNER JOIN ([Job Description] " & _
			 "INNER JOIN [Worker table] " & _
			 "ON [Job Description].WorkerID=[Worker table].WorkerID) " & _
			 "ON Jobs.JobID=[Job Description].JobDesc " & _
			 "WHERE ((([Worker table].WorkerID)<>188) "

  strSQL2 = ")) " & _
			"ORDER BY [Worker table].LastName, [Worker table].FirstName;"

  If MyComboBox.Text = "Select" Then
	strSQL_Complete = strSQL1 & strSQL3
  Else
    strSQL2 = And " & _
			 "(([Worker table].WorkStatusID) = " & MyComboBox.Text
	strSQL_Complete = strSQL1 & strSQL2 & strSQL3
  End If
    
  DoCmd.OpenQuery strSQL_Complete
  
  strSQL1 = vbNullString
  strSQL2 = vbNullString
  strSQL3 = vbNullString
  strSQL_Complete = vbNullString
 
End Sub

I've not yet tested the code, and I'm not 100% sure about how you want to access the data. If you want to return it to the recordsource of your form, then you can change DoCmd.OpenQuery... to Form.RecordSource = strSQL_Complete, I believe.

Post back with any comments/questions, or either let us know if that worked for you.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

I'm sorry I did not give enough info. The function of this form is to select a worker or workers for a series of reports that will look to this query for critieria.
This query is to supply the names (WorkerID)s for the reports either by a particular JobID or all JobIDs.
The JobID info is supplied by the ComboJob box it lists all the individual JobIDs and <Select>. I want <Select> to return all the workers regardless of JobID. I appreciate your willingness to help.

 

[kjv1611]

So... You want the Combo Box itself to list all the workers as choices in that list?

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

What is the value of the BoundColumn when <Select> is displayed ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

dbar10,
What results do you get when you run your first query? Is it wrong or right? If it is wrong, tell us what is wrong about it.

If choosing Select, I want to return all the JobIDs

is this regardless of the status and other criteria in the query?

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks guys. Every worker has a JobID i.e. CA, CNA, LPN etc. I want the query to return every worker with a certain JobID or have the ability to return all workers regardless of JobID. The JobID for <Select> = 1. The query works fine for particular JobID but when I use <Select>, no records are returned. This query is going to be used to build reports on Workers. Other criteria is being used in the query such as WorkStatus and others. Hope this helps.

 

[traingamer]

As I understand the question, you want to include all ids when <Select> is chosen or individual ones otherwise. One way to accomplish this is to use the LIKE keyword with an Immediate If (IIF) function.
Replace this:

((Jobs.JobID)=Forms!WorkerReports!ComboJob Or Forms!WorkerReports!ComboJob="<Select>"))

With this (not tested):

((Jobs.JobID) [b]like[/b] iif(Forms!WorkerReports!ComboJob="<Select>", "*", Forms!WorkerReports!ComboJob))

In this case, if <select> is chosen (I've seen many instances of people using <all> instead), the wilcard character (*) is used, otherwise it uses the value selected.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

wilcard, wildcard, whatever...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[PHV]

The JobID for <Select> = 1
And ((Jobs.JobID)=Forms!WorkerReports!ComboJob Or Forms!WorkerReports!ComboJob="1"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

Thanks everyone, especially PHV. Once again you pointed out what should have been obvious to me. I knew I was on the right track but I was using the "<Select>" instead of the JobID of 1 from the bound column. Thank you guys very much. Hope you have a great day.

 

[PHV]

Greg, FYI, Like '*' don't retrieve Null ...

 

[traingamer]

Greg, FYI, Like '*' don't retrieve Null ...

True. I was assuming that it was not null, which may or may not have been the case.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard