SQL and recordset.Open

  • Thread starter Thread starter Brian
  • Start date Start date
B

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
Click to expand...
looking for data, not manipulating data (like delete,
append, etc.).
One possible error would be the punctuation of your
Click to expand...
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
Click to expand...
[Product Name] = '" & Brian_Value1 & "' and [Date
Purchased] > #" & Format(Brian_Value2, "MM/DD/YYYY")
& "#;")
' Note the single & double quotes within the SQL
Click to expand...
statement, allowing for variables
Tim


.
Click to expand...
..
 
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.
Click to expand...

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
 
Back
Top