Results 1 to 9 of 9
  1. #1
    honey2wood is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2010
    Posts
    25

    Query Compiler error

    Hi,

    I have a database that has been running quite successfully for a lot of years. Upgrading to a 64 bit version is highlighting errors which I expected.
    However a query that runs to check for out of date Purchase Orders is causing a compiler error.


    I understand that this is probably 32 / 64 bit issue. Searching various sites will give me details of how to change macro/VB but not yet found anything about Query Expressions changes.
    Code is ' Last(IIf(([PO_Date]+1095)>Now(),"OK","Out of date"))'. Really not sure what I need to change to get it to work in 64bit.
    I would be very grateful is anyone here has any ideas.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    upgrades will do this.
    1. look in vbe (alt-f11) tools, references
    look for old drivers....checked but say MISSING.
    uncheck it and slide down to find the newer version

    2. If you have API calls, add the PTRSAFE to allow it to work with both 32/ & 64 bit
    alter all DECLARES to:


    Code:
    #If Win64 Then      'Public Declare PtrSafe Function
         Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
     #Else
          Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
    #End If
    


  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In general, queries shouldn't be affected by conversion to 64-bit but newer versions of Access don't always accept code that worked in earlier versions.

    If you do have APIs, them you will indeed need to convert them. However, the conditional compilation is the last post was incorrect and won't work correctly in 64-bit Access.
    It should have been (changes shown in RED):

    Code:
    #If VBA7 Then    'Access 2010 or later (32/64-bit)
         Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr
     #Else
          Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long 
    #End If


    If all users are running A2010 or later, you can omit the conditional compilation and just have:
    Code:
    Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr

    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    honey2wood is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2010
    Posts
    25
    Hi Ranman

    I had done this as it had been suggested by someone else. There are 4 that were ticked but none saying missing.
    Your other option was good but I dont see that it cant be done to a query.

  5. #5
    honey2wood is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2010
    Posts
    25
    Hi, Isleofdogs
    Thanks for getting back to me.
    Originally I though that was the problem but at the moment I have no API code that I can change. The problem one is in the query.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The IIf part of your expression looks fine though I'd use Date() rather than Now() as you don't need the time part.
    It should also work in an aggregate query using Last (just tested in A365 64-bit).
    However wrapping that in Last makes no sense at all. In fact Last is often problematic in a query

    What happens if you remove Last?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    honey2wood is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2010
    Posts
    25
    Hi, I have changed the Now to Date and that works fine.
    However I have removed Last but I get an error message " Your query does not include the specific expression IIf(([PO_Date]+1095)>Now(),"OK","Out of date") as part of an aggregate function"
    I have used Last as I am looking for the last date we received a PO from a company with the view to removing them after 3 years.


  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I thought you had replaced Now with Date???
    Using Last (and First) in expressions are unreliable as Access tables have no inherent sort order. Even if the expression works it may not give the results you expect

    Far better could be to sort by PO_Date and then use Max instead OR do a SELECT TOP 1 ... query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    When you have an aggregate function like count, sum, first, last, max, ... in your query, than every field in the select statement must have an aggregation function or must be part of the GROUP BY clause.

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

Similar Threads

  1. debug compiler 'grayed out....huh?
    By Synergy.ron@gmail.com in forum Access
    Replies: 6
    Last Post: 05-14-2021, 06:09 PM
  2. Replies: 2
    Last Post: 03-05-2020, 10:24 AM
  3. compiler error
    By Sheba in forum Forms
    Replies: 20
    Last Post: 10-03-2014, 12:41 PM
  4. Compiler error: Method or member not found
    By JosmithTwo in forum Programming
    Replies: 3
    Last Post: 11-26-2012, 07:10 AM
  5. Compiler WRONG example-DB
    By pacala_ba in forum Access
    Replies: 12
    Last Post: 04-18-2009, 01:00 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