Open report based on multiple criteria

Talismanic · Feb 2, 2001

I have a report that opens based on a drop down box. Basicaly you select the name in the box and press a button and it will open the report with the related data.

DoCmd.OpenReport "CloseOutRep", acViewPreview, , "[Estimator]= '" & Me.txtEstimator & "'"

The user wants to narrow the reports down by adding two more combo boxes - txtLocation and txtYear. How can I open this report with the additional criteria of

"[Location]= '" & Me.txtLocation & "'"
"[SalesYear]= '" & Me.txtSales & "'"

I guess all criteria has to be true for the report to display data and then all I need is a way to make Access assume all records should be displayed when one criteria or another is not chosen.

Any ideas?

[This message has been edited by Talismanic (edited 02-02-2001).]

Chris RR · Feb 2, 2001

I'd base the reports on queries that pull in the proper text fields from the form. If the fields are optional, make sure that the query criteria uses "like" with a * (that covers the no input situation.)

In the click event of the report run button, I'd do a little editting to make sure that anything that really is required has been entered:
stDocName = Me![lstReports]

Select Case stDocName

Case "rMy_wonderful_report"

If IsNull(Me.cboSomething) Then
MsgBox "You must select a Something!", vbExclamation, "REQUIRED INPUT"
Me.cboArea.SetFocus
Exit Sub
End If
End Select
stDocName = Me![lstReports]
DoCmd.OpenReport stDocName, acPreview
Forms![fMy_Form].Visible = False

Talismanic · Feb 2, 2001

I'd base the reports on queries that pull in the proper text fields from the form. If the fields are optional, make sure that the query criteria uses "like" with a * (that covers the no input situation.)

All three combo box's are based on a queries and the choices are limmited to the list.

In the click event of the report run button, I'd do a little editting to make sure that anything that really is required has been entered: stDocName = Me![lstReports]

None of the fields are required, if none are selected I want the whole list to show.

Is it possible to open the report based on my multiple criteria?

pdx_man · Feb 2, 2001

Yes,
Build your WHERE statement before getting to the Open command.

:
WHEREStmt = ""
if not isnull(me.txtEstimator) then
WHEREStmt = "[Estimator] = " & me.txtEstimator & " AND "
if not isnull(me.txtLocator) then
WHEREStmt = WHEREStmt & "[Locator] = " & me.txtLocator & " AND "
if not isnull(me.txtYear) then
WHEREStmt = WHEREStmt & "[Year] = " & me.txtYear & " AND "

'Get rid of trailing AND statement
WHEREStmt=left(WHEREStmt,len(WHEREStmt) - 5)

DoCmd.OpenReport stDocName, acPreview, WHEREStmt
:

Talismanic · Feb 5, 2001

pdx_man, I am missing something! Here is the code as I have it. By the way, I found out I only need Estimator and Year so I am only dealing with two pieces of criteria instead of three.

Private Sub cmdReport_Click()

Dim whereStmt As String

whereStmt = ""

If Not IsNull(Me.txtEstimator) Then
whereStmt = "[Estimator] = " & Me.txtEstimator & " AND "

If Not IsNull(Me.txtYear) Then
whereStmt = whereStmt & "[SalesYear] = " & Me.txtYear & " AND "

whereStmt = Left(whereStmt, Len(whereStmt) - 5)

End If
End If

DoCmd.OpenReport "CloseOutRep", acViewPreview, whereStmt

End Sub

Chris RR · Feb 5, 2001

Let me explain my method.

If your base your report on a query, and that query (the one underlying your report) uses your screen fields as its criteria, you don't need to do the "where" business at all. Just having the query criteria is enough.

Using "like *" & something in that query will cover the situation where nothing has been entered into the form, but you still want to return records.

The source for your combo boxes (or even if they are straight text fields) doesn't matter (although I like the combo boxes idea for other reasons.) It's using the form fields as the report's underlying query's criteria that is important.

By the way, the other method from pdx_man ought to work as well. Any luck???

[This message has been edited by Chris RR (edited 02-05-2001).]

Talismanic · Feb 5, 2001

Chris RR, no luck yet but I am still trying. I am going to print your response out on paper and see if I can make some sense of it. No fault of yours, I am just a little slow when it comes to new concepts. Thanks!!!

Talismanic · Feb 6, 2001

I can not get any of the above to work but can some one help me with this. It should work if I can get the syntax correct.

Private Sub cmdReport_Click()

Dim bProcOK As Boolean
bProcOK = True

If IsNull(Me.txtEstimator) Then

MsgBox "You must select an estimator", vbExclamation, _
"Error"
txtEstimator.SetFocus
bProcOK = False

Else

If IsNull(Me.txtYear) Then
MsgBox "You must select a year", vbExclamation, _
"Error"
txtYear.SetFocus
bProcOK = False
End If
End If

If bProcOK Then

DoCmd.OpenReport "CloseOutRep", acPreview, , _
"[Estimator]='" & Me![txtEstimator] & "[SalesYear]='" & _
Me![txtYear]

End If

End Sub

Chris RR · Feb 6, 2001

Just offhand, I'd change your selection to look like this:
"[Estimator]='" & Me![txtEstimator] & "' [SalesYear]='" & _
Me![txtYear] & "'"

(adding a single quote followed by a space before [SalesYear] and adding the last single quote mark)
Good luck.

pdx_man · Feb 7, 2001

Try this

Private Sub cmdReport_Click()

Dim whereStmt As String

whereStmt = ""

If Not IsNull(Me.txtEstimator) Then
whereStmt = "[Estimator] = " & Me.txtEstimator & " AND "
End If
If Not IsNull(Me.txtYear) Then
whereStmt = whereStmt & "[SalesYear] = " & Me.txtYear & " AND "
End If

whereStmt = Left(whereStmt, Len(whereStmt) - 5)

DoCmd.OpenReport "CloseOutRep", acViewPreview,, whereStmt

End Sub

[This message has been edited by pdx_man (edited 02-06-2001).]

Talismanic · Feb 7, 2001

I am almost ready to give up! I can not believe how complicated this is. I wonder if there may be something else wrong the way I have it all set up.

Here is the error I am getting

Talismanic · Feb 8, 2001

Thanks everyone! I finaly got it, if you are interested this is how the open report code looks now:

If bProcOK Then

DoCmd.OpenReport "CloseOutRep", acPreview, , _
"[Estimator]='" & Me![txtEstimator] & "' AND [SalesYear]='" & _
Me![txtYear] & "'"

End If

tmanw86 · Nov 11, 2010

I am trying to follow along here and I am not having any luck. I have the same situation. A form that displays data from a query, that is filtered on mulitple unbound search field criteria. Since the query is filtered based on the unbound fields, I cannot open a report off of the query without it returning all records. My attempted code continues to return all records. My code is as follows:

Private Sub GeneratePrintableReport_Click()
varWhere = ""
If Not IsNull(Me.txtID) Then
varWhere = varWhere & "[Corrective_Action_ID] LIKE """ & Me.txtID & "*"" AND "
End If

If Not IsNull(Me.txtopen) Then
varWhere = varWhere & "[Date_opened] LIKE """ & Me.txtopen & "*"" AND "
End If

If Not IsNull(Me.txtStatus) Then
varWhere = varWhere & "[Status] LIKE """ & Me.txtStatus & "*"" AND "
End If

If Not IsNull(Me.txtprogram) Then
varWhere = varWhere & "[Program] LIKE """ & Me.txtprogram & "*"" AND "
End If

If Not IsNull(Me.txtCAM) Then
varWhere = varWhere & "[CAM] LIKE """ & Me.txtCAM & "*"" AND "
End If

If Not IsNull(Me.txtWBS) Then
varWhere = varWhere & "[WBS] LIKE """ & Me.txtWBS & "*"" AND "
End If

If Not IsNull(Me.txtComplete) Then
varWhere = varWhere & "[Estimated_Completion_Date] LIKE """ & Me.txtComplete & "*"" AND "
End If

If Not IsNull(Me.txtissue) Then
varWhere = varWhere & "[Issue] LIKE """ & Me.txtissue & "*"" AND "
End If

varWhere = Left(varWhere, Len(varWhere) - 5)

DoCmd.OpenReport "CorrectiveActionLog", acViewReport, varWhere

As you can see I want it to return all records only when search boxes are left blank. PLEASE HELP!

boblarson · Nov 11, 2010

You're missing an extra comma:

Code:

DoCmd.OpenReport "CorrectiveActionLog", acViewReport, [COLOR=red][B],[/B][/COLOR] varWhere

tmanw86 · Nov 11, 2010

Thank you. Unfortunately, now the report is returning no records whatsoever.

boblarson · Nov 11, 2010

Set a breakpoint and see exactly what varWhere is returning before it is used in the DoCmd.

Make sure the construct looks right when you get to that point.

tmanw86 · Nov 11, 2010

I am fairly new with Access and I am not sure I know how to do this. How do I veryify what varWhere is returning via breakpoints

tmanw86 · Nov 11, 2010

I set a breakpoint and typed in ?varwhere in the immediate window and recieved this:

[Corrective_Action_ID] LIKE "*" AND [Date_opened] LIKE "*" AND [Status] LIKE "*" AND [Program] LIKE "*" AND [CAM] LIKE "kearl*" AND [WBS] LIKE "*" AND [Estimated_Completion_Date] LIKE "*" AND [Issue] LIKE "*" AND

tmanw86 · Nov 11, 2010

I have made some progress, however, I am getting runtime error 3075 when there is more than one search field populated. Below is my code for building the filter and openng the report.

Private Sub GeneratePrintableReport_Click()
Dim varWhere As Variant

varWhere = Null

' Check for CAM
If Me.txtCAM > "" Then
varWhere = varWhere & "[CAM] LIKE '" & Me.txtCAM & "'"" AND "
End If

' Check for WBS
If Me.txtWBS > "" Then
varWhere = varWhere & "[WBS] LIKE '" & Me.txtWBS & "'"" AND "
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 6)
End If
End If
DoCmd.OpenReport "CorrectiveACtionLog", acViewReport, , varWhere

It works great if only one of the text boxes has data in them but when both criteria are populated, it returns the 3075 syntax error. Please help!

RafaelBarreto · Aug 3, 2011

Hi:
I need some help. I have built the where condition but it says it has a sintax error:
My code
WhereConditionTxt = "[Nombrada.N°] = " & N°Form & " And [Nave] = " & Nave & _
" And [Fecha] = " & Fecha & " And [Lugar] = " & Lugar & _
" And [Turno Trabajado] = " & Turno

Thanks in advance
Rafael

pdx_man said:
Yes,
Build your WHERE statement before getting to the Open command.

:
WHEREStmt = ""
if not isnull(me.txtEstimator) then
WHEREStmt = "[Estimator] = " & me.txtEstimator & " AND "
if not isnull(me.txtLocator) then
WHEREStmt = WHEREStmt & "[Locator] = " & me.txtLocator & " AND "
if not isnull(me.txtYear) then
WHEREStmt = WHEREStmt & "[Year] = " & me.txtYear & " AND "

'Get rid of trailing AND statement
WHEREStmt=left(WHEREStmt,len(WHEREStmt) - 5)

DoCmd.OpenReport stDocName, acPreview, WHEREStmt
:

Open report based on multiple criteria

Registered User.

Registered User.

Registered User.

Just trying to help

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Just trying to help

Registered User.

Registered User.

New member

Smeghead

New member

Smeghead

New member

New member

New member

New member

Similar threads

Users who are viewing this thread