SQL statements and recordset.Open

[Brian]

I am trying to use the recordset.Open command to run an
SQL statement. The book a I am reading has this for an
example:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * from tblClients"

rst.Close
Set rst = Nothing

I have no problem getting that to work, but i have an SQL
statement saved as a string and i want to pass that
through the rst.Open command. I tried:

rst.Open strsearch

strsearch is the varible with my search string in it.
The string changes depending on what the user is
searching for, so i cant just type in an SQL statement
like in the example. But when i put the varible name in
there i get an error:

Run-time error '3001'

Arguments are of the wrong type, are out of
acceptable range, or are in conflict with
one another.

I'm not sure what that means in English. I hit debug and
it highlights the 'rst.Open strsearch' line.

Any help would be great. Thanks!

Brian

 

[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