RE: SQL statements and recordset.Open

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Brian (anonymous_at_discussions.microsoft.com)
Date: 07/09/04 

Date: Fri, 9 Jul 2004 09:59:59 -0700

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
>
>"Brian" wrote:
>
>> 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
>>
>.
>

Relevant Pages

  • Re: Checking control properties
    ... the SQL statement you execute with CurrentDB.Execute must be a ... you can build a string from pieces. ... String values inside SQL statements must be delimited by quotes. ... this case is to use single quotes instead of double quotes inside the SQL ...
    (microsoft.public.access.gettingstarted)
  • Re: Passing Date Values - Beginner
    ... I'd say that 'CurrentStrMth" and "CurrentEndMth" are not dates. ... A variable is a container for something, such as a date or a string, but it ... note that you don't put quotes around it. ... Now, when you're building a string to create your SQL Statement, you are ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Open Recordset & AddNew vs. RunSQL "INSERT INTO..."
    ... wrapped in quotes in the VALUESsectionof the Insert Into Sql statement. ... Dim StrVol As String, strSql As String ... Note the Sql statement now has strVol wraped in in a function FixQuotes ...
    (microsoft.public.access.formscoding)
  • SQL and recordset.Open
    ... strsearch variable. ... >' Note the single & double quotes within the SQL ... >> strsearch is the varible with my search string in it. ... so i cant just type in an SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: input mask
    ... The SQL statement is what most people on this newsgroup post - ... Function ReplaceStr(ByVal strText As String, ByVal strSearch As _ String, ByVal strReplace As String, Optional _ ByVal intCompMode As Integer) As String ' ...
    (microsoft.public.access.queries)