How to usu Like Operator and Wildcard

  • Thread starter Thread starter isbjornen
  • Start date Start date
I

isbjornen

What is the proper way to write a SQL statement for a Recordset using the
Like Operator and the asterisk wildcard (*)?

Here is the SQL statement from the query (works just fine):

SELECT SOFSN.SERIAL_NUMBER
FROM SOFSN
WHERE (((SOFSN.SERIAL_NUMBER) Like "*" & [Forms]![frmRepair]![txtSerialNo]));

Here's what I'm trying to do (and I tried a zillion ways without success):

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*' &
[Forms]![frmRepair]![txtSerialNo]));"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 1 Then
'Do something
End If

rst.Close

The txtSerialNo is a String and sometimes starts with a zero - that's what
I'm trying to capture in case no zero is entered from the start.
Example: 120 could be 0120

Thanx!
 
Try:

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*" &
[Forms]![frmRepair]![txtSerialNo])) & "';"


Rob
 
Thanks, but i didn't quite work. I had to modify the statement since VBA
doesn't like the parenthesis outside quotes:

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*" & [Forms]![frmRepair]![txtSerialNo]
& "'));"

However, the recordset is empty when I run it (RecordCount = 0).

Any and all suggestions are welcome!!!


Robert Morley said:
Try:

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*" &
[Forms]![frmRepair]![txtSerialNo])) & "';"


Rob
What is the proper way to write a SQL statement for a Recordset using the
Like Operator and the asterisk wildcard (*)?

Here is the SQL statement from the query (works just fine):

SELECT SOFSN.SERIAL_NUMBER
FROM SOFSN
WHERE (((SOFSN.SERIAL_NUMBER) Like "*" & [Forms]![frmRepair]![txtSerialNo]));

Here's what I'm trying to do (and I tried a zillion ways without success):

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*' &
[Forms]![frmRepair]![txtSerialNo]));"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 1 Then
'Do something
End If

rst.Close

The txtSerialNo is a String and sometimes starts with a zero - that's what
I'm trying to capture in case no zero is entered from the start.
Example: 120 could be 0120

Thanx!
Click to expand...
Click to expand...
 
isbjornen said:
What is the proper way to write a SQL statement for a Recordset using the
Like Operator and the asterisk wildcard (*)?

Here is the SQL statement from the query (works just fine):

SELECT SOFSN.SERIAL_NUMBER
FROM SOFSN
WHERE (((SOFSN.SERIAL_NUMBER) Like "*" & [Forms]![frmRepair]![txtSerialNo]));

Here's what I'm trying to do (and I tried a zillion ways without success):

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*' &
[Forms]![frmRepair]![txtSerialNo]));"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Click to expand...


Shouldn't you use % instead of * with ADO?
 
Woops, yeah, that was my mistake. Sorry.
Thanks, but i didn't quite work. I had to modify the statement since VBA
doesn't like the parenthesis outside quotes:

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*" & [Forms]![frmRepair]![txtSerialNo]
& "'));"

However, the recordset is empty when I run it (RecordCount = 0).

Any and all suggestions are welcome!!!


Robert Morley said:
Try:

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*" &
[Forms]![frmRepair]![txtSerialNo])) & "';"


Rob
What is the proper way to write a SQL statement for a Recordset using the
Like Operator and the asterisk wildcard (*)?

Here is the SQL statement from the query (works just fine):

SELECT SOFSN.SERIAL_NUMBER
FROM SOFSN
WHERE (((SOFSN.SERIAL_NUMBER) Like "*" & [Forms]![frmRepair]![txtSerialNo]));

Here's what I'm trying to do (and I tried a zillion ways without success):

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*' &
[Forms]![frmRepair]![txtSerialNo]));"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 1 Then
'Do something
End If

rst.Close

The txtSerialNo is a String and sometimes starts with a zero - that's what
I'm trying to capture in case no zero is entered from the start.
Example: 120 could be 0120

Thanx!
Click to expand...
Click to expand...
Click to expand...
 
% sign - why didn't I think of that?!?! Thank You Very Much - Works Great!

Marshall Barton said:
isbjornen said:
What is the proper way to write a SQL statement for a Recordset using the
Like Operator and the asterisk wildcard (*)?

Here is the SQL statement from the query (works just fine):

SELECT SOFSN.SERIAL_NUMBER
FROM SOFSN
WHERE (((SOFSN.SERIAL_NUMBER) Like "*" & [Forms]![frmRepair]![txtSerialNo]));

Here's what I'm trying to do (and I tried a zillion ways without success):

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

SQL = "SELECT SOFSN.SERIAL_NUMBER " & _
"FROM SOFSN " & _
"WHERE (((SOFSN.SERIAL_NUMBER) Like '*' &
[Forms]![frmRepair]![txtSerialNo]));"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Click to expand...


Shouldn't you use % instead of * with ADO?
Click to expand...
 
Back
Top