VBA If textbox is empty display all values

Question

I have 2 textboxes and a button. The button opens a query with the values of the textboxes. Everything works fine, except when one textbox is empty then I want to find all the records from that textbox(So far I've tested it on textbox2). Here is my code:

Private Sub vbaBtn_Click()

Dim af1 As String, af2 As String
 af1 = "([a1]='" + [krit1] + "') "

If krit2 = "" Then
 af2 = "([a2]='" + "*" + "') "
Else
 af2 = "([a2]='" + [krit2] + "') "
End If

DoCmd.OpenForm "FormLisategevusalad", acFormDS, , af1 & " " & "And" & " " & af2

End Sub

It seems to me, that VBA is always choosing the Else option not the If one, since when I debugged it said the error is an "Invalid use of null" and pointed to the Else condition.

Am I using a wrong method to detect and empty string? If I get the empty string recognition to work, is this af2 = "([a2]='" + "*" + "') " the right way to return all records?

Thanks in advance.

In table design, have you set to'No' the field 'Allow Zero Length'. krit2 might be Null, not "". You can google Zero Length Strings or look at this: https://answers.yahoo.com/question/index?qid=20070128045303AAZu8fM. I don't guarantee this is the answer, just something to be clear on. — , Feb 28 '15 at 13:13
@user2204315 Hello, thank You for the reply it gave me an idea. Although at the moment it wont help me sadly, cause i have zero-length values in table2. I will try different workarounds. — Renet, Feb 28 '15 at 13:45

score 1 · Accepted Answer · answered Feb 28 '15 at 16:10

An empty textbox holds the value of Null, thus concatenating this with + results in a Null. Use &:

Private Sub vbaBtn_Click()

    Dim af1 As String, af2 As String

    af1 = "([a1]='" & [krit1] & "') "

    If IsNull([krit2]) Then
        af2 = "([a2] Is Not Null) "
    Else
        af2 = "([a2]='" & [krit2] & "') "
    End If

    DoCmd.OpenForm "FormLisategevusalad", acFormDS, ,af1 & " And " & af2

End Sub

VBA If textbox is empty display all values

1 Answers1