#Error, from DFirst Function

[mike02]

Hey, I have set up my Dfirst function, and I am getting an #Error, I have looked at the reasonings for this and my thinking still doesnt work.
Here is my SQL. the formula is NetCash...

SELECT
T1.AccountID, 
T1.ClientID, 
T1.ProductID, 
T1.MonthlyDate, 
T1.Cash,
 (DFirst("[T1].[Cash]","qryAccountSetup","[T1].[ClientID]=" & [T1].[ClientID] & " And [T1].[MonthlyDate] <=#" & [T1].[MonthlyDate] & "#")) AS NetCash


FROM qryAccountSetup AS T1;

Now I have AccountID, which is Autonumber for the table. If i want to get the file 1 row ahead would define this in the DFirstFunction and get that value?

[Dal Jeanis]

The problem is that the alias is not active within the Dlookup. Pull out the T1s inside the quotes. Try something like this:

(DFirst("[Cash]","qryAccountSetup","[ClientID]=" & [T1].[ClientID] & " And [MonthlyDate] <=#" & [T1].[MonthlyDate] & "#"))

[mike02]

perfect, is there away to get it to lag one date? meaning I set up a prior date or something and have it match that date?

[Dal Jeanis]

If it had an autokey, you could add & "AND [Keyfield] < " & T1.[KeyField]

Gak, wait a minute, this is a recursive query? No, don't think this will work at all. Create a second query that doesn't have the call to itself, and call that one to calculate the DFirst. Make sure it has an order by clause that getswhat you want.

[mike02]

I have another quick question....

How can I set this up to grab previous value not the sum....

(SELECT SUM(Cash)     
FROM qryAccountSetup AS T2      
WHERE T2.ClientID = [qryAccountSetup].ClientID   
AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash

the syntax worked for the DFirst function. Thanks

[Dal Jeanis]

Let me try to put it a different way.

1) Access does not know any order for records.
2) Access can't refer to a different record when it's on the current record.

That means that, when calculating a value on the current record, there aren't any other records (like previous record) to refer to. So, you have to code a subquery that properly calculates what that previous record should/would/could have been.

If you can calculate the ending value, and you know what the current record's change to that value is, then you can just calculate the difference, and that's the starting value. If not, then you need a subquery.

[mike02]

ok, I did some research and came up with a bit of an sql, posted in new thread, to allow future searches... Thanks for your help with this DFirst Function!!

[Dal Jeanis]

good enough!