I'm building a lab db to brush up my SQL. One of the exercises I'm setting myself is to create a SELECT clause that will find all the elements in a table that match a pattern.
Here's the SQL command towards which I'm aiming:
Code:
SELECT ID, LastName, FirstName FROM SampleData WHERE LastName LIKE "R*";
If I drop the WHERE clause completely, I get 84 entries (the right number!).
If I use WHERE LastName= <some value that exists>, then I get the right number.
However, when I use WHERE LastName LIKE '*', it doesn't give me anything!
If I am following the syntax of the LIKE statements properly, then my SQL command string is accurate (let alone trying to build it programmatically)
I know the answer is staring me in the face, but I can't see it!
If this is being used in code, you will need to change to single quotes as the whole thing will be in double quotes. If this is in a saved query or SQL Statement in a record source, then it should be fine (although you can forego the semi-colon at the end).
If this is dealing with SQL Server (for example using ADO or if you are using an ADP) then the wildcard would be % instead of *.
I hate it when there's a quick response but in this case it doesns't help;
Here's the more extensive code that I'm using:
Code:
Dim rs As ADODB.Recordset
Dim SQLCommand As String
Debug.Print "Unfiltered SELECT!"
SQLCommand = "SELECT * FROM SampleData;"
Debug.Print SQLCommand
Set rs = New ADODB.Recordset
rs.Open SQLCommand, _
CurrentProject.AccessConnection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount= "; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
Debug.Print "Match known value"
SQLCommand = "SELECT * FROM SampleData WHERE LastName ='Rizzo';"
Debug.Print SQLCommand
rs.Open SQLCommand, _
CurrentProject.AccessConnection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount= "; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
Debug.Print "Match wildcard pattern"
SQLCommand = "SELECT * FROM SampleData WHERE LastName LIKE '*';"
Debug.Print SQLCommand
rs.Open SQLCommand, _
CurrentProject.AccessConnection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount= "; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
And here (with some redactions) are the last 2 sets of debug.print-outs (I'm not going to dump the whole dataset for obvious reasons!)
Code:
SELECT * FROM SampleData WHERE LastName ='Rizzo';
RecordCount= 2 BOF=False EOF=False
1 Rizzo **** *** ********** ******** IL 600** USA ************
72 Rizzo **** *** ********** ******** IL 600** USA ************
Match wildcard pattern
SELECT * FROM SampleData WHERE LastName LIKE '*';
RecordCount= 0 BOF=True EOF=True
So, I am reasonably certain that I have the right syntax, and the right quote characters. Just not the right results. And I haven't even begun to build a more realistic pattern to match!
Well, you've made it past the "So quick that I feel like a complete and utter fool" limit!
Using a % rather than * certainly returned the right set of data in my test bed. Now onwards to using a 'real world' situation.
OK - we're one small step forward for ADODB syntax, and one giant leap backwards for results.
Here is an example of a SELECT statement that does work:
Code:
Dim rs As ADODB.Recordset
Dim SQLCommand As String
Set rs = New ADODB.Recordset
Debug.Print "Match wildcard pattern"
SQLCommand = "SELECT * FROM SampleData WHERE LastName LIKE 'R%';"
Debug.Print SQLCommand
rs.Open SQLCommand, _
CurrentProject.AccessConnection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount= "; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
and here is a very small incremental step to what I want (which is building a list of fields to select, which match a SearchKey):
Code:
Dim rs As ADODB.Recordset
Dim SQLCommand As String
SQLCommand = "SELECT ID, LastName, FirstName FROM SampleData WHERE LastName LIKE "
SQLCommand = SQLCommand + "'" + SearchKey + "%';"
Debug.Print SQLCommand
Set rs = New ADODB.Recordset
rs.Open SQLCommand, _
CurrentProject.Connection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount"; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
Set rs = Nothing
Now the first one works (thanks again, Bob) but the second one doesn't!
The field names are exactly as defined in the table.
Perhaps, I thought, I should add the table name (even though according to the syntax in the Help system) it's not strictly necessary:
Code:
Dim rs As ADODB.Recordset
Dim SQLCommand As String
SQLCommand = "SELECT SampleData.ID, SampleData.LastName, SampleData.FirstName FROM SampleData WHERE LastName LIKE "
SQLCommand = SQLCommand + "'" + SearchKey + "%';"
Debug.Print SQLCommand
Set rs = New ADODB.Recordset
rs.Open SQLCommand, _
CurrentProject.Connection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount"; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If rs.RecordCount > 0 Then
Debug.Print rs.GetString
End If
rs.Close
Set rs = Nothing
Nada!
Well, I suppose this is why I set this little lab up in the first place. But it's very frustrating when I can't get access to the right answers without bugging the good folks on this list!
Is this why I spent hundreds of dollars on Office 2010? (I know, different thread!)
Here is a free hint: throw out "it doesn't work" from you vocabulary. This expression is meaningless for anyone not watching your screen. You have at least shown what you do, but the second part is the reaction of the system, and that is difficult to deduce from "it deosn't work".
what is contained by searchkey? If that thing is Null then nothing gets added to your sqlcommand. You should use & to concatenate strings and not +. + has other features.
After due chastisement from Spikepl, I have replace the '+'concatenation operator by '&'.
The code for the query is as follows (declarations, etc omitted):
Code:
SQLCommand = "SELECT SampleData.ID, SampleData.LastName, SampleData.FirstName FROM SampleData WHERE LastName LIKE "
SQLCommand = SQLCommand & "'" & SearchKey & "%';"
Debug.Print SQLCommand
Set rs = New ADODB.Recordset
rs.Open SQLCommand, _
CurrentProject.Connection, _
adOpenDynamic, _
adLockOptimistic
Debug.Print "RecordCount"; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
and the results (in the immediate window) from the query are as follows:
Code:
SELECT SampleData.ID, SampleData.LastName, SampleData.FirstName FROM SampleData WHERE LastName LIKE 'R%';
RecordCount-1 BOF=False EOF=False
I am intrigued why using what appears to be correct syntax for selecting fields returns no records (RecordCount=-1), whereas using select * does return records. I hope it is clear that I am using this query against a static database that has not been changed or otherwise disrupted between my posts. The inability on my part to perceive a reason for the discrepancy between the advertised results and the actual results, is what leads me to the tentative, albeit politically incorrect, conclusion that it doesn't work (Sorry )
If I were making a syntax error I would expect something (compiler? pre-processor? Jet) to tell me. If there's something else that I'm doing wrong I can't find it in any manuals - and so am seeking help from this group!
I am intrigued why using what appears to be correct syntax for selecting fields returns no records (RecordCount=-1), whereas using select * does return records.
Your assertion is that you're not returning records. That's not true.
You've tested both BOF and EOF and they're false. You have records returned.
The RecordCount property returning -1 and you getting records returned are two different things.
In this case, you're using an unsupported cursor type. (Dynamic is rarely a productive choice - so with KeySet as a general rule.)
You can't predict the behaviour of the substitution you'll get when you request an unsupported cursor type (well you can to an extent, but a property such as RecordCount is freely abandoned, without error.)
Ultimately, all you need is:
Code:
If Not (rs.BOF AND rs.EOF) Then
Debug.Print rs.GetString
End If
But using adOpenKeyset should see your existing code work too.
Thanks, LPurvis, for a clear explanation of why the (wrong) cursor-type was contributing to my dilemma. I've replaced it with adOpenKeyset and, as you predicted, it now reports a valid record count. Myopia, too, contributed to missing the 'False' indications on the BOF/EOF tests.
So a quick recap.
I'm constructing a SQL Select statement (in the SmartSearch function), and putting that in to the RowSource property. Just for kicks, I'm also doing the retrieval that I would expect the combo-box to do (ie using the SQL statement).
If I understand the feedback I've received properly, simply setting the RowSource should prompt a requery. I've commented out that line of code, but am still getting results that I can't understand.
Here, again, is the code for the SmartSearch functoin, and the combo-box OnChange event:
Code:
Function SmartSearch(SearchKey As String) As String
' COnstruct (and check) a SQL command to populate a combo box
Dim rs As ADODB.Recordset
Dim SQLCommand As String
Dim InternalSearchKey As String
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim row As Long
Dim col As Long
Dim ResultsLoaded As Boolean
SQLCommand = "SELECT ID, LastName, FirstName FROM SampleData WHERE LastName LIKE "
SQLCommand = SQLCommand & "'" & SearchKey & "%';"
SmartSearch = SQLCommand
Debug.Print SQLCommand
' Now check out the SQL command, so we can check that it will work
' when used as the RowSource quesry for the calling combo-box
Set rs = New ADODB.Recordset
rs.Open SQLCommand, _
CurrentProject.Connection, _
adOpenKeyset, _
adLockOptimistic
Debug.Print "RecordCount"; rs.RecordCount, "BOF="; rs.BOF, "EOF="; rs.EOF
If Not rs.BOF And Not rs.EOF Then
' Put the results of the query into the SQLResults textbox on the calling form
Me.txtSQLResults = rs.GetString
End If
rs.Close
Set rs = Nothing
End Function
Private Sub cboSmartSearch_Change()
Dim SearchKey As String
Dim SQLCommand As String
Dim RowCount As Long
SearchKey = Me.cboSmartSearch.Text
SQLCommand = SmartSearch(SearchKey)
'
Me.cboSmartSearch.RowSourceType = "Table/Query"
' Put the returned SQL statement into the 'cheat' box on the form.
' This allows us to see what query we are trying to execute as part
' of the control's work
Me.txtSQLCommand = SQLCommand
' Now set the controls RowSource property. THis should prompt a requery
Me.cboSmartSearch.RowSource = SQLCommand
' Me.cboSmartSearch.Requery ' Dropped for test purposes
Me.cboSmartSearch.Dropdown
End Sub
I've also added a couple of 'cheat' textboxes onto the form - one for the command that is returned, and one for the results of the query that I do in SmartSearch.
Here is the definition of the combo-box (selected fields)
Code:
ColumnCount 4
ColumnWidths 0.5";1.5";1.5";1"
RowSource <blank at form load time>
RowSourceType Table/Query
LimitToList No
InputMask <blank>
DefaultValue <blank>
ValidationRule <blank>
DecimalPlaces Auto
So the behavior I am experiencing is as follows:
1) At form load time everything proceeds as expected.
2) I key a character (in this case an 'R'), and I see the following SQL statement in the Command cheatbox: SELECT ID, LastName, FirstName FROM SampleData WHERE LastName LIKE 'R%';
3) The SQLResults contains a string of the records I expected to see.
4) The combo-box does not contain any data in the drop-down list.
5) When I type a second character, the text-box portion of the combo-box suddenly becomes right-justified, and any attempt to leave the combo-box produces the following error-message:
The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field, or a number that is larger than the FieldSize setting permits. (I can't find a property called FieldSize for the combo-box, and the control is not bound to a table column)
It's brand new day here in New York, so I have a fresh set of head-cells to beat against a brick wall., I'm hoping that someone out there can provide some pain relief!
While walking the dog I was thinking about the "invalid data" comment. Even though there is nothing in the combo-box definition to imply that it is a numeric field,. the first field of the SELECT statement references a numeric field. Seems as if MS is taking this to mean that the whole text area is now a numeric field. Tow possibilities to test:
1) Conceal the numeric field (Column width=0)
2) Bind the 2nd column, an alpha-numeric column to the control (not much hope with this, Bound column seems to default to "first available field with a non-zero column width in the display")
SO here's what happens.
1) The data-entry part of the combo-box works as if it were an alphanumeric field, and the SmartSearch function operates to create a valid SQL statement, and the right number of records being returned. We haven't broken anything, and we've fixed the nasty error message.
2) As expected, changing the 'Bound Column' setting does nothing. Still get the right-justification and nasty error message.
Well, I guess it's not always necessary to see the numeric field (good argument for not using numeric keys!), and at least it hasn't affected the functionality of the code outside the control.
Question remains - does setting the control RowSource property cause a requery. Evidence seems to indicate not. But it is, at least, one tiny step forward.
>> Me.cboSmartSearch.RowSourceType = "Table/Query"
Don't assign this property. It's a) redundant and b) not a property you should try to set at runtime anyway. (It can cause requerying issues.)
>> 1) Conceal the numeric field (Column width=0)
That's standard practice. Displayed ID's only serve to confuse users.
>> 2) Bind the 2nd column, an alpha-numeric column to the control
If this control is just for returning a row you want to visit (and not for any data use) then the numeric ID field makes the obvios binding sense. That shouldn't affect the function of the control in searching.
>> does setting the control RowSource property cause a requery
Yes, absolutely.
have you worked from Allen Browne's version of the combo filter? http://allenbrowne.com/ser-32.html
Or perhaps even my one step further efficient filtering? (The "Combo Filtering" example in the link in my sig.)
To be fair, ADO should be perfectly safe enough, especially in the context of this question. It's not being deprecated.
Only the OLEDB provider for SQL Server is going to be (and that's starting with the next version of SQL Server - yet to come).
)
