By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,041 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,041 IT Pros & Developers. It's quick & easy.

SQL question in and Access

P: n/a
Could someone please help with an SQL question. First Let me explain
what I'm trying to accomplish. I have a small Address book application
that has a search feature. Currently you have to type the Firstname
and LastName exactly as it was entered in the Access Database record
for the routine to find it. I would like to modify the SQL statement
using the LIKE keyword and the % wildcard so that a user can enter
something close and have the search routine find the record. Here is
the SQL statement I have been working on.

searchName = "'" & "%" & LCase(objSearch.txtFName.Text) & " " _
& LCase(objSearch.txtLname.Text) & "%" & "';"

searchSQL = "SELECT UserID, FirstName & Chr(34) & Chr(32) & Chr(34) &
LastName AS Name," _
& " CompanyName, Address, City, State, Zip,
Email, UserNote, Phone, Ext," _
& " Cell, Fax FROM Address WHERE Lcase(Name)
LIKE " & searchName

What I'm trying to do is concatenate the FirstName and LastName fields
as an alias field "Name" and then convert that to lower case to be
compared in the LIKE test.

ie: FirstName = "Bob"
LastName = "Smith"

and then basicly SELECT UserID, FirstNamd+' '+LastName AS Name FROM
Address WHERE Lcase(Name) LIKE '%Bob%'


WHERE Lcase(FirstName) = 'Bob' AND Lcase(LastName) = 'Smith'
Here is the actual search routine Any Help on this would really be
Private Sub Search_rec(ByVal type As Integer)

Dim searchSQL As String
Dim searchName As String
Dim objSearch As New Search

If type = 1 Then
objSearch.rdoByName.Checked = True
objSearch.rdoByComp.Checked = False
objSearch.rdoByComp.Checked = True
objSearch.rdoByName.Checked = False
End If

If objSearch.DialogResult <> DialogResult.OK Then
Exit Sub
End If

If objSearch.rdoByName.Checked Then

searchName = "'" & "%" & LCase(objSearch.txtFName.Text) & "
" _
& LCase(objSearch.txtLname.Text) & "%" & "';"

searchSQL = "SELECT UserID, FirstName & Chr(34) & Chr(32) &
Chr(34)" _
& "LastName AS Name, CompanyName, Address, City,
State, Zip," _
& " Email, UserNote, Phone, Ext, Cell, Fax FROM
Address WHERE" _
& " Lcase(Name) LIKE " & searchName

searchSQL = "SELECT * FROM Address WHERE Lcase(CompanyName)
LIKE " & "'" _
& "%" & LCase(objSearch.txtCompName.Text) & "%" & "'"""
End If
Dim daSearch As New OleDbDataAdapter
Dim dsSearch As New DataSet
Dim dv As DataView
Dim i As Integer

Dim objSearchcommand As New OleDbCommand(searchSQL,
daSearch.SelectCommand = objSearchcommand


daSearch.Fill(dsSearch, "Search")

Catch oledbexceptionErr As OleDbException
MessageBox.Show(oledbexceptionErr.Message, "Access SQL")
End Try

If dsSearch.Tables("Search").Rows.Count = 1 Then
i = dsSearch.Tables("Search").Rows(0).Item("UserID")

dv = New DataView(dsUsers.Tables("Addressbook"))
dv.Sort = "UserID"
intIndex = dv.Find(i)

Call NavigateRecords()
Call Count()
MsgBox("No matching records found",
End If

objSearchcommand = Nothing
daSearch = Nothing
dsSearch = Nothing

End Sub

Feb 22 '06 #1
Share this Question
Share on Google+
2 Replies

P: n/a

As advice bring first your question back to the elementair part of your
question (only one or two fields by instance). We have now to analyze a hug
complex part of code (not the language vb part but the SQL part). Maybe
there is somebody who want to do that, but I am sure not much.

As second advice, there is a special newsgroup for this kind of ADONET


As thirth one, be aware that in the SQL string the Like is different for
SQLClient and for Jet (access).

I hope this helps something,


Feb 23 '06 #2

P: n/a
"Newbie" <la******> wrote in news:1140642977.929054.308980
searchName = "'" & "%" & LCase(objSearch.txtFName.Text) & " " _
& LCase(objSearch.txtLname.Text) & "%" & "';"

jetCriterion = _
"FirstName LIKE ""*" & objSearch.txtFName.Text & "*""" & _
" AND LastName LIKE ""*" & objSearch.txtLName.Text & "*"""
jetSQL = "SELECT etc etc etc" & jetCriterion

I think that will be more likely to get the result you are after.


Tim F

Feb 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.