Checking for Blank text boxes in VB

[benu302000]

I have a search form set up with multiple text boxes and a search button, which pulls up another form to display the results. I do this by building a "where" statement and sending it as a parameter to the next form. In order to do this, for each portion of the "where" statement I build I have to check to see if there's anything already in the statement (I.E. wether I should concatonate "'[org code]=' & Me![Org Code] " or "and '[org code]=' & Me![Org Code] "

My problem is that when I check to see if there is already anything in the where statement, it seems to always assume that its not empty, even if it is ( I'm using IsNull() to check for empty string ). Is there a different way I should be doing this? Heres my code:



Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Search Form Result"

If Not IsNull(Me.Billet_Number) Then
stLinkCriteria = stLinkCriteria & "[Billet Number]=" & Me![Billet Number]
End If

If Not IsNull(Me.Org_Code) Then
If Not IsNull(stLinkCriteria) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[org code]=" & Me![Org Code]
End If

If Not IsNull(Me.Org_Title) Then
If Not IsNull(stLinkCriteria) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[org title]=" & Me![Org Title]
End If


DoCmd.OpenForm stDocName, , , stLinkCriteria



Thanks in advance

 

[RoyVidar]

The isnull function will always return false when used on strings, because strings cannot be Null. Try for instance something like this:

[tt]if (len(trim$(stLinkCriteria & vbnullstring))>0) then
' contains values
else
' does not contain anything
end if[/tt]

Roy-Vidar

 

[benu302000]

what is "trim$" and "vbnullstring"?

 

[PHV]

Replace this:
If Not IsNull(stLinkCriteria) Then
By this:
If Len(stLinkCriteria) > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244