Another simple question from a simple mind!

[Southern at Heart]

I open a table (recordset?) with the following code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Name")
....and then I can work with rst

I have a query of just the records I want, and want to open it up to work
with but can't figure out how. My Query is:


strSQL = "SELECT Name.Phone " & vbCrLf & "FROM Name " & vbCrLf & _
"WHERE (((Name.Phone) Like ""*"" & Chr(13) & Chr(10) & ""*"" & Chr(13) &
Chr(10) & ""*""));"

I've use DoCmd.RunSql but that was just for an update.
I want to open this query to work with the data...
thanks

 

[Allen Browne]

You can use a SQL statement with OpenRecordset:

Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT [Name].Phone " & vbCrLf & _
"FROM [Name] " & vbCrLf & _
"WHERE ([Name].Phone Like ""*" & vbCrLf & "*" & vbCrLf & "*"");"
Set rst = CurrentDb.OpenRecordset(strSQL)

Notes:
1. The quotes above are different. To verify it gives what you intend,
temporarily add:
Debug.Print strSql
and check the Immediate Window (Ctrl+G) after it runs.

2. Name is a reserved word. It it's not just an example, you might consider
renaming the table (after turning Name Autocorrect off.) In the mean time,
enclosing it in square brackets may help.

For a list of reserved/problem words, see:
http://allenbrowne.com/AppIssueBadWord.html

For an explanation of why to turn Name AutoCorrect off:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Tony Toews [MVP]]

I've use DoCmd.RunSql but that was just for an update.

currentdb.execute strSQL, dbfailonerror is better as that will give
you the error messages.

I want to open this query to work with the data...

What work do you want to do? You can so through a recordset loop and
read and/or update the records. Given a choice though using a query
to do updates is considerably faster.

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/