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.
This query runs and works as expected.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));
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:
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?Code:SELECT PayApps.*, PrevPayApps.PrevPayAppID FROM PayApps INNER JOIN PrevPayApps ON PayApps.PayAppID = PrevPayApps.PayAppID;
I've attached a database for you to play with if needed, this one throws the error but doesn't crash.