SQL and recordset.Open

[Brian]

Thanks for the reply. I added the format function on my
dates and the debug.print result looks good. All single
quotes and the # symbols around dates and the ; at the
end. Is there a restriction as to where you can call the
rst.Open command? I have it in my Private Sub Form_Load()
procedure. It is still giving me the same error as
before.

Brian

-----Original Message-----
Make sure your SQL (within that variable) is just

looking for data, not manipulating data (like delete,
append, etc.).

One possible error would be the punctuation of your

strsearch variable. That means using the proper #'s for
dates, and single-quotes for text values that are needed
as criteria.

Example:

Brian_Value1 = "ABCDEFG"
Brian_Value2 = Datevalue("06/01/04")
strsearch = "Select * from [Some Table Name] Where

[Product Name] = '" & Brian_Value1 & "' and [Date
Purchased] > #" & Format(Brian_Value2, "MM/DD/YYYY")
& "#;")

' Note the single & double quotes within the SQL

statement, allowing for variables

Tim


.

..

 

[Tim Ferguson]

Thanks for the reply. I added the format function on my
dates and the debug.print result looks good. All single
quotes and the # symbols around dates and the ; at the
end. Is there a restriction as to where you can call the
rst.Open command? I have it in my Private Sub Form_Load()
procedure. It is still giving me the same error as
before.

The error is telling you that the SQL command is malformed: perhaps it
would help if you are able to post the actual debug.print result. The
suggestions that (the other!) Tim gave you were right for a Jet database,
but may need to be different for another database provider.

This error (and yes, it is in English: have you read it?) is extremely
unlikely to be the result of opening the recordset in any particular event,
as you know since you successfully opened the simple "SELECT * FROM" query.
What are you going to be doing with the result anyway -- some things that
come later may be happening at the wrong time, but they will not tbe source
of this error.

Hope that helps


Tim F