OpenReport where condition and wildcards

marysilvaramos

Registered User.
Local time
Today, 11:28
Joined
Oct 22, 2012
Messages
25
Hello to All:

I am trying to filter a report using a form. The form has the following combo boxes (until now) to filter the report:

txtSchool Year (ie: 2012-2013)
numQuarter (ie: 1,2,3,4)
txtStudent Name (ie: Doe, jane)

Now, the search options include all the fields, one or more fields or none of them (this option should print all the info in the table). I do not have issues when all the fields have data, the problem starts when one or more are empty. I am trying to use wildcards to include all the records in case the field is empty, but the syntax some how is not ok.

Here is my current code:

strWhereQuarter = IIf(IsNull([Forms]![ReportCardFilterForm]![Quarter]), "Quarter = #", "Quarter = [Forms]![ReportCardFilterForm]![Quarter]")
strWhereSchoolYear = "txtSchoolYear = [Forms]![ReportCardFilterForm]![txtSchoolYear]"

DoCmd.OpenReport "ReportCardsUnion", acViewReport, , strWhereQuarter and strWhereSchoolYear

Thanks in advance,

Mary
 
Have the reports recordsource be a query, using DAO and VBA, modify the query's SQL string as appropriate, then open the form.
 
I'm going to assume that quarter is on the form that is running this code. If it is not, go back to the other nomenclature.
Code:
Dim strWhereQuarter as Variant
Dim strWhereSchoolYear as String
strWhereQuarter = IIf(Me.[Quarter] & "" = "", null, "Quarter = '" & Me.[Quarter] & "")
strWhereSchoolYear = "txtSchoolYear = '" & Me.[txtSchoolYear] & "'"
DoCmd.OpenReport "ReportCardsUnion", acViewReport, , strWhereQuarter + " AND " & strWhereSchoolYear

You needed to get the "and" inside the where string but only if it was necessary. I made the Quarter criteria a variant because I set it to null in the IIf() if the field was empty. I also used both the + and the more standard & as concatenation operators in the final expression. When two strings are concatenated with the + and one of them is null, null is returned so that will eliminate both the variable and the "AND" I concanated to it. Then the rest of the expression is built by concatenating with the ampersand. This is the standard concatenation character and if one of the operands is null, it will return a ZLS and concatenate that with the other operand.
 
llkhoutx Thanks for your reply but i did not understand what your mentioned.

ok Pat! it worked wonderful. now I need to add the student name. I have the following:

strWhereQuarter = IIf(Me.[Quarter] & "" = "", Null, "Quarter = '" & Me.[Quarter] & "'")
strWhereSchoolYear = "SchoolYear = '" & Me.[txtSchoolYear] & "'"
strStudentName = IIf(Me.[StudentName] & "" = "", Null, "StudentName = '" & Me.[txtStudentName] & "'")

DoCmd.OpenReport "ReportCardsUnion", acViewReport, , strStudentName + " AND " + strWhereQuarter + " and " & strWhereSchoolYear

as you said the + eliminate the studentname field if quarter field is null. how add the third field in the list? I still do no understand much about how to play with these concatenation operators.

thanks again,

mary
 
DoCmd.OpenReport "ReportCardsUnion", acViewReport, , strStudentName + " AND " + strWhereQuarter + " and " & strWhereSchoolYear & " and " & strStudentName

Print the concatenated string in the immediate window so you can see what you are building. It isn't always easy to get the + to handle optional arguments so if you can't work out when you need the + and when you neee the &, you can do it with If statements. You can also use parentheses to control the order of operation (just as you do with math and logic operations) so that you specify which arguments are combined with the + and which with the &.
 
Thanks Pat again! I am working on it! I noticed that the field Me.[StudentName] (combo box) is empty in the form but always shows in the debugger the first Student Name in the list. So, when the line strStudentName = IIf(Me.[StudentName] & "" = "", Null, "StudentName = '" & Me.[txtStudentName] & "'") runs, some how make strStudentName="". Is there any way to make the Me.[StudentName] field shows null or "" when it is not nothing in it?

attached is the debugger screen

Thanks again,

Mary
 

Attachments

  • screen.jpg
    screen.jpg
    67.2 KB · Views: 342
Is the combo bound? It shouldn't be. If the combo has a value, someone put it there. Perhaps there is a default defined on the form. If so, remove it.
 

Users who are viewing this thread

Back
Top Bottom