Results 1 to 11 of 11
  1. #1
    AussieMick is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    5

    SQR() function from ACCESS SQL no longer working?

    I'm very puzzled:

    The SQR() function is used in ACCESS SQL queries in a db I've inherited. These have been working up until last Wednesday. The db gets used twice a week, Wed and Fri.



    The queries now report a compile error. Any suggestions would be greatly appreciated.

    Here is a simple example that illustrates the problem:

    Created a table TEST with one field [BASENUMBER] as an Integer.
    Added rows of data: 1, 2, 3, 4, 5

    This SQL query works:
    SELECT TEST.[BASENUMBER] as X, [X]*[X] as X2
    FROM TEST;

    X X2
    1 1
    2 4
    3 9
    4 16
    5 25

    This query, representative of the type of calculations, does not:

    SELECT TEST.[BASENUMBER] as X,
    [X]*[X] as X2,
    SQR([X2]) as FSQR
    FROM TEST;

    It returns: Compile error. in query expression 'SQR([X2]'.

    And nor does this
    SELECT TEST.[BASENUMBER], SQR(TEST.[BASENUMBER]) as FSQR FROM TEST;

    The machine is running Windows 10 Home 10.0.15063 Build 15063
    Access 2010 32 bit as part of Microsoft Office 2010

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Perhaps a reference issue. In the VBA editor go into Tools/References and see if any are flagged as Missing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try this line instead:

    SQR([X]*[X]) as FSQR

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Hmm....

    All those queries work fine for me (and I am using Access 2007).
    I wonder if you have a corrupt database.
    Maybe try it in a fresh database?
    Last edited by JoeM; 07-21-2017 at 08:32 AM. Reason: typo

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just tried this on my windows 10 home, Access 2010 (version 14)
    Code:
    Sub testSQR()
    Dim i As Long
    For i = 1 To 5
      Debug.Print i & "   " & i * i & "  " & Sqr(i)
    Next i
    End Sub
    to produce
    Code:
    1   1  1
    2   4  1.4142135623731
    3   9  1.73205080756888
    4   16  2
    5   25  2.23606797749979
    Also,

    SELECT x as Y,
    Y*Y as Y2,
    sqr(y2) as FSQR
    FROM TestX;
    gives
    Code:
    Y Y2 FSQR
    1 1 1
    2 4 2
    3 9 3
    4 16 4
    5 25 5
    where my table testX is

    Code:
    x
    1
    2
    3
    4
    5

  6. #6
    AussieMick is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    5
    Thanks Paul, when I do this there are 3 ticked/selected:
    Visual Basic for Applications
    Microsoft Access 14 Object Library
    OLE Automation

    and what looks to be a couple of hundred that are unticked/unselected.

    I'm sorry, I don't know if any others should be selected. Nothing seems to be showing as an error.

  7. #7
    AussieMick is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    5
    Sorry, Bulzie - no go - similar compile error for SQR([X]*[X]) as FSQR

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Sorry, no go - similar compile error.
    Which reply is that in reference to?

    Did you try it again in a new, fresh database?

  9. #9
    AussieMick is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    5
    Thanks Orange - your VBA code runs in the db.

    So it would appear that the function is there somewhere, but that the SQL parser/compiler component can't find it?

  10. #10
    AussieMick is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    5
    Gents,

    As per JoeM's suggestion, created a new database and test table/query.

    Ran fine!

    Thanks very much for the interest and fast, helpful replies.

    Now to get to grips with exporting the old database and importing it into a new one - a job for me in the morning, I feel, when some more neurons will hopefully be active.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Glad you got it working.

    Now to get to grips with exporting the old database and importing it into a new one - a job for me in the morning,
    It is actually pretty quick and easy to do!

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

Similar Threads

  1. Email from Access no longer working
    By Gina Maylone in forum Access
    Replies: 4
    Last Post: 10-12-2016, 06:46 AM
  2. Query no longer working in Access 2013
    By aytee111 in forum Access
    Replies: 4
    Last Post: 09-11-2014, 06:48 PM
  3. Replies: 2
    Last Post: 05-29-2013, 01:44 PM
  4. Replies: 1
    Last Post: 05-21-2013, 01:28 PM
  5. Print button no longer working
    By kris335 in forum Programming
    Replies: 6
    Last Post: 09-28-2012, 08:56 PM

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