Query Criteria /Parameter through VB 2

[davidmo]

Hey Everyone:
I am trying to create a form where the person can choose the type of parameter (Equal To, Range, Less Than, etc.) to use for a report and thought VBA would be best. Here is my code to choose the parameter and then run the report:

Dim stDocName As String, strCriteria As String, prmSQL As String

DoCmd.OpenQuery "qryLoanReport_step_1", , acAdd
DoCmd.Close

Select Case Me.Parameters
Case "Range"
strCriteria = "Between "&Me.InputNumber1&" AND "&Me.InputNumber2
Case "Less Than"
strCriteria = "<" & Me.InputNumber1
Case "Equal To"
strCriteria = "=" & Me.InputNumber1
Case "Greater Than"
strCriteria = ">" & Me.InputNumber1
Case "Not Equal To"
strCriteria = "<>" & Me.InputNumber1
End Select

prmSQL = " SELECT tbl_Borrower_Personal_Information.Borrower_Social_Security_Number, tbl_Historical_Loan_Information.Lender_Case_Number," & _
" [Borrower_Last_Name] & ', ' & [Borrower_First_Name] AS [Full Name], tbl_Borrower_Personal_Information.Borrower_Home_Phone_Number, tbl_Borrower_Personal_Information.Borrower_Current_City, tbl_Borrower_Personal_Information.Borrower_Current_State_Abbreviation, tbl_Borrower_Personal_Information.Borrower_Email_Address," & _
" tbl_Historical_Loan_Information.Mortgage_Applied_Type, tbl_Historical_Loan_Information.Loan_Amount, tbl_Historical_Loan_Information.Loan_Interest_Rate, tbl_Historical_Loan_Information.Loan_Number_of_Months, tbl_Historical_Loan_Information.Amortization_Detail_Type_Description, tbl_Historical_Loan_Information.Purpose_of_Loan," & _
" tbl_Historical_Loan_Information.Subject_Appraisal_Value, tbl_Historical_Loan_Information.Subject_Loan_to_Value, tbl_Historical_Loan_Information.Loan_Closing_Date" & _
" FROM tbl_Borrower_Personal_Information INNER JOIN (tbl_Historical_Loan_Information INNER JOIN qryLoanReport_step_1 ON (tbl_Historical_Loan_Information.Borrower_Social_Security_Number = qryLoanReport_step_1.Borrower_Social_Security_Number) AND (tbl_Historical_Loan_Information.Loan_Closing_Date = qryLoanReport_step_1.FirstOfLoan_Closing_Date))" & _
" ON tbl_Borrower_Personal_Information.Borrower_Social_Security_Number = tbl_Historical_Loan_Information.Borrower_Social_Security_Number" & _
" WHERE (((tbl_Historical_Loan_Information.Loan_Interest_Rate)strCriteria))" & _
" ORDER BY [Borrower_Last_Name] & ', ' & [Borrower_First_Name], tbl_Historical_Loan_Information.Purpose_of_Loan, tbl_Historical_Loan_Information.Loan_Closing_Date DESC;"


DoCmd.SetWarnings False

DoCmd.RunSQL prmSQL

DoCmd.SetWarnings True

stDocName = "rptRecentLoanRates"
DoCmd.OpenReport stDocName, acPreview

I am having two areas of trouble:
1. I get a Expected:end of statement message regarding the
" AND " for my Case "Range" line
2. Also get syntax error (missing operator) message for my strCriteria field.

Help?

Thanks.
DMO

 

[SgtJarrow]

David,

Before you drive yourself crazy trying to fix the SQl each time, you should be able to make use of the report's Filter properties. There is Filter and FilterOn. FilterOn is easy enough...just True or False as to whether you want the report filtered or not. The Filter property is basically the WHERE clause from your SQL, without the WHERE. Check the help files first and read on it...e.asy to use.

This will allow a couple things: The first is that the report can be opened without any filter is necessary. The second is that you can manage the Filter property much easier than the whole SQL.

Set the report source to the whole SQL with NO WHERE clause at all.
Where you would call the report opening, use:

Build the WHERE clause
DoCmd.OpenReport Name, acViewPreview, , strWhere

Hope this works and let me know if you get stuck

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[davidmo]

Rob:
thanks for the help.

I took out the Where clause in my SQL statement (the only change in the code) and now am getting the message "A RunSQL requires an arguement consisting of an SQL statement."

Can I still use my select case statement in the Filter area of the Report properties?

And do I substitute strWhere in the code you wrote to open the report with strCriteria from my code?

 

[SgtJarrow]

First, let's look at the SQL...it should be or very close to:

prmSQL = " SELECT tbl_Borrower_Personal_Information.Borrower_Social_Security_Number, tbl_Historical_Loan_Information.Lender_Case_Number," & _
                     " [Borrower_Last_Name] & ', ' & [Borrower_First_Name] AS [Full Name], tbl_Borrower_Personal_Information.Borrower_Home_Phone_Number, tbl_Borrower_Personal_Information.Borrower_Current_City, tbl_Borrower_Personal_Information.Borrower_Current_State_Abbreviation, tbl_Borrower_Personal_Information.Borrower_Email_Address," & _
                     " tbl_Historical_Loan_Information.Mortgage_Applied_Type, tbl_Historical_Loan_Information.Loan_Amount, tbl_Historical_Loan_Information.Loan_Interest_Rate, tbl_Historical_Loan_Information.Loan_Number_of_Months, tbl_Historical_Loan_Information.Amortization_Detail_Type_Description, tbl_Historical_Loan_Information.Purpose_of_Loan," & _
                     " tbl_Historical_Loan_Information.Subject_Appraisal_Value, tbl_Historical_Loan_Information.Subject_Loan_to_Value, tbl_Historical_Loan_Information.Loan_Closing_Date" & _
                     " FROM tbl_Borrower_Personal_Information INNER JOIN (tbl_Historical_Loan_Information INNER JOIN qryLoanReport_step_1 ON (tbl_Historical_Loan_Information.Borrower_Social_Security_Number = qryLoanReport_step_1.Borrower_Social_Security_Number) AND (tbl_Historical_Loan_Information.Loan_Closing_Date = qryLoanReport_step_1.FirstOfLoan_Closing_Date))" & _
                     " ON tbl_Borrower_Personal_Information.Borrower_Social_Security_Number = tbl_Historical_Loan_Information.Borrower_Social_Security_Number" & _
                     " ORDER BY [Borrower_Last_Name] & ', ' & [Borrower_First_Name], tbl_Historical_Loan_Information.Purpose_of_Loan, tbl_Historical_Loan_Information.Loan_Closing_Date DESC;"

That would be the source of the report...You can drop the DoCmd.RunSQL prmSQL line, since the report will ALWAYS use this base query. Also, set the FilterOn property of the report to True.

What you need to "build" is something like:

   Select Case Me.Parameters 'Where do this come from?????
        Case "Range"
            strCriteria = "Between "&Me.InputNumber1&" AND "&Me.InputNumber2
        Case "Less Than"
            strCriteria = "<" & Me.InputNumber1
        Case "Equal To"
            strCriteria = "=" & Me.InputNumber1
        Case "Greater Than"
            strCriteria = ">" & Me.InputNumber1
        Case "Not Equal To"
            strCriteria = "<>" & Me.InputNumber1

    stDocName = "rptRecentLoanRates"
    DoCmd.OpenReport stDocName, acPreview, , strCriteria

This should open the report, which always has the same base data based on the SQL, and it is filtered and displayed based on the strCriteria you passed during opening.

All this is based only on the small sample of code you have given. It will probably need to be tweaked slightly to fit your exact situation.

Let me know how it works out.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

Replace this:
strCriteria = "Between "&Me.InputNumber1&" AND "&Me.InputNumber2
with this:
strCriteria = "Between " & Me.InputNumber1 & " AND " & Me.InputNumber2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidmo]

Rob and PHV:
Success thanks to both of you. It works perfectly.

Below is the code. Only thing I added was putting the report field name that the criteria was based on in the Select Case statement.

Thanks again.

DMO
Select Case Me.Parameters
Case "Range"
strCriteria = "[Loan_Interest_Rate]Between " & Me.InputNumber1 & " AND " & Me.InputNumber2
Case "Less Than"
strCriteria = "[Loan_Interest_Rate]<" & Me.InputNumber1
Case "Equal To"
strCriteria = "[Loan_Interest_Rate]=" & Me.InputNumber1
Case "Greater Than"
strCriteria = "[Loan_Interest_Rate]>" & Me.InputNumber1
Case "Not Equal To"
strCriteria = "[Loan_Interest_Rate]<>" & Me.InputNumber1
End Select


stDocName = "rptRecentLoanRates"
DoCmd.OpenReport stDocName, acPreview, , strCriteria