Search Form Code

[DeanRowe]

Hi,

I have built a search form where I use various checkboxes and combo boxes to filter the data that is shown on the continuous form.

However I am struggling with the code for the following instance…

I have a field called [Invoice ID]. I also have a check box called “MatchedFilter” . If “MatchedFilter” is checked/true then I want to only show records that have an empty “Invoice ID” field. And if it is unchecked/false then I want to only show records that have data in the “Invoice ID” field.

I’ve tried the following code but I think I’m barking up the wrong tree and have it completely wrong:

If Me.MatchedFilter = True Then
        strWhere = strWhere & "(is null ([Invoice ID])) AND "
    ElseIf Me.MatchedFilter = False Then
        strWhere = strWhere & "(is not null ([Invoice ID])) AND "
    End If

I've also tried this code but to no avail:

If Me.MatchedFilter = True Then
        Me.Invoice_ID = "Invoice_ID is null"
    ElseIf Me.MatchedFilter = False Then
        Me.Invoice_ID = "Invoice_ID is not null"
    End If

Could anyone help me with this code please?

Thank you

 

[Dreamweaver]

Try "HAVING" instead of "WHERE" "[Invoice ID] Is Null"

 

[DeanRowe]

Thanks for replying Dreamweaver, I couldn't get the HAVING code to work however I've just managed to crack it with the following code:

If Me.MatchedFilter = True Then
strWhere = strWhere & "([Invoice ID]) Is Null AND "
ElseIf Me.MatchedFilter = False Then
strWhere = strWhere & "([Invoice ID]) Is Not Null AND "
End If