using 'WHERE Clause' as filter

[dixxy]

Hello,

I am trying to use a WHERE Clause from a query as a filter for a report, but I am having trouble...I am refering to a faq in this forum as guidlines ([URL unfurl="true"]http://www.tek-tips.com/faqs.cfm?spid=703&sfid=2657) [/url], but it;'s not workinbg out..

here is my where clause;

WHERE (((tblClients.LastName)=[Forms]![frmSearchExtras]![cboClient]) AND ((tblExtra.Done)=[Forms]![frmSearchExtras]![Check4]) AND ((tblExtra.Type)=[Forms]![frmSearchExtras]![txtExtra] Or (tblExtra.Type)=[Forms]![frmSearchExtras]![txtBO])) OR (((tblClients.LastName)=[Forms]![frmSearchExtras]![txtClient2]) AND ((tblExtra.Type)=[Forms]![frmSearchExtras]![txtExtra] Or (tblExtra.Type)=[Forms]![frmSearchExtras]![txtBO]) AND ((tblExtra.JobNumber)=[Forms]![frmSearchExtras]![cboJobNumber]));
[code]

1. can this be done?
2. how should i formulate that?

Thanks,

Sylvain

 

[Steve101]

If you're populating the Filter property of the report, you must exclude the WHERE word; otherwise the syntax of the where clause should not have to change.

The other option you have is to rebuild the entire recordsource of the report with the new where clause; eg.
on the OnOpen event:

me.RecordSource = "SELECT .... WHERE ....."

HTH


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[dhookom]

It's hard to tell if we don't know the data types. Making the assumption that only the LastName field is text:

   Dim strWhere as String
   strWhere = "LastName=""" & [Forms]![frmSearchExtras]![cboClient] & """ AND Done = " & _
     [Forms]![frmSearchExtras]![Check4] & " AND Type = " & _
     [Forms]![frmSearchExtras]![txtExtra] & " Or Type = " & _ 
     [Forms]![frmSearchExtras]![txtBO] & " OR  LastName = """ & _
     [Forms]![frmSearchExtras]![txtClient2] & """ AND Type= " & _
     [Forms]![frmSearchExtras]![txtExtra] & " Or Type = " _
     [Forms]![frmSearchExtras]![txtBO] & " AND JobNumber = " & _
     [Forms]![frmSearchExtras]![cboJobNumber]
    DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Note: I removed all the ()s so you will have to add them back in where they belong.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.