Solved: WHERE statement in VBA (ADO) (Access / Excel '97)

[phendrena]

Hi

I want to amend the following statement :

"WHERE Manual_Covernote_Log.ChassisNumber '" & Me.txtDCSearch.Value & "'"

To include LIKE and a wildcard * before and after Me.txtDCSearch.Value

I have tried the following :

"WHERE Manual_Covernote_Log.ChassisNumber '" * " & " & Me.txtDCSearch.Value & "*" & "'" & _

This doesn't work for me, can anyone help?

Thanks,

[CreganTur]

Try this:
"WHERE Manual_Covernote_Log.ChassisNumber LIKE '*'" & "'" & Me.txtDCSearch.Value & "'" & "'*';"

[phendrena]

Hi Randy,

Thank you for the reply.
I've tried to code that you suggested and it doesn't work.
I've tried adjusting the code you suggeted by removing one of wildcards with no success:
"WHERE Manual_Covernote_Log.ChassisNumber LIKE '" & Me.txtDCSearch.Value & "'" & "'*'" & _
"WHERE Manual_Covernote_Log.ChassisNumber LIKE '*'" & "'" & Me.txtDCSearch.Value & "'" & _

Any suggestions?

Thanks,

[CreganTur]

Take a look at this thread (http://www.vbaexpress.com/forum/showthread.php?t=25272). OBP has another method you can try.

[hansup]

Hi Randy,

Thank you for the reply.
I've tried to code that you suggested and it doesn't work.
I've tried adjusting the code you suggeted by removing one of wildcards with no success:
"WHERE Manual_Covernote_Log.ChassisNumber LIKE '" & Me.txtDCSearch.Value & "'" & "'*'" & _ "WHERE Manual_Covernote_Log.ChassisNumber LIKE '*'" & "'" & Me.txtDCSearch.Value & "'" & _
Any suggestions?

Thanks, As I understand, if your text control contains "bob" you want WHERE to return all records whose ChassisNumber contains "bob". If so, the WHERE clause in the finished SQL statement you give ADO should look like this:

WHERE Manual_Covernote_Log.ChassisNumber LIKE '%bob%'

To construct that WHERE clause, use:

"WHERE Manual_Covernote_Log.ChassisNumber LIKE '%" & Me.txtDCSearch.Value & "%'"

It helps to see the actual SQL statement you're submitting to ADO. If you're using a string variable, say strSQL, to hold your SQL statement use

Debug.print strSQL
or
MsgBox strSQL

to review the finished SQL statement before you feed it to ADO.

Good luck,
Hans

[phendrena]

As I understand, if your text control contains "bob" you want WHERE to return all records whose ChassisNumber contains "bob". If so, the WHERE clause in the finished SQL statement you give ADO should look like this:

WHERE Manual_Covernote_Log.ChassisNumber LIKE '*bob*'

To construct that WHERE clause, use:

"WHERE Manual_Covernote_Log.ChassisNumber LIKE '*" & Me.txtDCSearch.Value & "*'"

It helps to see the actual SQL statement you're submitting to ADO. If you're using a string variable, say strSQL, to hold your SQL statement use

Debug.print strSQL
or
MsgBox strSQL

to review the finished SQL statement before you feed it to ADO.

Good luck,
Hans

Hi thanks for the reply,

The msgbox displays the following :-


SELECT Manual_Covernote_Log.DateOfCall, Manual_Covernote_Log.DealerNumber, Manual_Covernote_Log.DealerName, Manual_Covernote_Log.CustomerName, Manual_Covernote_Log.ChassisNumber, Manual_Covernote_Log.ManualCovernoteNumber, Manual_Covernote_Log.Vehicle, Manual_Covernote_Log.Comments
FROM Manual_Covernote_Log
WHERE Manual_Covernote_Log.ChassisNumber LIKE '*10086*'
ORDER BY Manual_Covernote_Log.DateOfCall;

The code appears to be correct, although i am far from being an expert, but it still doesn't want to work for me.

Any idea where I am going wrong? :banghead:

If it helps the field i am searching is made up of a 17 digit string such as WF0HXXWPJH8R19260 generally we would only want to search on the last 5 numerical characters, but it more might be needed.

Thanks,

[hansup]

The code appears to be correct, although i am far from being an expert, but it still doesn't want to work for me.

Any idea where I am going wrong? :banghead:

If it helps the field i am searching is made up of a 17 digit string such as WF0HXXWPJH8R19260 generally we would only want to search on the last 5 numerical characters, but it more might be needed. Your SQL statement looks valid to me. [Note: That was flat out wrong. It was not valid for ADO. I changed the wildcards from * to % in the SQL statements below.]

As a side note, consider aliasing the table name to make it more concise:

SELECT m.DateOfCall, m.DealerNumber, m.DealerName, m.CustomerName, m.ChassisNumber, m.ManualCovernoteNumber, m.Vehicle, m.Comments
FROM Manual_Covernote_Log AS m
WHERE m.ChassisNumber Like '%10086%'
ORDER BY m.DateOfCall;

Actually, since you're referencing a single table, you don't need to qualify the field names at all:

SELECT DateOfCall, DealerNumber, DealerName, CustomerName, ChassisNumber, ManualCovernoteNumber, Vehicle, Comments
FROM Manual_Covernote_Log
WHERE ChassisNumber Like '%10086%'
ORDER BY DateOfCall;

But, back to your current SQL statement, how does it fail? What happens? Can you paste that statement into the SQL View of a new query and run it from there? Does it work?

Hans

[phendrena]

Hi Hans,

Thanks for the reply,


SELECT DateOfCall, DealerNumber, DealerName, CustomerName, ChassisNumber, ManualCovernoteNumber, Vehicle, Comments
FROM Manual_Covernote_Log
WHERE ChassisNumber Like '*10086*'
ORDER BY DateOfCall;

But, back to your current SQL statement, how does it fail? What happens? Can you paste that statement into the SQL View of a new query and run it from there? Does it work?

I'm running the query from an excel userform however running the query in Access does produce the desired result.

[phendrena]

This is the full VBA query i'm running :-

'------------------------------------------'
'------ Chassis Number Search ----'
'------------------------------------------'

Private Sub cChassisNumber()
'Requires Reference to Microsoft ActiveX Data Objects 2.8 Library
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rcArray() As Variant
Dim sSQL As String
Dim row As Long, col As Long

On Error GoTo ErrHandler

'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT Manual_Covernote_Log.DateOfCall, Manual_Covernote_Log.DealerNumber, Manual_Covernote_Log.DealerName, " & _
"Manual_Covernote_Log.CustomerName, Manual_Covernote_Log.ChassisNumber, Manual_Covernote_Log.ManualCovernoteNumber, Manual_Covernote_Log.Vehicle, Manual_Covernote_Log.Comments " & _
"FROM Manual_Covernote_Log " & _
"WHERE Manual_Covernote_Log.[ChassisNumber] LIKE '*" & Me.txtDCSearch.Value & "*' " & _
"ORDER BY Manual_Covernote_Log.DateOfCall;"

'MsgBox sSQL

'Open connection to the database
Set cnt = New ADODB.Connection
cnt.Open glob_sConnect

'Open recordset
Set rst = New ADODB.Recordset
' Record locking
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open sSQL, cnt

With rst
'Fill Array
.MoveFirst
row = .RecordCount
col = .Fields.Count - 1
If row < 1 Then GoTo endnow
ReDim rcArray(0 To row, 0 To col)
'Write the field names
For col = 0 To .Fields.Count - 1
rcArray(0, col) = .Fields(col).Name
Next col
.MoveFirst
'Write the recordset
For row = 0 To .RecordCount
For col = 0 To .Fields.Count - 1
rcArray(row + 1, col) = .Fields(col).Value
Next col
If row = rst.RecordCount - 1 Then
Exit For
Else: .MoveNext
End If
Next row
End With

'Place data in the listbox
With lbxResults
.Clear
.ColumnCount = 8
.List = rcArray
.ListIndex = 1
End With


'Close ADO objects
endnow:
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

Exit Sub

ErrHandler:
MsgBox "Chassis Number Not Found"
Exit Sub

End Sub

[hansup]

I'm running the query from an excel userform however running the query in Access does produce the desired result. I'm not familiar with that context. So I have no idea why valid SQL fails there.

If you create a Zip archive of your database and workbook, and attach it to your reply, we may be able to figure out the problem. Just make sure to obfuscate or discard any sensitive data contained in those files.

Hans

[hansup]

'Open connection to the database
Set cnt = New ADODB.Connection
cnt.Open glob_sConnect
What is glob_sConnect? A connection string for your Access database? I you've defined it elsewhere, please show us what it contains.

Hans

[hansup]

As a temporary trouble-shooting measure, please change this

On Error GoTo ErrHandler
to this

'On Error GoTo ErrHandler

Right now, we don't know where your code is failing. Open the ADO connection? Opening the recordset? Somewhere later on?

Also, have you looked at the GetRows method of the recordset object to fill your array?

Hans

[phendrena]

What is glob_sConnect? A connection string for your Access database? I you've defined it elsewhere, please show us what it contains.

Hans

Here you go :

Option Explicit
'Set reference to the Microsoft ActiveX Data Objects x.x Library!
'Global constants required
Const glob_sdbPath = "S:\BTeams\Ford\DST\Call Log Database\Dealer Support Database.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"

The zip contains the Excel and Access files.
The form i'm using for the searches is frmCNSearch which is called from the main form frmDSTCallLog.

[phendrena]

Error : Runtime error 3021
Either BOF or EOF is true, or the current record has been Deleted.

Debug highlights : .MoveFirst

[CreganTur]

Dealer Support Database is an external database, not the same database that you're trying to run this query in, correct?

[hansup]

Sorry it to me so long!

ADO doesn't use the "*" wildcard character. :banghead:
Instead you must use ANSI SQL wildcards. Make the substitutions indicated in red below and I think you will find joy. :bug:

'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT Manual_Covernote_Log.DateOfCall, Manual_Covernote_Log.DealerNumber, Manual_Covernote_Log.DealerName, " & _
"Manual_Covernote_Log.CustomerName, Manual_Covernote_Log.ChassisNumber, Manual_Covernote_Log.ManualCovernoteNumber, Manual_Covernote_Log.Vehicle, Manual_Covernote_Log.Comments " & _
"FROM Manual_Covernote_Log " & _
"WHERE Manual_Covernote_Log.[ChassisNumber] LIKE '%" & Me.txtDCSearch.Value & "%' " & _
"ORDER BY Manual_Covernote_Log.DateOfCall;"

[hansup]

Error : Runtime error 3021
Either BOF or EOF is true, or the current record has been Deleted.

Debug highlights : .MoveFirst That makes perfect sense now. Your ADO connection was fine. And your recordset opened without error. But the recordset was empty because, with ADO, the LIKE was treating * as a literal character to match rather than a wildcard.

When I changed the wildcard to %, your spreadsheet code runs fine.

Hans

[phendrena]

@ Randy - That's correct the query is generated in Excel and uses ADO to get the data from Access.

@ Hans - Changing the wildcards from * to % has worked a treat. I'll do some proper checking at work tomorrow on the full database but i'm happy to mark this one as solved..... time to move on to the next addition to my excel/access setup - getting data from access into excel and then updating that data (I can get the data from access obviously!)

Thank you both for your assistance.

[chinaboy]

Hi, I know this is an old post, but does someone have the sample files for reference?