"select case"

G

Guest

Hi Community...Please help me with a VBA...I have a search form with 4
unbound fields and an unbound listbox. The user inputs data into one or more
of the fields and the search results are shown in the list box. But there is
a problem. Please help me rectify the VBA to solve it.

When I do a search on the first field (number - Main_Pt_ID) by itself, it
works OK....When I do a search on the last field (Test - Name Field - Lname)
by itself, it works OK....But when I do a search on the second field (number
- Arthur_ID) by itself, nothing happens. Same with the third Field (Date -
DOB)....But the 2nd and 3rd fields work beautifully if used in conjunction
with the First field only....Please help me fix this glitch:

Dim sSearch As String
Dim sql As String
Dim strSQL() As String

If IsNull(Me.sMain_Pt_ID) = True And IsNull(Me.sArthur_ID) = True And
IsNull(Me.sDOB) = True And IsNull(sLname) = True Then
MsgBox "You must enter a search value", vbOKOnly
Exit Sub
End If

'/build the search string
If IsNull(sMain_Pt_ID) = False Then
sSearch = "[Main_Pt_ID]=" & Me.sMain_Pt_ID
End If


If IsNull(sArthur_ID) = False Then
If IsNull(sSearch) = False Then
sSearch = sSearch & " Or [Arthur_ID]=" & Me.sArthur_ID
Else
sSearch = "[Arthur_ID]=" & Me.sArthur_ID
End If
End If

If Not IsNull(Me.sDOB) Then
If IsNull(sSearch) = False Then

sSearch = sSearch & " Or [DOB]= #" & Me.sDOB & "#"

Else
sSearch = "[DOB]= #" & Me.sDOB & "#"

End If
End If

'Dim strSQL() As String
If Len(Nz(Me.sLname, "")) > 0 Then
strSQL() = Split(Me.sLname & ",,", ",")
strSQL(0) = Trim(strSQL(0))
strSQL(1) = Trim(strSQL(1))
If Len(strSQL(0)) > 0 Then
strSQL(0) = "([LName] = '" & strSQL(0) & "' "
If Len(strSQL(1)) > 0 Then
strSQL(0) = strSQL(0) & _
" AND [Fname] LIKE '" & strSQL(1) & "*' " ' <- Change * to %
End If
strSQL(0) = strSQL(0) & ") "
If Len(Nz(sSearch, "")) = 0 Then
sSearch = strSQL(0)
Else
sSearch = sSearch & " OR " & strSQL(0)
End If
End If
End If



'MsgBox sSearch

sql = "SELECT Main_Pt_ID, Arthur_ID, FName, LName, DOB, Pt_Type,
Head_Fam_member_ID FROM Tbl_Main_Pt WHERE " & sSearch

Me.ListFind.RowSource = sql
 
G

George Nicholson

Dim sSearch As String

Note: sSearch initializes as an empty string (""), not Null. Therefore,
If IsNull(sSearch) = False Then.....
sSearch = sSearch & " Or ...."
Click to expand...

The above test will *always* be False. A string variable can't ever be Null
(at least in the version of VB we're using here). Only Variants variables
can be Null, and a Field can contain a Null value.

Pretty sure this is why your code works & doesn't work in the combinations
you site: whenever field 2 or 3 "starts" sSearch, sSearch is beginning with
" OR..", which it shouldn't, generating no results. When field 2 or 3
"follows" field 1, the "OR" clause is appropriate and works.

Better:
If Len(sSearch) > 0 Then .....


HTH,


Robbhat said:
Hi Community...Please help me with a VBA...I have a search form with 4
unbound fields and an unbound listbox. The user inputs data into one or
more
of the fields and the search results are shown in the list box. But there
is
a problem. Please help me rectify the VBA to solve it.

When I do a search on the first field (number - Main_Pt_ID) by itself, it
works OK....When I do a search on the last field (Test - Name Field -
Lname)
by itself, it works OK....But when I do a search on the second field
(number
- Arthur_ID) by itself, nothing happens. Same with the third Field (Date -
DOB)....But the 2nd and 3rd fields work beautifully if used in conjunction
with the First field only....Please help me fix this glitch:

Dim sSearch As String
Dim sql As String
Dim strSQL() As String

If IsNull(Me.sMain_Pt_ID) = True And IsNull(Me.sArthur_ID) = True And
IsNull(Me.sDOB) = True And IsNull(sLname) = True Then
MsgBox "You must enter a search value", vbOKOnly
Exit Sub
End If

'/build the search string
If IsNull(sMain_Pt_ID) = False Then
sSearch = "[Main_Pt_ID]=" & Me.sMain_Pt_ID
End If


If IsNull(sArthur_ID) = False Then
If IsNull(sSearch) = False Then
sSearch = sSearch & " Or [Arthur_ID]=" & Me.sArthur_ID
Else
sSearch = "[Arthur_ID]=" & Me.sArthur_ID
End If
End If

If Not IsNull(Me.sDOB) Then
If IsNull(sSearch) = False Then

sSearch = sSearch & " Or [DOB]= #" & Me.sDOB & "#"

Else
sSearch = "[DOB]= #" & Me.sDOB & "#"

End If
End If

'Dim strSQL() As String
If Len(Nz(Me.sLname, "")) > 0 Then
strSQL() = Split(Me.sLname & ",,", ",")
strSQL(0) = Trim(strSQL(0))
strSQL(1) = Trim(strSQL(1))
If Len(strSQL(0)) > 0 Then
strSQL(0) = "([LName] = '" & strSQL(0) & "' "
If Len(strSQL(1)) > 0 Then
strSQL(0) = strSQL(0) & _
" AND [Fname] LIKE '" & strSQL(1) & "*' " ' <- Change * to
%
End If
strSQL(0) = strSQL(0) & ") "
If Len(Nz(sSearch, "")) = 0 Then
sSearch = strSQL(0)
Else
sSearch = sSearch & " OR " & strSQL(0)
End If
End If
End If



'MsgBox sSearch

sql = "SELECT Main_Pt_ID, Arthur_ID, FName, LName, DOB, Pt_Type,
Head_Fam_member_ID FROM Tbl_Main_Pt WHERE " & sSearch

Me.ListFind.RowSource = sql
Click to expand...
 
G

Guest

George Nicholson said:
Dim sSearch As String
Click to expand...

Note: sSearch initializes as an empty string (""), not Null. Therefore,
If IsNull(sSearch) = False Then.....
sSearch = sSearch & " Or ...."
Click to expand...

The above test will *always* be False. A string variable can't ever be Null
(at least in the version of VB we're using here). Only Variants variables
can be Null, and a Field can contain a Null value.

Pretty sure this is why your code works & doesn't work in the combinations
you site: whenever field 2 or 3 "starts" sSearch, sSearch is beginning with
" OR..", which it shouldn't, generating no results. When field 2 or 3
"follows" field 1, the "OR" clause is appropriate and works.

Better:
If Len(sSearch) > 0 Then .....


HTH,


Robbhat said:
Hi Community...Please help me with a VBA...I have a search form with 4
unbound fields and an unbound listbox. The user inputs data into one or
more
of the fields and the search results are shown in the list box. But there
is
a problem. Please help me rectify the VBA to solve it.

When I do a search on the first field (number - Main_Pt_ID) by itself, it
works OK....When I do a search on the last field (Test - Name Field -
Lname)
by itself, it works OK....But when I do a search on the second field
(number
- Arthur_ID) by itself, nothing happens. Same with the third Field (Date -
DOB)....But the 2nd and 3rd fields work beautifully if used in conjunction
with the First field only....Please help me fix this glitch:

Dim sSearch As String
Dim sql As String
Dim strSQL() As String

If IsNull(Me.sMain_Pt_ID) = True And IsNull(Me.sArthur_ID) = True And
IsNull(Me.sDOB) = True And IsNull(sLname) = True Then
MsgBox "You must enter a search value", vbOKOnly
Exit Sub
End If

'/build the search string
If IsNull(sMain_Pt_ID) = False Then
sSearch = "[Main_Pt_ID]=" & Me.sMain_Pt_ID
End If


If IsNull(sArthur_ID) = False Then
If IsNull(sSearch) = False Then
sSearch = sSearch & " Or [Arthur_ID]=" & Me.sArthur_ID
Else
sSearch = "[Arthur_ID]=" & Me.sArthur_ID
End If
End If

If Not IsNull(Me.sDOB) Then
If IsNull(sSearch) = False Then

sSearch = sSearch & " Or [DOB]= #" & Me.sDOB & "#"

Else
sSearch = "[DOB]= #" & Me.sDOB & "#"

End If
End If

'Dim strSQL() As String
If Len(Nz(Me.sLname, "")) > 0 Then
strSQL() = Split(Me.sLname & ",,", ",")
strSQL(0) = Trim(strSQL(0))
strSQL(1) = Trim(strSQL(1))
If Len(strSQL(0)) > 0 Then
strSQL(0) = "([LName] = '" & strSQL(0) & "' "
If Len(strSQL(1)) > 0 Then
strSQL(0) = strSQL(0) & _
" AND [Fname] LIKE '" & strSQL(1) & "*' " ' <- Change * to
%
End If
strSQL(0) = strSQL(0) & ") "
If Len(Nz(sSearch, "")) = 0 Then
sSearch = strSQL(0)
Else
sSearch = sSearch & " OR " & strSQL(0)
End If
End If
End If



'MsgBox sSearch

sql = "SELECT Main_Pt_ID, Arthur_ID, FName, LName, DOB, Pt_Type,
Head_Fam_member_ID FROM Tbl_Main_Pt WHERE " & sSearch

Me.ListFind.RowSource = sql
Click to expand...
Thanks George...Very useful.....It works now.....Immense help :)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with a VBA 4
HELP NEEDED WITH REPORT 1
Blank Report 2
Paramater box pop up even if customer selected in combo box 1
Incremental Key Seasrch, ComboBox 3
How to read single instance of recursive meetings in Outlook 1
How to read single instance of recursive meetings in Outlook 1
How to read single instance of recursive meetings in Outlook 1

Top