|
Subject:
|
removing Null values from linkcriteria
|
|
Posted By:
|
aciras
|
Post Date:
|
8/24/2006 10:13:39 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.
|
|
Reply By:
|
BrianWren
|
Reply Date:
|
8/24/2006 12:25:51 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: 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.
|
|
Reply By:
|
aciras
|
Reply Date:
|
8/25/2006 8:08:54 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
|
|
Reply By:
|
aciras
|
Reply Date:
|
8/29/2006 1:19:15 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.
|
