Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Exclamation Unanswered: trouble converting from 2000 to 2003 (query problem)

    Hi there
    I have a database that runs absolutely fine when in Access 2000, but when it is run on a computer with Access 2003 I get an error in one of my queries:
    Syntax error (missing operator) in query expression '[employee list].'.
    here is the query, sorry it is a long one:

    SELECT tbl_grade.[NEW WAGE RATE], [employee list].[CLOCK NUMBER], [employee list].SURNAME, [employee list].[FIRST NAMES], [employee list].GENDER, [employee list].CONTROLLER, [employee list].[IDENTITY NUMBER], [employee list].OCCUPATION, [employee list].FORKLIFT, [employee list].[TOW TRUCK], [employee list].[ADDITIONAL QUALIFICATIONS], [employee list].[DATE EMPLOYED], [employee list].[OLD GRADE], [employee list].[OLD GRADE] AS [New Grade], [employee list].[OLD RATE PER HOUR], [employee list].UNION, [employee list].UIF, [employee list].BANK, [employee list].[BANK ACCOUNT NUMBER], [employee list].[BANK BRANCH], [employee list].[ACCESS CARD NUMBER], [employee list].[BACK OF CARD], [employee list].STATUS, [employee list].[DATE OF LEAVING], [employee list].[REASON FOR LEAVING], tbl_Section.Section, [employee list].Race, [employee list].KPI_Category
    FROM tbl_Section INNER JOIN (tbl_grade INNER JOIN [employee list] ON tbl_grade.GradeID = [employee list].GradeID) ON tbl_Section.SectionID = [employee list].SectionID
    WHERE ((([employee list].STATUS)="current" Or ([employee list].STATUS)="temp"))
    ORDER BY [employee list].[CLOCK NUMBER];

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it could be so many things -- cut and paste error introducing a line break into the table name, the use of doublequotes instead of singlequotes to delimit strings, the wonky nesting of joins with parentheses, etc.

    try this:
    Code:
    select G.[NEW WAGE RATE]
         , E.[CLOCK NUMBER]
         , E.SURNAME
         , E.[FIRST NAMES]
         , E.GENDER
         , E.CONTROLLER
         , E.[IDENTITY NUMBER]
         , E.OCCUPATION
         , E.FORKLIFT
         , E.[TOW TRUCK]
         , E.[ADDITIONAL QUALIFICATIONS]
         , E.[DATE EMPLOYED]
         , E.[OLD GRADE]
         , E.[OLD GRADE] AS [New Grade]
         , E.[OLD RATE PER HOUR]
         , E.UNION
         , E.UIF
         , E.BANK
         , E.[BANK ACCOUNT NUMBER]
         , E.[BANK BRANCH]
         , E.[ACCESS CARD NUMBER]
         , E.[BACK OF CARD]
         , E.STATUS
         , E.[DATE OF LEAVING]
         , E.[REASON FOR LEAVING]
         , S.Section
         , E.Race
         , E.KPI_Category
      from (
           [employee list] as E
    inner 
      join tbl_Section as S
        on E.SectionID 
         = S.SectionID
           )
    inner 
      join tbl_grade as G
        on E.GradeID
         = G.GradeID
     where E.STATUS = 'current' 
        or E.STATUS = 'temp'
    order 
        by E.[CLOCK NUMBER]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    when looking at the original DB the query is a normal query, but when looking at the DB that I got back from the person who is meant to use it, the query is now a union query.

    is this a Access 2003 thing?
    thanks in advance
    actionant

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "the query is now a union query" means that somebody changed it

    i don't think access would do that by itself

    (disclaimer: i'm not on access 2003 so i don't know for sure)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    I've changed it back now and resent it to the person who is going to use it.
    We'll see how it goes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •