“undefined function Dlookup in expression error”

you missing a comma "," after tblLinedetails.Qty

or try something like this:

Select tblLinedetails.IDLine, tblLinedetail.Qty,
QryFin.Qty as Net
From tblLinedetails
inner join QryFin on tblLinedetails.IDLine+1 = QryFin.IDLine

Select allOpen in new window

1.  That error message implies a DLOOKUP somewhere in the mix, so I would look at qryFin and see if there is a dlookup in that query.

2.  in addition to the comma after tblLineDetails.Qty,

The embedded select statement will not return any records.

SELECT Qty as Net from [qryFin] WHERE qryFin.IDline = qryFin.IDLine + 1

There is never a case where IDLine = IDLine + 1, except perhaps when IDLine is NULL

So that portion of the query is most likely going to return a NULL for every record.  Can you explain what you are attempting to do with that subquery?

I have tried the code below but says not supported!

SELECT tblLinedetails.IDLine, tblLinedetails.ProductName, tblLinedetails.Qty, tblLinedetails.ProductCode,  tblLinedetails.Qty AS Net
FROM tblLinedetails
inner join tblLinedetails.IDLine on tblLinedetails.IDLine+1 = tblLinedetails.IDLine

What I want here is to subtract the value in the previous row and the next row

Regards

Chris

So, Chirs, what you are trying to do is some form of running inventory?  Are you going to use this in a report, or in a query.  If in a report, then you don't need to add this column to the query, you can do it using the running sum property of a textbox in the report.

If you need this in a query, then you need a way to identify consecutive records for a particular ProductCode or ProductName.  Is the IDLine column consecutive for each productcode or product name?  If not, you have to something like:

SELECT T1.ProductCode, T1.IDLine, Min(T2.IDLine) as NextID
FROM yourTable as T1 LEFT JOIN yourTable as T2
ON T1.ProductCode = T2.ProductCode
AND T2.IDLIne > T1.IDLIne
GROUP BY T1.ProductCode, T1.IDLine

Select allOpen in new window

This subquery can be used to identify IDLine values which are not consecutive.

Then you would wrap this up as a subquery:

SELECT C.ProductCode, C.IDLIne, C.NextID
FROM (
SELECT T1.ProductCode, T1.IDLine, Min(T2.IDLine) as NextID
FROM yourTable as T1 LEFT JOIN yourTable as T2
ON T1.ProductCode = T2.ProductCode
AND T2.IDLIne > T1.IDLIne
GROUP BY T1.ProductCode, T1.IDLine
) as C

Select allOpen in new window

view this in your query designer, then revert to design view and add your table (yourTable) to the query grid twice.
on the first one, you LEFT JOIN the table to this query on ProductCode and IDLine, in the 2nd.
Then create another join from the subquery to the 2nd instance of your table on the ProductCode and between NextID and IDLine

Even when using ELookup instead of DLookup, that query can run quite long when working with large recordsets, because the function must be called for every record.

Dale