SQL query problem

[DanielConserv]

Hi,

I was wondering if anyone could tell me what i'd done wrong. The following is the code. What it does is to check if the data user input is valid before it goes print preview the report for that particular record. My problem is to ensure that it detects the input like a*. then it should go and find out anyrecords started with "a" letter just like wild card "like"

Dim cnnNew As New ADODB.Connection
Dim rsNew As New ADODB.Recordset
Dim queryString, SystemName As String
Dim isrecordfound As Boolean

isrecordfound = False

SystemName = Chr$(34) + Me.CB_System + Chr$(34)

queryString = "SELECT [System].System_Name FROM [System] WHERE [System].System_Name = like " & SystemName

rsNew.Open queryString, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rsNew.EOF
isrecordfound = True

rsNew.MoveNext
Loop

rsNew.Close

From the code above, i've got syntax error.

Can anyone give me advice on how to fix this error?

Many thanks

[pranoy]

SystemName = Chr$(34) + Me.CB_System + Chr$(34)
queryString = "SELECT [System].System_Name FROM [System] WHERE [System].System_Name = like " & SystemName

Should be

SystemName = Chr$(34) + Me.CB_System + "%"+ Chr$(34)
queryString = "SELECT [System].System_Name FROM [System] WHERE [System].System_Name like " & SystemName

Note: when using 'like ' do not user '=' along with 'like'. Also to make a wild card search add '%'.
Thus 'a*' will be 'a%'
'*a' will be '%a'
'*a*' will be '%a%'

[FunkyDexter]

That's not quite right because it will ALWAYS do a wildcard search. You'ld be better to use the replace function against the systemName variable like this:-

SystemName = Chr$(34) + REPLACE(Me.CB_System, "*", "%") + Chr$(34)
queryString = "SELECT [System].System_Name FROM [System] WHERE [System].System_Name like " & SystemName

That way the user will only get a wildcared search if they entered the * character. If they don't enter the * they'll get an 'exact match' search.