How to usu Like Operator and Wildcard

[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!

 

[Robert Morley]

Try:

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


Rob

 

[isbjornen]

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!!!

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!

 

[Marshall Barton]

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

Shouldn't you use % instead of * with ADO?

 

[Robert Morley]

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!!!

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!

 

[isbjornen]

% sign - why didn't I think of that?!?! Thank You Very Much - Works Great!

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

Shouldn't you use % instead of * with ADO?