Search Form Issues

  • Thread starter Thread starter ace
  • Start date Start date
A

ace

I still have a lot to learn so please bear with me...

I am trying to create a search form that will open a report based on
the search criteria specified by the user. I have five search
criteria in all:

1. City
2. State
3. Property Type
4. Origination Date*
5. calCutBalUnits* (a calculated field - "Cutoff Balance per Unit" -
in the report)

* I would like the user to specify a range for these fields.

The VB code that I have created so far is listed below (please ignore
the first three search fields for now - I'll come back to them later).
First problem, the code isn't even compiling (even though it was last
week). Second, when I do get it to compile, the report opens, but the
search form criteria fields are totally ignored.

I could use some serious help here and would appreciate any
suggestions.

Thanks.


Private Sub cmdSearch_Click()

Dim strForm As String 'Name of form to close.
Dim strReport As String 'Name of report to open.
Dim strField1 As String 'Name of city field.
Dim strField2 As String 'Name of state field.
Dim strField3 As String 'Name of property type field.
Dim strField4 As String 'Name of date field.
Dim strField5 As String 'Name of loan per unit field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strForm = "Search Form"
strReport = "CMBS Loan Level Market Report"
strField1 = "City"
strField2 = "State"
strField3 = "Property Type"
strField4 = "origination Date"
strField5 = "calCutBalUnits"


If IsNull(Me.CitySearch) Then
End If


If IsNull(Me.StateSearch) Then
End If


If IsNull(Me.PropertyTypeSearch) Then
End If


If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no
start.
strWhere = strField4 & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no
End.
strWhere = strField4 & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end
dates.
strWhere = strField4 & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


If IsNull(Me.txtLowDollar) Then
If Not IsNull(Me.txtHighDollar) Then 'High loan/sf amount,
but no low.
strWhere = strField5 & " <= " & Format(Me.txtHighDollar,
conDateFormat)
End If
Else
If IsNull(Me.txtHighDollar) Then 'Low loan/sf amount,
but no high.
strWhere = strField5 & " >= " & Format(Me.txtLowDollar,
conDateFormat)
Else 'Both start and end
dates.
strWhere = strField5 & " Between " &
Format(Me.txtLowDollar, conDateFormat) _
& " And " & Format(Me.txtHighDollar, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub
 
Back
Top