error sintyax in sql...please!

[sal21]

Code here...

Code: Select all

DATA_ACCENSIONE = DateAdd("D", -14, Date)

Set CMD = New ADODB.Command

SQL = "SELECT * from DBO_CC_DAILY WHERE [DATA_ACC] >= #" & Format(DATA_ACCENSIONE, "mm/dd/yyyy") & _
"# AND TIPO_DATO='" & TEST_COUNT & "'"

With CMD
        .CommandTimeout = 0
        .CommandText = SQL
        .ActiveConnection = OCONN
        .CommandType = adCmdText
        Set OCONN_RS = .Execute
        Set CMD = Nothing
    End With

[HansV]

You don't specify any field(s) after SELECT.

You changed the code while I posted a reply!

[HansV]

Please insert the following line below the instruction SQL = ...:

Debug.Print SQL

When you run the code, the SQL string will be written to the Immediate window. Please copy this string and paste it into a reply.

[sal21]

Please insert the following line below the instruction SQL = ...:

Debug.Print SQL

When you run the code, the SQL string will be written to the Immediate window. Please copy this string and paste it into a reply.

Immediate window return:

SELECT * from DBO_CC_DAILY WHERE [DATA_ACC] >= #05/05/2010# AND TIPO_DATO='00'

note:
tipo_dato is formatted TEXT in table field

[HansV]

The SQL looks OK. But since it looks like you're working with a SQL Server table, try this:

Code: Select all

SQL = "SELECT * from DBO_CC_DAILY WHERE [DATA_ACC] >= '" & Format(DATA_ACCENSIONE, "mm/dd/yyyy") & _
"' AND TIPO_DATO='" & TEST_COUNT & "'"

[sal21]

The SQL looks OK. But since it looks like you're working with a SQL Server table, try this:

Code: Select all

SQL = "SELECT * from DBO_CC_DAILY WHERE [DATA_ACC] >= '" & Format(DATA_ACCENSIONE, "mm/dd/yyyy") & _
"' AND TIPO_DATO='" & TEST_COUNT & "'"

Tks. work now!

[Charlotte]

Just to clarify this, Access SQL and the SQL used by SQL Server use different characters to delimit dates, so the same SQL that works in Access may not work in SQL Server and vice versa.

[Carol W.]

Charlotte,

Thanks for clarifying this.

I was just about to post a question on this. I know that Access uses the # as a date delimiter. I now see that SQL Server uses a ' as a date delimiter.