I am trying to construct an SQL statement using VB Script. I want to retrieve all of the records of a certain year...

SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart("y", Table.DatePlayed) = 2003 "

I get an error page stating...

Expected end of statement at the quotation marks:
... DatePart("yyyy" ...

I have tried this with double quotes, single quotes, no quotes, double single quotes, etc., but no luck!

Anyone?

Thanks,

fergy

SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart('yy', Table.DatePlayed) = 2003"

When modifying it to your suggestion I get the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Ah access.
try
SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""y"", Table.DatePlayed) = 2003"

Can't remember the datepart systax for access but something like this.

With the double quotes around it I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

I'll just throw this in here...

SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart("yyyy", Table.DatePlayed) = 2003"

When trying your suggestion, Iget the following:

Microsoft VBScript compilation error '800a0401'

Expected end of statement


It seems the quotations are throwing the syntax out of whack. How can you use datepart in an SQL statement if you can't use quote characters?

SELECT DatePlayed FROM table WHERE DatePart("y", DatePlayed) = 2003

OR

SELECT DatePlayed FROM table WHERE DatePart(year, DatePlayed) = 2003

That would cause problems with the embedded quote - would have to be


SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"

tried this in access and it doesn't lik Table - don't know if you have that as the name

SQL = "SELECT Table.Dateplayed FROM [Table] WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"


Shouldn't give the 2 expeted parameters error though.
Can you post the code you are using.
And do you succeed with other similar queries?

The double quotes you suggested above doesn't do it. I've constructed numerour SQL statements in the past with no difficulty. However, I have never tried building one that required the use of quotation marks, other than for variable names and strings in WHERE clauses.

Here is the code I am working with in my asp script:
---------------------------------------------------------
IF StatTimeFrame = "Career" THEN
set rs = db.execute("SELECT Rounds.*, Rounds.MemberID FROM Rounds WHERE Rounds.Status = 'LOCKED' )
ELSE
set rs = db.execute("SELECT Rounds.*, Rounds.MemberID, Rounds.Dateplayed FROM Rounds WHERE Rounds.Status = 'LOCKED' AND Rounds.MemberID <> '"&MemberName&"' AND StatTimeFrame = DatePart(""yyyy"", Rounds.DatePlayed) ")
END IF
---------------------------------------------------------

The first SQL statement above works fine. I have confirmed that the second SQL statement works fine without the DatePart code. The DatePart code does in fact cause the difficulty.

By the way, thanks for your help!

fergy, in the meantime, while you are digging out the appropriate docs about how to escape a doublequote in ASP or whatever you're using, you can bypass the problem with this workaround --

change

StatTimeFrame = DatePart("yyyy", Rounds.DatePlayed)

to

StatTimeFrame = year(Rounds.DatePlayed)


rudy
http://rudy.ca/

Even when I attempt this workaround, I get the following error:

Too few parameters. Expected 1.

"Too few parameters. Expected 1." is a simple syntax error

what query did you get this on?

not the ASP code that builds the query, but the query string after any variables have been suffed into it

Well, I've found the problem I had with implementing your suggested work - around. In fact it wasn't the work-around that was the issue. It was actually the following:

year(Rounds2.DatePlayed) = StatTimeFrame

I failed to put puntuation around my variable name. I corrected it to be:

'"&StatTimeFrame&"'

I still can't get this to work using the DATEPART function, as the quotation marks cause syntax errors. However, using the workaround above solves the problem.

I sincerely thank everyone for their help on this issue.

fergy

I know this issue has already been solved but another solution to the problem would have been the following:

Where you use
StatTimeFrame = DatePart("yyyy", Rounds.DatePlayed)

Replace the quotes arount yyyy with 4 single quotes
StartTimeFrame = DatePart(''''yyyy'''', Rounds.DatePlayed)

When the SQL is being executed, it will interpret the 4 single quotes with as 1 double quote.

Hope this is of help to someone in the future.

-Jon

www.madridcom.com