Syntax Error - Missing Operator

23 Mar 2006 11:14 AM

lovely_angel_for_you

Hi,

I have this query to pull the data out from the system. And whenever I
run this, I get the "Syntax Error (Missing Operator)" Error displayed.

    Set conn = New ADODB.Connection
    FilePath = App.Path & "\Calls.mdb"
    With conn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & FilePath & ";"
        .Open
    End With
    'Set rdset = New ADODB.Recordset

'The following is single string, it just gets wrapped up.
    Set rdset = conn.Execute("SELECT * FROM InputData Where Name=" &
Name.Text & " AND DateNow=#" &
Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")
            'And then I get to use the data here.
    rdset.Close
    conn.Close

Any help on the above will be greatly appreciated.

Thanks & Regards
Lovely

23 Mar 2006 12:32 PM

Larry Lard

lovely_angel_for_***@yahoo.com wrote:

> Hi,
>
> I have this query to pull the data out from the system. And whenever I
> run this, I get the "Syntax Error (Missing Operator)" Error displayed.
>
>
> 'The following is single string, it just gets wrapped up.
> Set rdset = conn.Execute("SELECT * FROM InputData Where Name=" &
> Name.Text & " AND DateNow=#" &
> Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")

Try

"SELECT * FROM InputData Where Name='" & Name.Text & "' AND
etc

You need to wrap string literals in single quotes (') in SQL strings.
As a consequence, you also need to double up any single quotes in the
string:

"SELECT * FROM InputData Where Name='" & Replace(Name.Text, "'", "''")
& "' AND

A better and neater option is to use parameters - it's all in the docs.

--
Larry Lard
Replies to group please

23 Mar 2006 2:19 PM

Jeff Johnson [MVP: VB]

<lovely_angel_for_***@yahoo.com> wrote in message

news:1143112482.960574.260910@g10g2000cwb.googlegroups.com...

> Set rdset = conn.Execute("SELECT * FROM InputData Where Name=" &
> Name.Text & " AND DateNow=#" &
> Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")

Unless your Name column is actually numeric, you'll need to enclose the
argument you give it in apostrophes:

Set rdset = conn.Execute("SELECT * FROM InputData Where Name='" &
Name.Text & "' AND DateNow=#" &
Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")

23 Mar 2006 4:13 PM

Tony Spratt

<lovely_angel_for_***@yahoo.com> wrote in message
Show quoteHide quote

news:1143112482.960574.260910@g10g2000cwb.googlegroups.com...
> Hi,
>
> I have this query to pull the data out from the system. And whenever I
> run this, I get the "Syntax Error (Missing Operator)" Error displayed.
>
>
>     Set conn = New ADODB.Connection
>     FilePath = App.Path & "\Calls.mdb"
>     With conn
>         .Provider = "Microsoft.Jet.OLEDB.4.0"
>         .ConnectionString = "Data Source=" & FilePath & ";"
>         .Open
>     End With
>     'Set rdset = New ADODB.Recordset
>
>
> 'The following is single string, it just gets wrapped up.
>     Set rdset = conn.Execute("SELECT * FROM InputData Where Name=" &
> Name.Text & " AND DateNow=#" &
> Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")
>             'And then I get to use the data here.
>     rdset.Close
>     conn.Close
>
>
> Any help on the above will be greatly appreciated.
>
>
> Thanks & Regards
> Lovely
>

Try this:

Set rdset = conn.Execute("SELECT * FROM InputData Where Name='" _
& Replace(Name.Text, "'", "''") & "' AND DateNow=#" _
& Format$(PickDate.List(PickDate.ListIndex), "mm/dd/yyyy") & "#")

You need to put single quotes around the text in the SQL statement and the
"Replace" function replaces all single quotes in the textbox itself with
pairs of single quotes (so the SQL server knows they don't end the string).

Cheers,

Tony.

Syntax Error - Missing Operator - When Using Date !