Results 1 to 11 of 11
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Query Error And Crashing Access

    I have tables called Jobs and PayApps. A job has many PayApps. Each PayApp has a date field called PeriodTo. I have the following query to find all PayApps for a job, and also for each PayApp find the PayApp that came just before it.



    Code:
    SELECT PayApps.PayAppID, (
      SELECT TOP 1 ppa.PayAppID
      FROM PayApps AS ppa
      WHERE ppa.JobID=PayApps.JobID
        AND ppa.PeriodTo<PayApps.PeriodTo
      ORDER BY ppa.PeriodTo DESC
    ) AS PrevPayAppID
    FROM PayApps
    WHERE (((PayApps.JobID)=3));
    This query runs and works as expected.

    Now I need to join the results of this query against other queries or tables ON PayAppID. When I try to do this Access reports the following error: "Invalid argument to function"

    Here is a simple example of a failing query:
    Code:
    SELECT PayApps.*, PrevPayApps.PrevPayAppID
    FROM PayApps INNER JOIN PrevPayApps ON PayApps.PayAppID = PrevPayApps.PayAppID;
    I have the same scenario setup on a larger much more complicated set of queries and instead of the error MS Access will just crash ouright. Can anyone spot the problem?

    I've attached a database for you to play with if needed, this one throws the error but doesn't crash.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I suspect it the 'SELECT TOP 1 ppa.PayAppID' phrase. (what is ppa?)
    I dont know why it works in 'PrevPayApps', but I bet none of the other queries that use PrevPayApps understand it either.

    I bet you could make a query with 2 instances of: PayApps, to get the same thing.
    Other queries may allow that.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by ranman256 View Post
    I suspect it the 'SELECT TOP 1 ppa.PayAppID' phrase. (what is ppa?)

    It's an alias defined in the next line for the second instance of the PayApps table, short for 'previous pay application'.

    I bet you could make a query with 2 instances of: PayApps, to get the same thing.
    Other queries may allow that.
    Any chance you could show me an example of what you mean?

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Any ideas?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try changing the "INNER JOIN" to "LEFT JOIN" or "RIGHT JOIN"

    Query "Fails"
    Code:
    SELECT Dummy.PayAppID, PrevPayApps.PrevPayAppID, Dummy.Whatever
    FROM Dummy LEFT JOIN PrevPayApps ON Dummy.PayAppID = PrevPayApps.PayAppID;
    Code:
    SELECT Dummy.PayAppID, PrevPayApps.PrevPayAppID, Dummy.Whatever
    FROM Dummy RIGHT JOIN PrevPayApps ON Dummy.PayAppID = PrevPayApps.PayAppID;
    Don't know what you are expecting for results, but the queries execute.....

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    That works for me, thank you. Any idea why it crashes with the inner join?

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I spoke too soon. I suppose that solved the issue with the database example I presented but my actual database with the complicated queries/subqueries still crashes... I hesitate to upload it because I don't think I have the energy to explain exactly what it is I'm doing

    I think I'm going to go in a slightly different direction and experimenting with temporary tables.

    If anyone's still curious let me know and I'll upload it.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Any idea why it crashes with the inner join?
    Well, I have figured out WHAT the cause is (I think), but as to WHY, I have no idea. It looks to me like a bug in A2016.

    Here's what it is. Your queries are selecting fields like PrevPayApps.PrevPayAppID from other queries, and in those other queries, PrevPayAppID is a subquery (Select...).

    It seems that Access does not like that subquery use, when you use the INNER JOIN. If I remove the PrevPayAppID field from the SQL, the query runs, even with the INNER JOIN. The query also runs with PrevPayAppID included, but changing INNER JOIN to LEFT JOIN. (See ssanfu's post #5).

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Here's another solution. Write a little function that achieves the same result as your subqueries do:

    Code:
    Function getmax(JobID As Integer, Period As Date) As Integer
    
    
    Dim Result As Integer
    
    Result = Nz(DMax("PayAppID", "PayApps", "JobID = " & JobID & " AND PeriodTo < #" & Period & "#"), 0)
    getmax = Result
    End Function
    Now, in your queries instead of this:

    PrevPayAppID: (SELECT TOP 1 ppa.PayAppID
    FROM PayApps AS ppa
    WHERE ppa.JobID=PayApps.JobID
    AND ppa.PeriodTo<PayApps.PeriodTo
    ORDER BY ppa.PeriodTo DESC
    )

    you can put this:

    PrevPayAppID: getmax([jobid],[periodto])

    I tried it in the FAILS queries and it works a treat.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thanks John! I hadn't considered a custom function. I've sort of have been leery to try it yet as I read all the time to avoid calls to vb functions in queries and try and rely on native db engine functions. Obviously this time the db engine doesn't much care for what I'm feeding it... I know realistically I overestimate the performance of these calls' impact on my little application, I'll have to start playing with it.

    ...On the other hand I am kind of enjoying learning to "think" in sql.

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by John_G View Post
    Here's another solution. Write a little function that achieves the same result as your subqueries do:

    Code:
    Function getmax(JobID As Integer, Period As Date) As Integer
    
    
    Dim Result As Integer
    
    Result = Nz(DMax("PayAppID", "PayApps", "JobID = " & JobID & " AND PeriodTo < #" & Period & "#"), 0)
    getmax = Result
    End Function
    Now, in your queries instead of this:

    PrevPayAppID: (SELECT TOP 1 ppa.PayAppID
    FROM PayApps AS ppa
    WHERE ppa.JobID=PayApps.JobID
    AND ppa.PeriodTo<PayApps.PeriodTo
    ORDER BY ppa.PeriodTo DESC
    )

    you can put this:

    PrevPayAppID: getmax([jobid],[periodto])

    I tried it in the FAILS queries and it works a treat.
    it occurs to me that the function you wrote returns the row with the highest ID, not strictly necessarily the row with the next highest PeriodTo. This kind of goes back to the limitations Allen Brown describes for DLookUp and not sorting its results... I'll look into his 'enhanced' functions.

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

Similar Threads

  1. Query Crashing Access
    By JonReedTDC in forum Queries
    Replies: 4
    Last Post: 04-21-2016, 04:33 PM
  2. Access Crashing without and error message
    By tonygg in forum Access
    Replies: 7
    Last Post: 11-30-2015, 06:14 PM
  3. Access Crashing
    By MFS in forum Access
    Replies: 18
    Last Post: 04-03-2014, 01:21 PM
  4. Replies: 5
    Last Post: 01-09-2014, 10:45 PM
  5. Access Crashing
    By martinjamesward in forum Access
    Replies: 2
    Last Post: 09-09-2010, 06:29 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