Help building a search string in an event procedure

[verdes]

I am using MS Access 2016

I have a database that includes 2 tables:

blank_tbl
blankID
blankYear (number)
blankDistrict (number - join on districtID)
blankInfo1
blankInfo2
blankInfo3
blankFinanceamt
blankTrusteeamt
blankClose (yes/no)

district_tbl
districtID
districtName
districtDioceseNumber

I have a search form where I build a select statement (when the search button is pushed) based on the search criteria listed in the form to locate the matching records from the blank_tbl: Diocese, Date Begin, Date End, District.

You can search on 1 or any combination of the criteria entered in the search form. For example: search for blanks records where District = 2 and Date End <= 10/1/2015.

When the select statement is built, it's passed to a subform that displays the matching record. I have everything working except a search that involves districtDiocese

The subform uses a query as the control source that uses all the fields in the blank table plus the diocese and district name from the diocese_tbl. I used the join property that says use every record in the blank_tbl and the matching District records.

I need to be able to find all blanks in a selected diocese and in a selected diocese for certain years etc.

My problem: I don't know how to construct the Select statement to get the diocese number from the district table and concatenate it with the other criteria in blank_tbl.

Here is the working code that I'm using in the event procedure without the search by diocese:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Diocese (commented out because I don't know how to reference it)
' If Nz(Me.Diocese) <> "" Then
' Add it to the predicate - exact match
' strWhere = strWhere & " AND " & "blank_tbl.districtDio = " & Me.Dio & ""

' End If

' If Distirct
If Nz(Me.District) <> "" Then
'Add it to the Predicate - exact match
strWhere = strWhere & " AND " & "blank_tbl.blankDistrict = " & Me.District & ""
End If

' If Begin Year
If Nz(Me.quadBeginYear) <> Then
' add it to the predicate
strWhere = strWhere & " AND " & "blank_tbl.blankYear >= " & Me.blankBeginYear & ""

End If

' If End Year
If Nz(Me.quadEndYear) <> Then
' add it to the predicate
strWhere = strWhere & " AND " & "blank_tbl.blankYear >= " & Me.blankEndYear & ""

End If





' If Closed
If Nz(Me.Closed) = -1 Then
'Add the predicate
strWhere = strWhere & " AND " & "blank_tbl.[blankClose] = " & Me.Closed & ""
End If



If strError <> "" Then
MsgBox strError
Else

If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Abatements.Form.Filter = strWhere
Me.Browse_All_Abatements.Form.FilterOn = True
End If
End Sub

Any help you can provide will be appreciated. Thanks

 

[Cronk]

I take the quick and easy way to start off with by using the Create Query UI to generate a query with all the criteria you want with specific values eg blank_tbl.blankYear >= 2017".

Then view the sql generated, copy it to your procedure and replace the specific values with references to the the form's controls.

Note your vba will have to embed single quotes where the data is text

eg "blank_tbl.districtDio = '" & Me.Dio & "'"

 

[verdes]

Thank you, so much!That helped me see clearly. Everything is working fine. I appreciate your help.