Syntax Error in From Clause (2000)

Topic

New Reply

I seem to be getting a run of these at the moment – a query string that runs if I paste it into a query object, but that gives an arror when I use it to open a recordset.

The query string is:
SELECT Week_Start_Date, Week_End_Date, RWYear, AgrWeek FROM qry_RetailWeeks WHERE [RWDate] = #26 August 2004#

When I try to open an recordset it gives an ‘Error in FROM clause’ error. The recordset opening code is:

rst.Open strSQL, CurrentProject.Connection, , , adCmdTable

with the query string in strSQL. qry_RetailWeeks is a simple select statement that I used to change the names of two of the fields in the underlying table. I did this when I first got the error because I thought that the field names might be reserved words and therefore the cause the error. The SQL for sql_RetailWeeks:

SELECT RetailWeeks.Date AS RWDate, RetailWeeks.AgrWeek, RetailWeeks.Week_Start_Date, RetailWeeks.Week_End_Date, RetailWeeks.Year AS RWYear
FROM RetailWeeks;

Is there something obvious in my SQL or, is it possible that ADO doesn’t like reserved words as columns column names even when it is ‘once removed’ from the underlying table? RetailWeeks is a table used everywhere by the client and cannot be changed.

Thanks again

Ian

Reply | Quote

Replies

Is your query called qry_RetailWeeks or sql_Retailweeks? You mention both names in your post.

Reply | Quote

Sorry, the line that says ‘The SQL for sql_RetailWeeks’ should read ‘The SQL for qry_RetailWeeks’.
There’s a table called RetailWeeks.
There’s a query called qry_RetailWeeks, which contains a subset of the columns in RetailWeeks and renames two that look as though they have reserved words as column names.
Then there’s a a string variable called strSQL that I put the SQL string into.
Using either the table RetailWeeks, or the query qry_RetailWeeks in the FROM clause gets the same error.

Ian

Reply | Quote

Charlotte may well have a valid point. Are you working with SQL Server tables? That might impose more restrictions than working with Access tables.

(As a test, I created a small table in an Access 2000 format database with fields Date and Year – something I would never do ordinarily – and used an SQL string similar to yours, with the date written out instead of using mm/dd/yy format. Opening an ADO recordset based on this SQL was no problem; it returned the correct record count. So apparently, ADO within Access itself can handle field names such as Year and Date, and dates written as 26 August 2004.)

Reply | Quote

Charlotte may well have a valid point. Are you working with SQL Server tables? That might impose more restrictions than working with Access tables.

(As a test, I created a small table in an Access 2000 format database with fields Date and Year – something I would never do ordinarily – and used an SQL string similar to yours, with the date written out instead of using mm/dd/yy format. Opening an ADO recordset based on this SQL was no problem; it returned the correct record count. So apparently, ADO within Access itself can handle field names such as Year and Date, and dates written as 26 August 2004.)

Reply | Quote

Sorry, the line that says ‘The SQL for sql_RetailWeeks’ should read ‘The SQL for qry_RetailWeeks’.
There’s a table called RetailWeeks.
There’s a query called qry_RetailWeeks, which contains a subset of the columns in RetailWeeks and renames two that look as though they have reserved words as column names.
Then there’s a a string variable called strSQL that I put the SQL string into.
Using either the table RetailWeeks, or the query qry_RetailWeeks in the FROM clause gets the same error.

Ian

Reply | Quote

Is your query called qry_RetailWeeks or sql_Retailweeks? You mention both names in your post.

Reply | Quote

The date is not valid. SQL requires dates in a US format of month/day/year. If you paste it into a query, it may be able to resolve the date in the query grid, but not if you pass it into an ADO recordset. I would also be suspicious of that fields Year and Date, especially if you’re trying to query a SQL Server database.

Reply | Quote

The date is not valid. SQL requires dates in a US format of month/day/year. If you paste it into a query, it may be able to resolve the date in the query grid, but not if you pass it into an ADO recordset. I would also be suspicious of that fields Year and Date, especially if you’re trying to query a SQL Server database.

Reply | Quote

Just out of curiousity, does it make a difference if you drop the adCmdTable at the end?

Reply | Quote

It might. The correct setting for passing in a SQL string is adCmdText

Reply | Quote

That’s what I thought, I always leave it blank, which defaults to -1, which I am not sure what that is. But I always thought adCmdTable, and adCmdTableDirect was used when you refer to a table, and not to a SQL string. When I saw this post, I checked the help file, and quite frankly felt more confused about the issue. I have opened tables directly with ADO (if I am just adding a record), and put “tblSomething” where a SQL statement would go, and I have problems if I don’t use adCmdTableDirect (though I don’t think I tried adCmdTable).

Reply | Quote

You use adCmdTableDirect when you’re using JRO, if I remember correctly. If you are just passing in the name of a table, you use adCmdTable. The argument tells ADO how to evaluate the source, is it a table, a SQL string, or what? Even if you use a command object and specifically set its commandtext property, you still need to pass in the adCmdText argument to avoid unexpected weirdness, at least in all ADO versions up to 2.7. I can’t say I’ve tried anything else since it became a habit with 2.1.

Reply | Quote

Aha. adCmdTableDirect has always worked for me for naming a table in the Open method. Honestly don’t think I tried adCmdTable. Quite frankly, for the past year or two, I simply leave the default, because I’m using SQL statements, and if I am just adding a record, I don’t even use a recordset, I just use INSERT INTO statements….though, if I had a ‘massive’ data dump, I would go back to recordsets.

Either way, it’ll be interesting to find out if the Options argument is the cause of the actual problem.

Reply | Quote

Aha. adCmdTableDirect has always worked for me for naming a table in the Open method. Honestly don’t think I tried adCmdTable. Quite frankly, for the past year or two, I simply leave the default, because I’m using SQL statements, and if I am just adding a record, I don’t even use a recordset, I just use INSERT INTO statements….though, if I had a ‘massive’ data dump, I would go back to recordsets.

Either way, it’ll be interesting to find out if the Options argument is the cause of the actual problem.

Reply | Quote

You use adCmdTableDirect when you’re using JRO, if I remember correctly. If you are just passing in the name of a table, you use adCmdTable. The argument tells ADO how to evaluate the source, is it a table, a SQL string, or what? Even if you use a command object and specifically set its commandtext property, you still need to pass in the adCmdText argument to avoid unexpected weirdness, at least in all ADO versions up to 2.7. I can’t say I’ve tried anything else since it became a habit with 2.1.

Reply | Quote

That’s what I thought, I always leave it blank, which defaults to -1, which I am not sure what that is. But I always thought adCmdTable, and adCmdTableDirect was used when you refer to a table, and not to a SQL string. When I saw this post, I checked the help file, and quite frankly felt more confused about the issue. I have opened tables directly with ADO (if I am just adding a record), and put “tblSomething” where a SQL statement would go, and I have problems if I don’t use adCmdTableDirect (though I don’t think I tried adCmdTable).

Reply | Quote

It might. The correct setting for passing in a SQL string is adCmdText

Reply | Quote

That was it, adCmdText should have been used, not adCmdTable. The code now works.

I have written rst open ststements like this so often that I think I must have got sloppy with the intellisense (is that the right word?) this time, you practically have to type in all of acCmdText, because acCmdTable has almost the same spelling and comes up first in the list, and I probably did this one in a rush. Because they look so similar I didn’t spot the difference.

On the date format, I’ve been using the long date format because (a) it works with Jet, and ( there is no possible ambiguity with dd/mm or mm/dd. However, I have tripped up with an application that I sent to the US (I’m based in Britain) where the long date format seems to include a day name and a comma. The comma I think caused problems. Is there a better, use everywhere, way of formatting dates that are to be put in SQL strings? Does everybody write their own format to US style function?

Sorry for wasing everybody’s time on the adCmdText issue,

Ian

Reply | Quote

I routinely put “#” & Format(DateVariable, “mm/dd/yyyy”) & “#” into my SQL strings if I need a date, I haven’t bothered to write a custom function for it.

Reply | Quote

I routinely put “#” & Format(DateVariable, “mm/dd/yyyy”) & “#” into my SQL strings if I need a date, I haven’t bothered to write a custom function for it.

Reply | Quote

We all fall over things like that sometimes, and it’s never a waste of time if someone else can benefit from it.

As to the date, our apps are used internationally, so we use a medium format date in displays to avoid confusion; and we created a function we called USDate that takes the passed in date and converts it to a mm/dd/yyyy format for use in SQL statements. It isn’t so bad between US and UK formats, but it can get tricky with some other regional settings where delimiters in a date may be spaces or something else besides “/” or “-“, so you have to do some testing there as well. One thing to watch out for is that the domain functions like DLookup, etc., also need the correct date format in their WHERE clauses. We tripped over that when we were implementing internationalization in our code too.

Reply | Quote

We all fall over things like that sometimes, and it’s never a waste of time if someone else can benefit from it.

As to the date, our apps are used internationally, so we use a medium format date in displays to avoid confusion; and we created a function we called USDate that takes the passed in date and converts it to a mm/dd/yyyy format for use in SQL statements. It isn’t so bad between US and UK formats, but it can get tricky with some other regional settings where delimiters in a date may be spaces or something else besides “/” or “-“, so you have to do some testing there as well. One thing to watch out for is that the domain functions like DLookup, etc., also need the correct date format in their WHERE clauses. We tripped over that when we were implementing internationalization in our code too.

Reply | Quote

Not a waste of time. Sometimes we all need a second pair of eyes on something, that’s why the lounge is here!

As for the date formatting, if you stick with Access, you can’t go wrong with #” & dteSomDateVariable & “# because the date variable is a constant format (double variable, whole numbers for days, decimal for time), which is Universal in Access. If you want it to work in SQL server, then I believe SQL Server wants MM/DD/YYYY.

Reply | Quote

It isn’t SQL Server, Drew, it’s the SQL language standard that requires dates in US format of mm/dd/yy or mm/dd/yyyy.

Reply | Quote

Oh, I had always thought it was SQL Server, because Jet doesn’t mind getting an actual date variable. So Jet just ‘comprimises’ I guess. Learn something knew everyday.

Reply | Quote

If you have your system dates set to US date format, Jet handles it transparently. Trust me, there’s translation that has to go on if the system date format is something other than mm/dd/yyyy or mm-dd-yyyy. I’ve had to deal with that in applications deployed in other parts of the world.

Reply | Quote

If you have your system dates set to US date format, Jet handles it transparently. Trust me, there’s translation that has to go on if the system date format is something other than mm/dd/yyyy or mm-dd-yyyy. I’ve had to deal with that in applications deployed in other parts of the world.

Reply | Quote

Oh, I had always thought it was SQL Server, because Jet doesn’t mind getting an actual date variable. So Jet just ‘comprimises’ I guess. Learn something knew everyday.

Reply | Quote

It isn’t SQL Server, Drew, it’s the SQL language standard that requires dates in US format of mm/dd/yy or mm/dd/yyyy.

Reply | Quote

Not a waste of time. Sometimes we all need a second pair of eyes on something, that’s why the lounge is here!

As for the date formatting, if you stick with Access, you can’t go wrong with #” & dteSomDateVariable & “# because the date variable is a constant format (double variable, whole numbers for days, decimal for time), which is Universal in Access. If you want it to work in SQL server, then I believe SQL Server wants MM/DD/YYYY.

Reply | Quote

That was it, adCmdText should have been used, not adCmdTable. The code now works.

I have written rst open ststements like this so often that I think I must have got sloppy with the intellisense (is that the right word?) this time, you practically have to type in all of acCmdText, because acCmdTable has almost the same spelling and comes up first in the list, and I probably did this one in a rush. Because they look so similar I didn’t spot the difference.

On the date format, I’ve been using the long date format because (a) it works with Jet, and ( there is no possible ambiguity with dd/mm or mm/dd. However, I have tripped up with an application that I sent to the US (I’m based in Britain) where the long date format seems to include a day name and a comma. The comma I think caused problems. Is there a better, use everywhere, way of formatting dates that are to be put in SQL strings? Does everybody write their own format to US style function?

Sorry for wasing everybody’s time on the adCmdText issue,

Ian

Reply | Quote

Just out of curiousity, does it make a difference if you drop the adCmdTable at the end?

Reply | Quote