Query syntax question. (1 Viewer)

[KelMcc]

I have 3 nearly identical queries running that, in turn, are ran by 3 nearly identical reports.

Everything is the same in regards to what fields/data to pull for my report. The only difference in the reports is which query they call.

The queries SELECT, FROM, and JOIN statements are exactly the same. The only difference is in the WHERE statement. The difference is that they each query the data differently, though the criteria uses the same table field. In one query I'm asking for info NOT equal to a specific term, the 2nd asks for info EQUAL to the term, and the 3rd ignores it (returns all info)

Here's the string, the section my question refers to is in bold.

WHERE (((SRComment.Date) Between [Forms]![FISReports]![From Date] And [Forms]![FISReports]![To Date]) AND ((SR.SRType)="Project")]);

My question is, what is the syntax to have that bolded part refer to a combo box on a form? That way I will need only 1 query and only 1 report as the selection will be made in the combo box.

I know this is convuluted. I hope some of you can make sense and help me out.

Thanks!

 

[pdx_man]

Try this:
Leave this criteria on the query

WHERE (((SRComment.Date) Between [Forms]![FISReports]![From Date] And [Forms]![FISReports]![To Date])

I am assuming that you are running the report by clicking a button on the form ... so, add a filter to the open report call.
There should be a qualifier for ALL, Equal, Not Equal, perhaps an Option control. Then build your filter string:

(Assuming a Option Group called optQualify and a Text Box called tbTerm.)

Select Case Me.optQualify
    Case 0: FilterStr = ""
    Case 1: FilterStr = "SR.SRType = '" & [Forms]![FISReports]![tbTerm]
    Case 2: FilterStr = "SR.SRType <>'" & [Forms]![FISReports]![tbTerm]
End Select

DoCmd.OpenReport "Rpt_Name", acPreview, , FilterStr

HTH

 

[KelMcc]

Questions:

Does the "Case" have to correspond to the values of my option group? (which are 1, 2, 3, not 0, 1, 2).

I wasn't sure what the purpose of the textbox was. I'm guessing this is where it gets the value to filter on? You suggested an Option Group, which I called "SRtypeopt". It has 3 choices, the values are 1, 2, and 3, which correspond to displayed text of All, Projects, and non-Projects.

I put this all together and the report gives me a "variable not defined error" and hangs on this: "FilterStr", which is the first time the code sees that.

Here's the whole thing, ATM.
------------
Private Sub RRPMIAll_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RPMI"

Select Case Me.SRtypeopt
Case 1: FilterStr = ""
Case 2: FilterStr = "SR.SRType = '" & [Forms]![FISReports]![Project]
Case 3: FilterStr = "SR.SRType <>'" & [Forms]![FISReports]![Project]
End Select

If Not IsNull(Forms![FISReports].BA) And Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "([Status].TOpCl) = '" & Me.Status & "' And[BA] = '" & Me.BA & "'"
ElseIf 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, FilterStr, , stDocWhere

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click
------

Thanks for your patience.

 

[KelMcc]

*bump*

 

[pdx_man]

I forgot to include the closing apostrophe


Select Case Me.SRtypeopt
Case 1: FilterStr = ""
Case 2: FilterStr = "SR.SRType = '" & [Forms]![FISReports]![Project]
Case 3: FilterStr = "SR.SRType <>'" & [Forms]![FISReports]![Project]
End Select

Select Case Me.SRtypeopt
Case 1: FilterStr = ""
Case 2: FilterStr = "SR.SRType = '" & [Forms]![FISReports]![Project] & "'"
Case 3: FilterStr = "SR.SRType <>'" & [Forms]![FISReports]![Project] & "'"
End Select

Give this a try ....

 

[KelMcc]

I added the "'", but the code never gets that far. Its hangs the first time it sees "FilterStr" and gives me "Compile Error: Variable not defined"

 

[RV]

Dim FilterStr As String ?

RV

 

[KelMcc]

I shoulda made it clear (I usually say this w/ my other posts) that I'm just learning "code" and what all the syntax is, so you have to be very specific with me.

I did add the line that RV said and this is what I have now:

---------

Dim stDocName As String, stDocWhere As String
Dim FilterStr As String

stDocName = "RPMI"

Select Case Me.SRtypeopt
Case 0: FilterStr = ""
Case 1: FilterStr = "SR.SRType = '" & [Forms]![FISReports]![Project] & "'"
Case 2: FilterStr = "SR.SRType <>'" & [Forms]![FISReports]![Project] & "'"
End Select

If Not IsNull(Forms![FISReports].BA) And Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "([Status].TOpCl) = '" & Me.Status & "' And[BA] = '" & Me.BA & "'"
ElseIf 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, FilterStr, , stDocWhere
-----------------

I now get this error: "Compile Error: Wrong number of arguments or invalid property assignment" and that last line, the "DoCmd.OpenReport" is highlighted.

I'm hoping its just some n00b syntax that I don't know.

Thanks again.

 

[pdx_man]

It looks like we are getting the FILTER and the WHERE clauses confused. Use the WHERE clause. If you look up the OpenReport Help topic, and look at my initial post, you will notice that I am leaving the Filter part blank. I think my error was in the variable name FilterStr that threw you off. Sorry. Concatonate any additional criteria to the string you are building in the initial CASE statement.

Dim stDocName As String, stDocWhere As String 

stDocName = "RPMI" 

Select Case Me.SRtypeopt 
  Case 0: stDocWhere = "" 
  Case 1: stDocWhere = "SR.SRType = '" & [Forms]![FISReports]![Project] & "' AND " 
  Case 2: stDocWhere = "SR.SRType <>'" & [Forms]![FISReports]![Project] & "' AND " 
End Select 

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

End If 

DoCmd.OpenReport stDocName, acPreview,  , stDocWhere

Let's try this ...

 

[KelMcc]

PDX, Thanks it runs w/out error, but is not returning me anything.

At this point, I'm sure its just me not knowing/understanding a couple things. May I ask a couple conceptual questions that will help me maybe figure this out?

In the whole "case" section, do the 0, 1, 2 correspond to the values of the option group? Option Groups only allow numeric data. If so, the text of "project", you originall generically called that tbtext. I assumed this to be the text accompanying the Option Group box selection. But, I think that is a faulty assumption. What is this text box supposed to be?

 

[KelMcc]

*bump*

 

[pdx_man]

The text box is where you would put your criteria. So, if you wanted information relating to, say, a color:

You would put in the tbtext would be 'Orange'
Then the Option Group would have the 3 selections:
Equals
Not Equal
All

So then, depending on which option selected, you would get everything that was Orange, everything NOT Orange or all colors.

Changing the value in tbtext allows you to query for different things.

 

[KelMcc]

Understood. Thanks PDX!