Query Criteria Not Working

E

epear56

Joined
Apr 17, 2014
Messages
3
Reaction score
0
Hi - I'm trying to create a report in Access 2010 based on filters chosen by the user. Three of the five filters work, meaning if the user selects one of the options (via a drop down) the filter is applied and if they don't select anything no filter is applied. The code that works is as follows:

In the query criteria box: Like IIf(FSSFilter()="1","*",FSSFilter())

Code for Function:
Dim strFSSFilter As String

If Nz(Forms![frmReportFilters]![cbFSSFilter].Value <> "") Then
strFSSFilter = (Forms![frmReportFilters]![cbFSSFilter].Value)
Else
strFSSFilter = "1"
End If

FSSFilter = Nz(strFSSFilter)

The other two filters I'm trying to get to work are Office and Job Level.

Like IIf(OfficeFilter()="1","*",OfficeFilter())

Function OfficeFilter()
Dim strOfficeFilter As String

If Nz(Forms![frmReportFilters]![cbOfficeFilter].Value <> "") Then
strOfficeFilter = (Forms![frmReportFilters]![cbOfficeFilter].Value)
Else
strOfficeFilter = "1"

End If

OfficeFilter = Nz(strOfficeFilter)

End Function

Then for the Job Level Filter:

Function JobLevelFilter()
Dim strJobLevelFilter As String

If Nz(Forms![frmReportFilters]![cbJobLevelFilter].Value <> "") Then
strJobLevelFilter = (Forms![frmReportFilters]![cbJobLevelFilter].Value)
Else
strJobLevelFilter = "1"
End If

JobLevelFilter = Nz(strJobLevelFilter)

End Function

When I try to use the Office and / or Job Level Filter (as is) they show all the records on the report - so they don't filter anything out.

I'd really appreciate your help. I've been driving myself crazy with this for the last several days and can't figure out how to fix it.

Please let me know if you need other information.

Thanks!
M
 
Nevermind - now none of the filters are working although I didn't touch the working filters code...
 
I figured it out. The problem was in combining the SQL logical statement with the VBA. I ended up entering the following code in the query criteria and it worked. By creating two iif statements I was able to separate the SQL and VBA enough that Access could read it.

My edited code: Like IIf(FSSFilter()="1","*") Or IIf(FSSFilter()<>"1",FSSFilter())
 
Back
Top