Report Filtering Issue

[Vaulcul]

Hello,

I have a report that is supposed to show the teachers that have taught specific classes this year. I'm almost done, I just need the report to filter the data before opening.

In setting the button that opens the report to filter the data I'm being asked for input for my Fall, Spring, and Summer... which shouldn't happen. If I add a single quote to those terms, I no longer am prompted for input, but am told that my expression is too complex.

I'm not seeing what I'm doing wrong with my code. Any help is appreciated.

 
pyer = ([Forms]![MENMain3]![NavigationSubform].[Form]![SYr]) - 1
    yer = [Forms]![MENMain3]![NavigationSubform].[Form]![SYr]
    nyer = ([Forms]![MENMain3]![NavigationSubform].[Form]![SYr]) + 1
 
 
    DoCmd.OpenReport "QuickScheduleCourse", acPreview, , "[SemesterYear]=" & ((pyer & " Or " & yer & " Or " & nyer) & " AND " & "[Semester]=" & "Fall") & " or " & "[SemesterYear]=" & ((pyer & " Or " & yer & " Or " & nyer) & " AND " & "[Semester]=" & "Spring") & " or " & "[SemesterYear]=" & ((pyer & " Or " & yer & " Or " & nyer) & " AND " & "[Semester]=" & "Summer")
 
 

[PhilOfWalton]

I think ...note the word think that you are missing quotes round the seasons.

Substitute "[Semester]=" & "Fall") with
"Semester = " & Chr$(34) & "Fall" & Chr$(34)
Do something similar for the other seasons

Incidentally, you don't need the square brackets.
Phil

[mbizup]

The above comment is correct... you need to delimit any text data with quotes.

Additionally, your VBA is gong to result in SQL criteria including conditions along the lines of:

SemesterYear = (Thie OR That OR SomethingElse)

This syntax will fail in SQL. You have two options for a list of ORs:

1. Spell it out as separate comparisons like this. Also note the parentheses to separate a list of ORs from ands. The hierarchy of operations evaluates ANDs before ORs (similar to multiplication and addition, respectively), so you need to use parentheses to ensure that the ORs are grouped together.

(SemesterYear = This OR SemesterYear = That OR SemesterYear = SomethingElse) AND Semester='Fall'

2. The other option is to use the IN keyword with a comma delimited list. Pro: the syntax is simpler. Con: You might see a noticeable performance hit.

SemesterYear IN (This, That, SomethingElse) AND Semester= 'Fall'

Additional tips:
1. Work out your query syntax, including criteria in the query editor first, and then put it into VBA.

2. Use a SQL string to build your criteria, and use that string in your report open statement. That makes for cleaner code, and more importantly allows you to view and test your criteria (very helpful when debugging):

Dim strSQL as string
strSQL = "[SemesterYear]=" ' etcetera
DoCmd.OpenReport "QuickScheduleCourse", acViewPreview, strSQL
" the following line will print your criteria into the editor's immediate window, allowing you to copy/paste/test/debug it in the query editor
Debug.Print strSQL