How to write vba Multi field search form

[alhabkk]

I have a table name ex. equipment and many fields inside ex. date, equipment name (combo box) and others.

I need to make form for all fields and button search once I click on it will open a report depending on a fields i selected.

I think that I have to use VBA

help me

 

[pbaldy]

One method is dynamic SQL, demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

In your case, you could build a wherecondition instead of the full SQL.

 

[alhabkk]

Paul im not that much good with codes !
I use this vba code but there is one problem, it not take the values i put it in boxs!

Private Sub Toggle3_Click()
Dim strReport As String
Dim strDateField As String
Dim lngLen As Long
Dim strWhere As String
Dim lngView As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


strReport = "rptFLM"
strDateField = "[Date]"
lngView = acViewReport



If Not IsNull(Me.txtlocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtlocation & "*"") AND "
End If

If Not IsNull(Me.txtDateFrom) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtDateTo) Then
strWhere = strWhere & "([Date] < " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtTagNumber) Then
strWhere = strWhere & "([Tag Number] Like ""*" & Me.txtTagNumber & "*"") AND "
End If

If Not IsNull(Me.txtCreatedby) Then
strWhere = strWhere & "([Created by] Like ""*" & Me.txtCreatedby & "*"") AND "
End If

If Not IsNull(Me.txtJSA1) Then
strWhere = strWhere & "([JSA / Procedure] Like ""*" & Me.txtJSA1 & "*"") AND "
End If


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

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

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


DoCmd.OpenReport strReport, lngView, strWhere
Exit_Handler:
Exit Sub

End Sub

 

[alhabkk]

see below attech

 

[pbaldy]

What is the result of the Debug.Print? Try this:

DoCmd.OpenReport strReport, lngView, , strWhere

 

[JHB]

Replace the IsNull with =""

If Not IsNull(Me.YourControlName) Then

If Not Me.YourControlName = "" Then

It is 6 replaces.
And as Paul wrote:
DoCmd.OpenReport strReport, lngView, , strWhere

 

[pbaldy]

Well, this test would be even better since it handles both Null & ZLS:

If Len(Me.SomeControl & vbNullString) > 0 Then

 

[alhabkk]

Thanks alot
its work great

 

[pbaldy]

No problem.