help with a VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi..I want some help with this VBA please. I have a searchform with 4 unbound
fields and an unbound Listfield. The user inputs data into one or more of the
fields and matching results are displayed in the Listbox. But, I am having
some problems.

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
 
'/build the search string
If IsNull(sMain_Pt_ID) = False Then
sSearch = " OR [Main_Pt_ID]=" & Me.sMain_Pt_ID
End If


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

If Not IsNull(Me.sDOB) Then
sSearch = sSearch & " Or [DOB]= #" & Me.sDOB & "#"
End If

sSearch = Mid(sSearch,4) 'Strip off the first or

If Len(sSearch) > 1 then
sSearch = "(" & sSearch & ")"
End If


Now add on the name portion of the criteria string


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi..I want some help with this VBA please. I have a searchform with 4 unbound
fields and an unbound Listfield. The user inputs data into one or more of the
fields and matching results are displayed in the Listbox. But, I am having
some problems.

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...
 
Hia John....You are a genius...It fixed it...many Thanks...I am new at the
Forum and cant find where I can rate your answer which deserves 110%..Please
let me know.

Also, one more thing:

sSearch = Mid(sSearch,4) 'Strip off the first or ----Whats the purpose of
this?
If Len(sSearch) > 1 then
sSearch = "(" & sSearch & ")" '---What does this mean?
End If
Click to expand...

Many many Thanks

Rob






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


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

If Not IsNull(Me.sDOB) Then
sSearch = sSearch & " Or [DOB]= #" & Me.sDOB & "#"
End If

sSearch = Mid(sSearch,4) 'Strip off the first or

If Len(sSearch) > 1 then
sSearch = "(" & sSearch & ")"
End If


Now add on the name portion of the criteria string


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi..I want some help with this VBA please. I have a searchform with 4 unbound
fields and an unbound Listfield. The user inputs data into one or more of the
fields and matching results are displayed in the Listbox. But, I am having
some problems.

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...
Click to expand...
 
sSearch = Mid(sSearch,4) 'Strip off the first or ----Whats the purpose of
this?
Click to expand...

When I build the search string I started EVERY criteria with " OR ". When I
get to the end I don't want the combined string to start with " OR ", so I
remove the " OR " at the beginning of the string.

sSearch = "(" & sSearch & ")" '---What does this mean?

I add braces around the OR criteria so that any one of the criteria in the
or is met. I wasn't quite sure how you were handling the name criteria.
Were you attempting to specify that any of the criteria must be met along
with the name

If you wanted
(A or B or C) and D
then the parentheses were needed. On the other hand if you wanted
(A or B or C) Or D
then the parentheses were not needed, but they would not cause any problems.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John Spencer said:
sSearch = Mid(sSearch,4) 'Strip off the first or ----Whats the purpose of

When I build the search string I started EVERY criteria with " OR ". When I
get to the end I don't want the combined string to start with " OR ", so I
remove the " OR " at the beginning of the string.

sSearch = "(" & sSearch & ")" '---What does this mean?

I add braces around the OR criteria so that any one of the criteria in the
or is met. I wasn't quite sure how you were handling the name criteria.
Were you attempting to specify that any of the criteria must be met along
with the name

If you wanted
(A or B or C) and D
then the parentheses were needed. On the other hand if you wanted
(A or B or C) Or D
then the parentheses were not needed, but they would not cause any problems.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..


Thanks again John...That was great help...Yes I wanted all to be Ors
Click to expand...
I still cant find the place to rate your help...I will find out (:
 
Back
Top