Running queries from a form

[Maxine1010]

Please, please can someone help.

I have set up a form to run a query using text boxes and combo boxes to set the query parameters. The results are then displayed in a report. The form uses the following:

text boxes: BeginningDate
Enddate

combo boxes: Ward
Acquisition Type

The date parameters work fine. I can even select a particular ward using In ([Forms]![Report Form]![Ward]) and the query will work BUT I cannot select a ward and an Acquisition Type at the same time. I would also like to return all records when no ward and/ or acquisition type is specified (using the Is Null property)but this only returns all records all of the time.

I would be extremely grateful of any advice in solving this problem.

Thanks

Maxine

 

[jfgambit]

Max:

Here is an example of how to re-write the SQL of a query definition through code.

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef

Set dbNm = CurrentDb()

'Set SQL Definition for lstSearch
strSQL = "SELECT tblCustomers.[Agreement#], tblCustomers.[Vin#], tblJacket.[Claim#], tblCustomers.CustLName, tblCustomers.VehicleSaleDate, tblCustomers.Program " & _
"FROM tblCustomers LEFT JOIN tblJacket ON tblCustomers.[Agreement#] = tblJacket.[Agreement#] "
strWhere = "WHERE"
strOrder = "ORDER BY tblCustomers.[Agreement#];"

' set where clause conditions
If Not IsNull(Me.txtAgree) Then
strWhere = strWhere & " (tblCustomers.[Agreement#])= '" & Me.txtAgree & "' AND"
End If
If Not IsNull(Me.txtVIN) Then
strWhere = strWhere & " (tblCustomers.[VIN#]) Like '*" & Me.txtVIN & "*' AND"
End If
If Not IsNull(Me.txtClaim) Then
strWhere = strWhere & " (tblJacket.[Claim#]) = " & Me.txtClaim & " AND"
End If
If Not IsNull(Me.txtLName) Then
strWhere = strWhere & " (tblCustomers.CustLName) Like '*" & Me.txtLName & "*' AND"
End If
If Not IsNull(Me.txtPDate) Then
strWhere = strWhere & " (tblCustomers.VehicleSaleDate) = #" & Me.txtPDate & "# AND"
End If
strWhere = Mid(strWhere, 1, Len(strWhere) - 4) ' remove ' and'

Set qryDef = dbNm.QueryDefs("qryJBSearch")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

DoCmd.openreport "NameofReport"

Let me know if this is confusing.
If it is, post a sample of your database and I'll see if I can add the above code.

HTH

 

[Maxine1010]

Thankyou!

I'm afraid I wouldn't know where to start with the code, so I would be very grateful if you wouldn't mind having a go at putting it onto the database I have attached. I have cleared the database and just entered some example records.

On entering the database just select the MRSA button and it will take you through to the main form. It is the Report button which takes you through to the form using the query. For your information, the following tables, queries, forms and reports are used:

Table: MRSA Table
Query: MRSA Report Query
Form: MRSA Report Form
Report: MRSA Report

Many thanks again for this!!!

 

[jfgambit]

Here you go...let me know how it works for you

You'll have to re-import your Macros from your master db, I had to delete them to make the db small enough to fit back on the board.

Have fun.

 

[Maxine1010]

Works brilliantly!!....

Thankyou very much, if ever I can repay the favour......!