Filter a query-based report by user input.

[DodgeyAus]

G'day

I currently have 3 copies of the same report, all based on similar queries:

• One which prints all reports.
• One which prints the reports based on the teacher entering their name (which is an expression in the query & report)
• One which prints the reports based on the teachers entering their class code (which is a field in the query & report)

I'm figuring there has to be a way of using the 1 report, and filtering the query by user input through a dialogue box when they hit the magic button. Unfortunately I haven't been able to work out the right question to ask Google to give me the answer I need.

I've tried filters and "where"s to no avail. The filters I've been using give me, "The specified field ... could refer to more than one table ..." I'm also obviously wording my "Where"s wrong too.

Any suggestions would be greatly appreciated.

Cheers, Rog

 

[Ranman256]

Test all controls for a possible filter then build the where clause.
[code]
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,4)

   'apply the sql to the report
docmd.openreport  "rMeReport",,sWhere

 

[rikklaney1]

I've just tried using this

If Not IsNull(Text51) Then swhere = swhere & " or " & Text51 & ""
If Not IsNull(Text53) Then swhere = swhere & " or " & Text53 & ""
If Not IsNull(Text54) Then swhere = swhere & " or " & Text54 & ""
If Not IsNull(Text55) Then swhere = swhere & " or " & Text55 & ""
If Not IsNull(Text56) Then swhere = swhere & " or " & Text56 & ""
'remove 1st And
swhere = Mid(swhere, 5)

'apply the sql to the report
DoCmd.OpenReport "exel pick report", acViewReport


Then on the query for the report I have a criteria for a field set to look to the swhere text box but when more than one is active it returns no records. Could that be because when it creates the statemnet in swhere it leaves off the quotation marks before and after the values? The text values are all names. Rops, ENG, Boom, FOL 1, and FOL 2.

 

[DodgeyAus]

Thanks guys. Looks like I need to learn another "language". Been suspecting that for a while, but haven't had the time. Need to move it up the list!