removing Null values from linkcriteria

aciras · August 24th, 2006, 10:13 AM

I have 4 textboxes for users to enter data and then search a database for those 4 criteria. However I don't know how to remove a criteria if the user does not fill it in. Here is the linkcriteria i am using, it works when all the criteria is provided:

stLinkCriteria = "[SLA Number]=" & "'" & varHIN & "'" & " And " & "[RO Number]=" & "'" & varRO & "'" & " And " & "[ISS Contact]=" & "'" & varISS & "'" & "And " & "[Client Name]=" & "'" & varClient & "'"

I tried an "IF IsNull" statement, but that would require putting data for each combination of Null and non-Null values for the 4 criteria.

Is there a wildcard function in vb? I was thinking I could assign that to a criteria if there was a null value there.

Essentially i'd like a statement like this (1, 2, 3, 4 are variables):

stlinkcriteria = 1 and 2 and 3 and 4
ONLY IF 1, 2, 3, 4 are NOT null values.

BrianWren · August 24th, 2006, 12:25 PM

Create an array of strings for each criterion, or a separate variable for each criterion.
If the textbox for a given criterion is empty, do nothing. (Strings initialize to "".)

Then just concatenate them all in your statement:

Code:

    stLinkCriteria = Var1 & var2 & var3 & var4
    ' Or
    stLinkCriteria = Var(1) & var(2) & var(3) & var(4)

Alternately, you could have 4 If statements, each of which contributes one criterion only if the textbox for that criterion is not empty.

aciras · August 25th, 2006, 08:08 AM

Thanks for the reply, i'm not 100% sure how to do what you said, here is my attempt:

    Dim stHIN As String 'variable assigned to criteria 1

    If Me.txtHIN <> "" Then
        varHIN = Me.txtHIN
    End If

I had one of those for each criteria, didn't work though. Maybe i'm not joining the variables correctly:

    stLinkCriteria = "[SLA Number]=" & "'" & stHIN & "'" & " and " & "[RO Number]=" & "'" & stRO & "'" & " and " & "[ISS Contact]=" & "'" & stISS & "'" & " and " & "[Client Name]=" & "'" & stClient & "'"

I can bring up the correct record using the above line as long as ALL the variables are there, as soon as one is missing the record cannot be found.

thanks

aciras · August 29th, 2006, 01:19 PM

Still unable to figure this out, any help would be greatly appreciated.

4 text boxes
user enters data, then clicks button to search records
based on the 4 text boxes, records are shown in a seperate form

everything works as long as ALL 4 text boxes are filled...i want to basically bypass a text box from being included in the "linkcriteria" command if it is empty...

thanks.