Query returns no records problem

[Fredrated]

I am having a problem with a query that returns no records.

For example, this query may return no records:
SELECT RMDocuments.Directory, RMDocuments.User FROM RMDocuments WHERE
(((RMDocuments.DOCUMENT_FILENAME)="TestFile.doc"));

When I set a recordset to this query, as in

strSQL = "SELECT RMDocuments.Directory, RMDocuments.User FROM RMDocuments
WHERE (((RMDocuments.DOCUMENT_FILENAME)=""TestFile.doc""));"
Set rsSrch = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

There is in fact no file by the name of "TestFile.doc", yet rsSrch.BOF and
rsSrch.EOF both return FALSE, leading me to believe that one or more records
were returned, then when I execute rsSrch.MoveFirst, I get "No current
record"!

What am I doing wrong? I thought that BOF=False and EOF=False would mean
that there was at least one record returned.

Thanks in advance for any help.

Fred

 

[Jerry Whittle]

Try inserting a single quotation mark ' on each side of ""TestFile.doc"". I
can't remember exactly how it's done. It's either at each end such as:

'""TestFile.doc""'

Or between the double quotes like so:

"'"TestFile.doc"'"

The double-double quotation marks confuses the statement.

 

[Ken Snell [MVP]]

Your code looks ij for setting the SQL statement. The problem is your use of
CurrentDb for opening the recordset; the recordset goes out of scope as soon
as that line of code finishes excecuting.

Try this:

Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = "SELECT RMDocuments.Directory, RMDocuments.User FROM RMDocuments
WHERE (((RMDocuments.DOCUMENT_FILENAME)=""TestFile.doc""));"
Set rsSrch = dbs.OpenRecordset(strSQL, dbOpenDynaset)
' more code here
' when all code is done, run these code steps
dbs.Close
Set dbs = Nothing

 

[Fredrated]

Actually, while most things do go out of scope when instantiated with
CurrentDb, recordsets are an exception and persist. I know it is a little
weak, but I have used this technique hundreds of times with no problem.

Thanks for addressing my problem.

Fred

 

[Fredrated]

Double-Double quotes ("") are the way you insert single Double-quotes (")
into a string that is itself delimited by double-quotes. (how's that for a
confusing sentence?)

The resulting string has only single-double quotes when it is evaluated by
Access, and is thus a valid SQL select statement. In the past I have used
CHR(34) to place quotes into a string created between quote marks, but this
technique is valid.

Everything executes correctly, and a recordset is created, I just don't
understand why, when the recordset is empty, at least one of BOF or EOF
doesn't evaluate to TRUE.

Thanks for checking out my issue.

Fred