Set Variable to FieldABC Of Last Record

[LouiseJ]

This seems as though it should be very to do.

How do I set a variable to FieldABC of the last record returned by a query. (the query is sorted on two columns.)

Have been trying to use Dlookup but I don't seem to be able to set the criteria.

Thanks

Louise J

 

[Remou]

You have not said what 'last' is. It is often best to post the SQL for any queries, as you will get a fuller answer.

Have you looked at DMax and DMin, which can be used in association with DlookUp?

 

[LouiseJ]

I have been trying the following;

Dim VariableName = DLookup(“Field2”, “QryName”, (Dmax Field2(Dmax Field1))

But can’t quite see why it is not working.

The SQL for the query that I am referring to is as follows:

SELECT [QrySalesEnquiries&Customer].*
FROM TblAccountLetter INNER JOIN [QrySalesEnquiries&Customer] ON TblAccountLetter.AccountLetter = [QrySalesEnquiries&Customer].AccountLetterSplit
ORDER BY TblAccountLetter.SortOrder, [QrySalesEnquiries&Customer].TblSalesEnquiries.[Customer Account Number];

Thanks

 

[Remou]

Try something like:

[tt]Dim VariableName
VariableName = DLookup("FieldABC", "QryName", "SortOrder=" & Dmax ("SortOrder","QryName"))[/tt]

Assuming that SortOrder is numeric.

I notice that the SQL you posted is also sorted by:
[tt][QrySalesEnquiries&Customer].TblSalesEnquiries.[Customer Account Number];[/tt]
But I cannot see how that would work.

 

[LouiseJ]

Hi Remou

That works!

Have also altered the sort of the SQL. Thanks for pointing that out.

Thanks for your help

LouiseJ