Results 1 to 8 of 8
  1. #1
    Budatlitho is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2016
    Posts
    36

    SQL generated by Access query throws error when testing

    Hello:
    I'm confused; I have a SQL statement that Access' query builder generates which runs in Access but throws syntax errors when tested using https://sqltest.net/

    Here is the code:

    SELECT Right$([Divisions]![Unit],2) AS [No], [2ColumnTags]![DivisionName] & [Office] & "<0x0009>" & [MemberName] AS OfficeAndName, [2ColumnTags]![DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2, [CITY] & ", " & [STATE] & " " & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM, [2ColumnTags]![PMTag] AS Expr2
    FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID
    WHERE (((Right$([Divisions]![Unit],2))=[2 Digit Division Number]))
    ORDER BY Divisions.ID;



    ?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    RIGHT doesnt need the $.

    if [Divisions]![Unit] is null, it will fail. (but thats not syntax)

  3. #3
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    SQL won't recognise [2ColumnTags]![DivisionName] as a valid table/field qualifier.

    [2ColumnTags].[DivisionName] should work instead.

    Also Standard SQL string delimiters are single quotes , it won't understand
    "<0x0009>" or "," or " "

    The join statement is weirdly laid out as well.

    I don't think I have ever seen access generate a query using the bang (!) notation before. Are you sure that is where it came from?
    There are enough differences between Access's version of SQL and T-SQL as used by SQL server that they are not freely interchangeable.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I didn't see Access listed on that site, Access uses is own version of SQL.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Budatlitho is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2016
    Posts
    36
    Any place I can test with Access' flavor of SQL?

    Changed code to:
    SELECT Right([Divisions].[Unit],2) AS [No], [2ColumnTags].[DivisionName] & [Office] & '<0x0009>' & [MemberName] AS OfficeAndName, [2ColumnTags].[DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2, [CITY] & ', ' & [STATE] & , , & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM, [2ColumnTags].[PMTag] AS Expr2
    FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID
    WHERE (((Right([Divisions].[Unit],2))=[2 Digit Division Number]))
    ORDER BY Divisions.ID;

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Erm - What about the Access query editor?
    I'm confused by the requirement.

    By the way table names starting with numbers are a complete no no in SQL server, I would advice changing them in case you ever decide to upscale.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi Minty, I work as a SQL server DBA since 2000 and never found a tablename starting with a number giving any trouble. What would be the problem?

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by NoellaG View Post
    Hi Minty, I work as a SQL server DBA since 2000 and never found a tablename starting with a number giving any trouble. What would be the problem?
    Actually it's not an invalid name, as you point out, but it does mean having to surround them with [ ] all the time, and in Oracle prefixing them with " , so it's generally a PITA.
    Same with field names.

    Not sure where I picked up that you couldn't use it. Must have dreamt it!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error Logging and testing
    By MadTom in forum Programming
    Replies: 7
    Last Post: 01-07-2020, 10:11 AM
  2. Repeated rebuild of right-click menu throws error
    By Petr Danes in forum Programming
    Replies: 1
    Last Post: 04-04-2019, 01:53 PM
  3. Replies: 6
    Last Post: 04-16-2018, 10:38 AM
  4. Replies: 7
    Last Post: 05-10-2017, 11:03 PM
  5. My code throws error 424. How do i work around this?
    By ThornofSouls in forum Programming
    Replies: 2
    Last Post: 09-11-2015, 06:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums