syntax error (missing operator) in query expression 'Table1.'. 1

[lowtek]

I am getting the above error when I try to run this union query and I cannot find what's wrong with the sql:

SELECT Mid(Table1.DEPT,2,2) AS STORE, Table1.DEPT, Table1.NAME, tbl_Accrual_Holidays.FILE, tbl_Accrual_Holidays.HOL, tbl_Accrual_Holidays.HOURS, tbl_Accrual_Holidays.DATE_ADDED, tbl_Accrual_Holidays.DCTRL, Table1.UNION FROM tbl_Accrual_Holidays INNER JOIN Table1 ON tbl_Accrual_Holidays.FILE=Table1.FILE WHERE (((tbl_Accrual_Holidays.HOL)=IIf(Forms!frm_HolidaySelection.Form!cboHolidayList="New Year's Day","NY" & Right(Year(Now())+1,2),DLookUp("

","[tbl_CalendarHolidays]","[HOLIDAY] = '" & Forms!frm_HolidaySelection!cboHolidayList & "'") & Right(Year(Now()),2))))
ORDER BY Mid(Table1.DEPT,2,2), Table1.DEPT, Table1.NAME;

It worked fine in Access 2000, but our Office was recently upgraded to 2003, and that is when this error started appearing. Any help is appreciated...pulling hairs.

Rey

 

[Ed2020]

I believe the problem is the bit immediately before the FROM keyword:

Table1.UNION

This is trying to retrieve a column from Table1 called "UNION".

IIRC "union" is a reserved word in Access, so there shouldn't (couldn't?) be a field with this name in any table.

Also, this isn't a union query - there is only one SELECT statement. With the SQL statement as it stands I can't see how this has ever executed in Access. I'd start by going back to a backup of the database file taken prior to the Office 2003 upgrade and checking the SQL statement in there.

HTH,

Ed Metcalfe.

Please do not feed the trolls.....

 

[lowtek]

Thanks Ed!

You are correct, as I found out on my own. I tried each field from Table1 to isolate the problem and found that indeed it was the union field that was causing the problem. I figured that it confused the query because union is a sql term. I did this to solve it (to help sql understand better): [table1].[union] and this fixed it.

Regards,

Rey

p.s. I was wondering why Access was representing the query (graphically) as a union query when I was pretty sure it was a select query...it made sense when I found that the table1.union part was causing the problem. When that was fixed, it was showing the select query icon in the object window.

 

[Ed2020]

You're welcome. Glad you got it sorted.

What I am surprised about is that Access 2000 executed the query correctly. Union has been a reserved word at least as far back as Access 97.

As and when you get the opportunity I'd strongly recommend renaming the field to avoid future problems of a similar nature. IMO Access should never have allowed the field to be created with that name in the first place.

Ed Metcalfe.

Please do not feed the trolls.....

 

[lowtek]

Thanks Ed...unfortunately, that is a column name in our payroll system to denote a union employee, so I will just have to remember to use the [] in sql view whenever I use this column.

I don't know why it worked in 2000 when perhaps it shouldn't have. That is why I was so frustrated when it suddenly wouldn't work after the upgrade. I wouldn't have even known this would be a problem if we didn't get the upgrade installed. Strange.