(Solved) Open a report in Access base on multiple criteria?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
I have a report that opens based on a combo box. The combo box is based on a query and is limited to the list. Basically you select the estimator's name in the text box and press a button to open the report with the related data. Here is the code I am using:

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 any criteria would have to be true for the report to display data, which shouldn't be a problem because of limiting the selections to the list in the combo box.

So if this is possible all I will need is a way to make Access assume all records should be displayed when one criterion or another is not chosen.

Any ideas?


------------------
Building the Ultimate site list for PC support.
 

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
I may have this solved now so don't spend any time trying to figure it out. I will post the code here if it works or if I need any more assistance, Thanks!

------------------
Building the Ultimate site list for PC support.
 

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
Ok I am very stuck, please help! This is what I have so far and I think this will work if I can get the contencantation for the strings correct.

Here is what I have:

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

------------------
Building the Ultimate site list for PC support.

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

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
I have also been shown this alternative way to do it but this is pulling everything regardless of estimator or sales year.

Dim whereStmt As String
whereStmt = ""
If Not IsNull(Me.txtEstimator) Then
whereStmt = "[Estimator] = '" & Me.txtEstimator & "'"
If Not IsNull(Me.txtYear) Then
whereStmt = whereStmt & "[SalesYear] = '" & Me.txtYear & "'"
whereStmt = Left(whereStmt, Len(whereStmt) - 5)
End If
End If
DoCmd.OpenReport "CloseOutRep", acViewPreview, whereStmt
End Sub

------------------
Building the Ultimate site list for PC support.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Here's the "correction" I got from blakeh:

Private Sub cmdReport_Click()

Dim bProcOK As Boolean
bProcOK = True

If IsNull(Me.txtEstimator.value) Then

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

Else

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

If bProcOK Then

DoCmd.OpenReport "CloseOutRep", acPreview, , _
"[Estimator]='" & Me![txtEstimator].value & "'
[SalesYear]='" & Me![txtYear].value & "'"
'if SalesYear is a number field then it should be
DoCmd.OpenReport "CloseOutRep", acPreview, , _ "[Estimator]='" &
Me![txtEstimator].value & "' [SalesYear]=" & Me![txtYear].value
End If

End Sub

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
Thanks Dreamboat, I can not believe how much trouble this has giving me. I am wondering if I should try something completely different now and see if I have better luck.

I have a thread similar to this one on another board with completely different code and am getting the same results. The error is not exact but it is close.

Here is the error


When I choose debug it is highlighting this part of the code:

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

The year is more of a string then it would be a number because the choices are pulled from a value list and there are only four choices 2001;2000,1999,1998.


------------------
Building the Ultimate site list for PC support.
 

YSB

Joined
Mar 7, 1999
Messages
779
Hi! It looks like Dreamboat has performed a successful resurrection!
(Thanks for the tip!
)

Anyway, the correct syntax for what you are trying to do is this:

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

If SalesYear is numeric then it would be this:

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

REMEMBER! The Where part of OpenForm/Report is like the Where clause of an SQL statement and must use SQL syntax. When using two conditions in SQL they should be combined with AND or OR.

As far as leaving a combobox out of the Where clause if it's empty, there might be a one-line way to do it but off hand I can only think of a slightly longer way. It would look something like this:

Dim WhereClause as String

WhereClause = "[Estimator]='" & Me![txtEstimator] & "'"

If Not IsNull(txtYear) then

'for string
WhereClause = WhereClause & " AND [SalesYear]= '" & Me![txtYear] & "'"

'for numeric
WhereClause = WhereClause & " AND [SalesYear]= " & Me![txtYear]

End If

DoCmd.OpenReport "CloseOutRep", acPreview, , WhereClause


This way the additional condition will only be added if there is something in the box.

By the way, it is called CONCATENATING strings.
Good Luck!


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

Talismanic

Thread Starter
Joined
May 25, 2000
Messages
409
It worked, Thank You.

Well I new it strarted with conca but the rest I left up to my imagination.

Thanks Again!!!

[This message has been edited by Talismanic (edited 02-08-2001).]
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top