Adding a 2nd (3rd really) criteria to a form and need help w/ code

[KelMcc]

This question picks up where this one left off.

I have a form, FISReports, this form calls a report, RISIF. RISIF uses a query, QISIFReport, as its record source.

On FISReports, there are 3 pieces of criteria:
1. To and From dates (to unbound boxes)
2. BA (unbound combo box, this is a list of employees)
3. Status (unbound combo box, a list of status's to choose from)

On the thread that's linked above, David R helped me get this to work where the To/From dates are in the query, QISIFReport, and the BA is part of the "event procedure". Basically if BA is unpopulated, then it picks all.

... OK, now the question. I want to add the Status to this and have it function like BA. If its blank you get all statuses, otherwise you just get what you selected.

Here's the code before I try this:
----------------
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RISIF"

If Not IsNull(Forms![FSISReports].BA) Then
stDocWhere = "[BA] = '" & Me.BA & "'"
End If

DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
------

What I don't know is the syntax to add my "status" criteria. I would assume I model it on the "BA" criteria, but do I do it as a separate "IF" statement or w/ an "AND" statement?

OK, that's my question.

 

[David R]

Try this thread for some help

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30739&highlight=strWhere

 

[Jack Cowley]

I would suggest that you use the method described in this article to filter your data....

http://support.microsoft.com/support/kb/articles/Q210/2/42.ASP?LN=EN-US&SD=gn&FR=0&qry=QBF&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

 

[KelMcc]

Cool! Helpful. I'm sure this is what I need, but I'm getting some errors no doubt from syntax.

Bold is the code I added

------
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RISIF"

If Not IsNull(Forms![FISReports].BA)
Then stDocWhere = "[BA] = '" & Me.BA & "' OR "
If Not IsNull(Forms![FISReports].Status)
Then stDocWhere = "[Status.TOpCl] = '" & Me.Status & "'"
End If

DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
-----------

I get this error:
Compile error: End if without block it

 

[KelMcc]

Jack, something w/ that link is goofed up. I click it/cutpaste it, whatever and I get a "cannot find" error.

 

[Jack Cowley]

If Not IsNull(Forms![FISReports].BA)
Then stDocWhere = "[BA] = '" & Me.BA & "'
ElseIf Not IsNull(Forms![FISReports].Status)
Then stDocWhere = "[Status.TOpCl] = '" & Me.Status & "'"
End If

 

[Jack Cowley]

The code in the article works. You will probably need to adapt it to your circumstances....

 

[KelMcc]

Jack, that article link is br0ked. Its give a "can't find" error and this is in the address line: http://query by form/

Anyway, I tried to change it to what you suggested:
--------
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RISIF"

If Not IsNull(Forms![FISReports].BA) Then stDocWhere = "[BA] = '" & Me.BA & "'"
ElseIf Not IsNull(Forms![FISReports].Status) Then stDocWhere = "[Status.TOpCl] = '" & Me.Status & "'"
End If

DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
-----

And now I get this error: Compile error "else without if"

So, in both cases, I'm sure its close, and is likely a syntax error w/ the quotes or something. I don't really know code, so I'm not sure what the cause could be.

 

[David R]

IF can be picky.

Jack, that article link is br0ked. Its give a "can't find" error and this is in the address line: http://query by form/

It looks like Jack got the descriptor and link part of the URL backwards. Try cutting and pasting what's shown, on the screen, not the 'shortcut' behind it.

Anyway, I tried to change it to what you suggested:
--------
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RISIF"

If Not IsNull(Forms![FISReports].BA) Then stDocWhere = "[BA] = '" & Me.BA & "'"
ElseIf Not IsNull(Forms![FISReports].Status) Then stDocWhere = "[Status.TOpCl] = '" & Me.Status & "'"
End If
(snip)
-----

And now I get this error: Compile error "else without if"

Move what is after the Then to a new line. Because the If and the Then clauses are on the same line, it considers it a 'different' sort of IF...Then than the If...Then...Else...End If that you want.

Consider:

IF a=b Then c=d Else c=e

IF a=b Then
  c=d
Else
  c=e
End If

Same thing, written two different ways.

I blame Billy Boy, of course.

 

[Jack Cowley]

Try this:

If Not IsNull(Forms![FISReports].BA) Then
stDocWhere = "[BA] = '" & Me.BA & "'"
ElseIf Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "[Status.TOpCl] = '" & Me.Status & "'"
End If

Try this link:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q210242

 

[KelMcc]

Thanks Jack & David. It now runs without error!

But.... it doesn't return what I'm hoping it would, so I think I've got to look at how the tables are related.

Thanks again.

 

[KelMcc]

Hmmm... It would seem that the "ElseIf" statement makes the two conditions "or"s rather than "ands". Is that correct? If so, then I need them to be "ands".

I noticed this because when I enter a BA, the report runs but doesn't seem to consider the "Status" criteria. This is also evidenced by when I leave the BA blank and the query actually hits the code that tells it to look at Status and I get this error:

Invalid bracketing of name "[Status.TOpCl]"

Status is the table that TOpCl (the status type) is cross-referenced. I've triple checked to make sure that all the names are spelled right, correct-case, spacing, etc.... and that's all good.

 

[KelMcc]

Well good for me!

I fixed the syntax for the file. I changed it to:

"([Status].TOpCl)" and the error went away!

But, this also confirmed to me that the "ElseIf" is an "or" statement. I need these conditions to be "and".....

 

[Jack Cowley]

If Not IsNull(Forms![FISReports].BA) And Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "[Status.TOpCl] = '" & Me.Status & "' And [BA] = '" & Me.BA & "'"
End If

Now you should be able to figure out the other If Statments if one or the other Combos is Null...

hth,
Jack

 

[KelMcc]

That is exactly my next challenge Jack, thanks. I'll see what I can do!