One or Multiple Criteria Search Form

E

Elvis72

Here is the code, I have split the search criteria because it was just too
long and I have no idea how to work around that:


Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long

strWhere = strWhere & "([Commissioning] = " & Me!cboFilterCommissioning & ")
AND "
strWhere = strWhere & "([ Steam Line Blowing] = " &
Me!cboFilterSteamLineBlowing & ") AND "
strWhere = strWhere & "([Chemical Cleaning] = " &
Me!cboFilterChemicalCleaning & ") AND "
strWhere = strWhere & "([Commissioning I and E] = " &
Me!cboFilterCommissioningIandE & ") AND "
strWhere = strWhere & "([Turnover Coordination] = " &
Me!cboFilterTurnoverCoordination & ") AND "
strWhere = strWhere & "([Commissioning Planning] = " &
Me!cboFilterCommissioningPlanning & ") AND "
strWhere = strWhere & "([Facility Evaluation] = " &
Me!cboFilterFacilityEvaluation & ") AND "
strWhere = strWhere & "([Operation Preparedness] = " &
Me!cboFilterOperationPreparedness & ") AND "
strWhere = strWhere & "([Corrosion Program] = " &
Me!cboFilterCorrosionProgram & ") AND "
strWhere = strWhere & "([Fugitive Emission Testing] = " &
Me!cboFilterFugitiveEmissionTesting & ") AND "
strWhere = strWhere & "([NDT Certification] = " &
Me!cboFilterNDTCertification & ") AND "
strWhere = strWhere & "([Maintenance] = " & Me!cboFilterMaintenance & ") AND "
strWhere = strWhere & "([Maintenance Planning] = " &
Me!cboFilterMaintenancePlanning & ") AND "
strWhere = strWhere & "([Piping] = " & Me!cboFilterPiping & ") AND "
strWhere = strWhere & "([Rotating] = " & Me!cboFilterRotating & ") AND "
strWhere = strWhere & "([Maintenance I and E] = " &
Me!cboFilterMaintenanceIandE & ") AND "
strWhere = strWhere & "([Operations] = " & Me!cboFilterOperations & ") AND "
strWhere = strWhere & "([Operations Planning] = " &
Me!cboFilterOperationsPlanning & ") AND "
strWhere = strWhere & "([Scheduling] = " & Me!cboFilterScheduling & ") AND "
strWhere = strWhere & "([Inventory of Supply] = " &
Me!cboFilterInventoryofSupply & ") AND "
strWhere = strWhere & "([Product Movement] = " & Me!cboFilterProductMovement
& ") AND "
strWhere = strWhere & "([Start-Up] = " & Me!cboFilterStartUp & ") AND "
strWhere = strWhere & "([Temporary] = " & Me!cboFilterTemporary & ") AND "
strWhere = strWhere & "([Long-Term] = " & Me!cboFilterLongTerm & ") AND "
strWhere = strWhere & "([PandID Reviews] = " & Me!cboFilterPandIDReviews &
") AND "
strWhere = strWhere & "([Preliminary] = " & Me!cboFilterPreliminary & ") AND "
strWhere = strWhere & "([As Built] = " & Me!cboFilterAsBuilt & ") AND "
strWhere = strWhere & "([HAZOP Participation] = " &
Me!cboFilterHAZOPParticipation & ") AND "
strWhere = strWhere & "([Training] = " & Me!cboFilterTraining & ") AND "
strWhere = strWhere & "([Create Training Material] = " &
Me!cboFilterCreateTrainingMaterial & ") AND "
strWhere = strWhere & "([Presenter] = " & Me!cboFilterPresenter & ") AND "
strWhere = strWhere & "([Technical Writing] = " &
Me!cboFilterTechnicalWriting & ") AND "
strWhere = strWhere & "([Understanding of Material] = " &
Me!cboFilterUnderstandingofMaterial & ") AND "

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

But it is still coming up with errors on the Me.Filters.
 
B

Beetle

That's a lot of criteria. I don't see any date or string delimiters in there,
which would imply that *all* of those fields and combo boxes are integer
values, which seems unlikely. Is that the case?
--
_________

Sean Bailey


Elvis72 said:
Here is the code, I have split the search criteria because it was just too
long and I have no idea how to work around that:


Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long

strWhere = strWhere & "([Commissioning] = " & Me!cboFilterCommissioning & ")
AND "
strWhere = strWhere & "([ Steam Line Blowing] = " &
Me!cboFilterSteamLineBlowing & ") AND "
strWhere = strWhere & "([Chemical Cleaning] = " &
Me!cboFilterChemicalCleaning & ") AND "
strWhere = strWhere & "([Commissioning I and E] = " &
Me!cboFilterCommissioningIandE & ") AND "
strWhere = strWhere & "([Turnover Coordination] = " &
Me!cboFilterTurnoverCoordination & ") AND "
strWhere = strWhere & "([Commissioning Planning] = " &
Me!cboFilterCommissioningPlanning & ") AND "
strWhere = strWhere & "([Facility Evaluation] = " &
Me!cboFilterFacilityEvaluation & ") AND "
strWhere = strWhere & "([Operation Preparedness] = " &
Me!cboFilterOperationPreparedness & ") AND "
strWhere = strWhere & "([Corrosion Program] = " &
Me!cboFilterCorrosionProgram & ") AND "
strWhere = strWhere & "([Fugitive Emission Testing] = " &
Me!cboFilterFugitiveEmissionTesting & ") AND "
strWhere = strWhere & "([NDT Certification] = " &
Me!cboFilterNDTCertification & ") AND "
strWhere = strWhere & "([Maintenance] = " & Me!cboFilterMaintenance & ") AND "
strWhere = strWhere & "([Maintenance Planning] = " &
Me!cboFilterMaintenancePlanning & ") AND "
strWhere = strWhere & "([Piping] = " & Me!cboFilterPiping & ") AND "
strWhere = strWhere & "([Rotating] = " & Me!cboFilterRotating & ") AND "
strWhere = strWhere & "([Maintenance I and E] = " &
Me!cboFilterMaintenanceIandE & ") AND "
strWhere = strWhere & "([Operations] = " & Me!cboFilterOperations & ") AND "
strWhere = strWhere & "([Operations Planning] = " &
Me!cboFilterOperationsPlanning & ") AND "
strWhere = strWhere & "([Scheduling] = " & Me!cboFilterScheduling & ") AND "
strWhere = strWhere & "([Inventory of Supply] = " &
Me!cboFilterInventoryofSupply & ") AND "
strWhere = strWhere & "([Product Movement] = " & Me!cboFilterProductMovement
& ") AND "
strWhere = strWhere & "([Start-Up] = " & Me!cboFilterStartUp & ") AND "
strWhere = strWhere & "([Temporary] = " & Me!cboFilterTemporary & ") AND "
strWhere = strWhere & "([Long-Term] = " & Me!cboFilterLongTerm & ") AND "
strWhere = strWhere & "([PandID Reviews] = " & Me!cboFilterPandIDReviews &
") AND "
strWhere = strWhere & "([Preliminary] = " & Me!cboFilterPreliminary & ") AND "
strWhere = strWhere & "([As Built] = " & Me!cboFilterAsBuilt & ") AND "
strWhere = strWhere & "([HAZOP Participation] = " &
Me!cboFilterHAZOPParticipation & ") AND "
strWhere = strWhere & "([Training] = " & Me!cboFilterTraining & ") AND "
strWhere = strWhere & "([Create Training Material] = " &
Me!cboFilterCreateTrainingMaterial & ") AND "
strWhere = strWhere & "([Presenter] = " & Me!cboFilterPresenter & ") AND "
strWhere = strWhere & "([Technical Writing] = " &
Me!cboFilterTechnicalWriting & ") AND "
strWhere = strWhere & "([Understanding of Material] = " &
Me!cboFilterUnderstandingofMaterial & ") AND "

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

But it is still coming up with errors on the Me.Filters.
Click to expand...
 
G

Graham Mandeno

Hi Elvis

The query window is much better for debugging SQL statements because you get
a lot more helpful information. I suggest you set a breakpoint at the line:
Me.Filter = strWhere

Then, when the code stops at the breakpoint, examine strWhere in the
Immediate window:
?strWhere

Select everything that is displayed and press Ctrl-C to copy.

Then create a new, empty query (click "Close" at the Show Table window) and
then click the "SQL button at the left of the toolbar to show the query in
SQL view.

Type the following:
Select * from [your table name] where
and then click ctrl-V to paste your WHERE clause.

Now run the query and see what happens.

My guess is that a lot of the combo boxes are Null, so you will be getting a
SQL string like this:
([Commissioning] = ) AND ([ Steam Line Blowing] = ) AND ...

If there is a possibility that combo boxes are Null, then you must test for
it:
If Not IsNull(Me!cboFilterCommissioning) Then
strWhere = strWhere & "([Commissioning] = " &
Me!cboFilterCommissioning _
& ") AND "
End If

(by the way, did you notice the leading space in [ Steam Line Blowing] ?)

Finally, as Sean said, if the field you are comparing is not numeric then
you will need delimiters around the comparison values - quotes for text
fields and hash/pound signs for dates.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Elvis72 said:
Here is the code, I have split the search criteria because it was just too
long and I have no idea how to work around that:


Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long

strWhere = strWhere & "([Commissioning] = " & Me!cboFilterCommissioning &
")
AND "
strWhere = strWhere & "([ Steam Line Blowing] = " &
Me!cboFilterSteamLineBlowing & ") AND "
strWhere = strWhere & "([Chemical Cleaning] = " &
Me!cboFilterChemicalCleaning & ") AND "
strWhere = strWhere & "([Commissioning I and E] = " &
Me!cboFilterCommissioningIandE & ") AND "
strWhere = strWhere & "([Turnover Coordination] = " &
Me!cboFilterTurnoverCoordination & ") AND "
strWhere = strWhere & "([Commissioning Planning] = " &
Me!cboFilterCommissioningPlanning & ") AND "
strWhere = strWhere & "([Facility Evaluation] = " &
Me!cboFilterFacilityEvaluation & ") AND "
strWhere = strWhere & "([Operation Preparedness] = " &
Me!cboFilterOperationPreparedness & ") AND "
strWhere = strWhere & "([Corrosion Program] = " &
Me!cboFilterCorrosionProgram & ") AND "
strWhere = strWhere & "([Fugitive Emission Testing] = " &
Me!cboFilterFugitiveEmissionTesting & ") AND "
strWhere = strWhere & "([NDT Certification] = " &
Me!cboFilterNDTCertification & ") AND "
strWhere = strWhere & "([Maintenance] = " & Me!cboFilterMaintenance & ")
AND "
strWhere = strWhere & "([Maintenance Planning] = " &
Me!cboFilterMaintenancePlanning & ") AND "
strWhere = strWhere & "([Piping] = " & Me!cboFilterPiping & ") AND "
strWhere = strWhere & "([Rotating] = " & Me!cboFilterRotating & ") AND "
strWhere = strWhere & "([Maintenance I and E] = " &
Me!cboFilterMaintenanceIandE & ") AND "
strWhere = strWhere & "([Operations] = " & Me!cboFilterOperations & ") AND
"
strWhere = strWhere & "([Operations Planning] = " &
Me!cboFilterOperationsPlanning & ") AND "
strWhere = strWhere & "([Scheduling] = " & Me!cboFilterScheduling & ") AND
"
strWhere = strWhere & "([Inventory of Supply] = " &
Me!cboFilterInventoryofSupply & ") AND "
strWhere = strWhere & "([Product Movement] = " &
Me!cboFilterProductMovement
& ") AND "
strWhere = strWhere & "([Start-Up] = " & Me!cboFilterStartUp & ") AND "
strWhere = strWhere & "([Temporary] = " & Me!cboFilterTemporary & ") AND "
strWhere = strWhere & "([Long-Term] = " & Me!cboFilterLongTerm & ") AND "
strWhere = strWhere & "([PandID Reviews] = " & Me!cboFilterPandIDReviews &
") AND "
strWhere = strWhere & "([Preliminary] = " & Me!cboFilterPreliminary & ")
AND "
strWhere = strWhere & "([As Built] = " & Me!cboFilterAsBuilt & ") AND "
strWhere = strWhere & "([HAZOP Participation] = " &
Me!cboFilterHAZOPParticipation & ") AND "
strWhere = strWhere & "([Training] = " & Me!cboFilterTraining & ") AND "
strWhere = strWhere & "([Create Training Material] = " &
Me!cboFilterCreateTrainingMaterial & ") AND "
strWhere = strWhere & "([Presenter] = " & Me!cboFilterPresenter & ") AND "
strWhere = strWhere & "([Technical Writing] = " &
Me!cboFilterTechnicalWriting & ") AND "
strWhere = strWhere & "([Understanding of Material] = " &
Me!cboFilterUnderstandingofMaterial & ") AND "

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

But it is still coming up with errors on the Me.Filters.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

One or More Criteria 1
Multiple Search Criteria with AND/OR??? 4
Multiple Criteria Search Form 3
Filter multiple fields with one search criteria on form 3

Top