stLinkCriteria and Yes/No filter

[hockeyfan21]

Hi,

I have 3 combo boxes on a form that I am filtering another form/report based on a table in VBA using stLinkCriteria
This is all working fine but I also need to put a hard filter for 2 other fields in the same table where [Approved] = 0 and [Denied]=0. My Debug.Print shows

[Category]= '2000 - RUNNING' OR [ProdtSubCatLongDesc]='2000 - RUNNING' AND [Approved] = FALSE

but it isn't actually filtering out the [Approved] entries.

VBA (1st combo box only)

If IsNull(cboDiv) And IsNull(cboGender) Then
stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "'"
stLinkCriteria2 = "[Approved] = FALSE "
stLinkCriteria3 = stLinkCriteria & " AND " & stLinkCriteria2

 Debug.Print stLinkCriteria3

any ideas on what I'm doing wrong? I've also used '0' instead of FALSE but get the same results.

Thank you!!

Toni

 

[plog]

When you mix ANDs and ORs you need to put parenthesis around the criteria that work together. I suggest 2 sets of parenthesis--1 around stLinkCriteria and 1 around stLinkCriteria2

 

[hockeyfan21]

Not quite sure I understand but here's what I tried - still showing items where Approved = TRUE

If IsNull(cboDiv) And IsNull(cboGender) Then
stLinkCriteria = ("[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "'")
stLinkCriteria2 = ("[Approved] = FALSE")
stLinkCriteria3 = stLinkCriteria & " AND " & stLinkCriteria2
 Debug.Print stLinkCriteria3

Debug.Print shows
[Category]= '2000 - RUNNING' OR [ProdtSubCatLongDesc]='2000 - RUNNING' And [Approved] = FALSE

() in the wrong place? Thank you!

 

[plog]

Yes, wrong place. They need to be part of the string, so inside the quote marks.

 

[hockeyfan21]

Genius!!! Thank you, all working great now.

If IsNull(cboDiv) And IsNull(cboGender) Then
stLinkCriteria = "([Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "')"
stLinkCriteria2 = "([Approved] = FALSE)" & " AND ([Denied]=FALSE)"
stLinkCriteria3 = stLinkCriteria & " AND " & stLinkCriteria2

Thank you so much for helping me out with this!

Toni

 

[plog]

Are you sure? It looks like you added to many sets of parenthesis. My advice was to surround the data inside each stLinkCriteria with parenthesis, not each criterion of each inside parenthesis.

 

[hockeyfan21]

Thanks, I changed the parenthesis but then just realized that it now requires a selection in every combo box, otherwise I end up with a trailing 'AND'

([Category]= '2000 - RUNNING' OR [ProdtSubCatLongDesc]='2000 - RUNNING') AND

If IsNull(cboDiv) And IsNull(cboGender) Then
stLinkCriteria = "([Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "')"

stLinkCriteria2 = "([Approved] = FALSE" & " AND" & "[Denied]=FALSE)"
End If

stLinkCriteria3 = stLinkCriteria & " AND " & stLinkCriteria2

Works as it should if I select something in all 3 combos.

 

[hockeyfan21]

Think I've figured it out - decided to go another route and put them all in one string

If IsNull(cboDiv) And IsNull(cboGender) Then stLinkCriteria = "([Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "') AND ([Approved]=FALSE AND [Denied]=FALSE) "

Thanks for your help and guidance-if you see anything in this solution that you think might cause me problems down the road, would appreciate your letting me know!