Help with query

[mwest]

I have one table named tblreport_OLD , the table has many columns. I would
like to do a search in 3 of the columns and getting only the records that
have all three.
Department, Contaminant, Technician. The search will be on different items,
so how can I set it up so I don't have to change the criteria all the time.
I would also like the records to open up into a form.
Any help would be greatly appreciated.

 

[fredg]

I have one table named tblreport_OLD , the table has many columns. I would
like to do a search in 3 of the columns and getting only the records that
have all three.
Department, Contaminant, Technician. The search will be on different items,
so how can I set it up so I don't have to change the criteria all the time.
I would also like the records to open up into a form.
Any help would be greatly appreciated.

As Criteria on the query, write:
[Enter Department]
as criteria for the department column.
[Enter Contaminant]
as criteria on the Contaminant column.
[Enter Technician]
as criteria on the Technician column.
*** Make sure you enter each criteria on the SAME ROW. ***
Save the query.

Then create a form, using the query as the form's record source.

When you open the form, it will prompt you for the 3 entries.
Only records that have all those entered values as the only value in
that field will be returned.

If a field can have more than one value, i.e. 'dust', 'moisture',
change the criteria for that column to
Like "*" & [Enter Contaminant] & "*"
This will find that record if you enter either word.

 

[mwest]

I've gotten a little further however, with the bottom code I keep getting a
compile error variable not defined. Any advice It is greatly appreciated

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant,
Department, Date) Then
DoCmd.Close acForm, "Find multi"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub

Err_multiOK_Click:
MsgBox Err.Description
Resume Exit_multiOK_Click
End Sub

I have one table named tblreport_OLD , the table has many columns. I would
like to do a search in 3 of the columns and getting only the records that
have all three.
Department, Contaminant, Technician. The search will be on different items,
so how can I set it up so I don't have to change the criteria all the time.
I would also like the records to open up into a form.
Any help would be greatly appreciated.

As Criteria on the query, write:
[Enter Department]
as criteria for the department column.
[Enter Contaminant]
as criteria on the Contaminant column.
[Enter Technician]
as criteria on the Technician column.
*** Make sure you enter each criteria on the SAME ROW. ***
Save the query.

Then create a form, using the query as the form's record source.

When you open the form, it will prompt you for the 3 entries.
Only records that have all those entered values as the only value in
that field will be returned.

If a field can have more than one value, i.e. 'dust', 'moisture',
change the criteria for that column to
Like "*" & [Enter Contaminant] & "*"
This will find that record if you enter either word.