DLAST FOR DATE SERIAL

[Luclarie]

Hi, intermediate novice here and I'm stumped trying to create a very simple DLAST in a query using SQL.

The plan is to create this into vb SQL but for now working with the query grid...

Every day we enter kg. amounts into a tbl, usually it's once a day, but it can sometimes be more than once a day. I'd like to create a dlast to get the last kg. entry in the future entry (it's descending) but I don't want to use the detail ID, what if someone forgets an earlier entry and then has a earlier date but later detail id?

entry_date

So I forced the entry_date into a serial in qry 1, and now in qry 2 trying to create SQL query for Dlast using serial1_date and serial2_date as it would seem the most logical thing to do -- What I'm not sure of is the formatting, thus the code could be wrong...

tbl_daily_entry
entry_date (format date - nothing special)
entry_amount_kg (format single, fixed 2 (looks like this: 00.00)

qry_1
entry_kg:entry_amount_kg
serial1_date: (CDbl([entry_date]))
serial2_date: (CDbl([entry_date]))


qry2:
LAST_ENTRYLast("[entry_kg]","qry_1", [serial1_date] < [serial2_date])

Questions
Is the tbl fields correct formats?
Since serials are now double format shouldn't they register as numbers?

&&&&&&
Any suggestions would be most helpful -
thank you!



DSum (Dlast) NOTES:
' *************************** '

' ***************************
' Typical Use
' Numerical values. Replace "number" with the number to use.
variable = DLast("[FieldName]", "TableName", "[Criteria] = number")

' Strings. Numerical values. Replace "string" with the string to use.
variable = DLast("[FieldName]", "TableName", "[Criteria]= 'string'")

' Dates. Replace "date" with the string to use.
variable = DLast("[FieldName]",, "TableName", "[Criteria]= #date#")

***************************

Referring to a control on a form

' Numerical values
variable = DLast("[FieldName]", "TableName", "[Criteria] = " & Forms!FormName!ControlName)

' Strings
variable = DLast("[FieldName]", "TableName", "[Criteria] = '" & Forms!FormName!ControlName & "'")

' Dates
variable = DLast("[FieldName]" , "TableName", "[Criteria] = #" & Forms!FormName!ControlName & "#")

***************************
Combinations
Multiple types of criteria
variable = DLast("[FieldName]", "TableName", "[Criteria1] = " & Forms!
[FormName]![Control1] _
& " AND [Criteria2] = '" D & Forms![FormName]![Control2] & "'" _
& " AND [Criteria3] =#" D & Forms![FormName]![Control3] & "#")

' Use two fields from a single record.
variable = DLast("[LastName] & ', ' & [FirstName]", "tblPeople", "[PrimaryKey] = 7")

' Expressions
variable = DLast("[Field1] + [Field2]" d , "tableName", "[PrimaryKey] = 7")

' Control Structures
variable = DLast("IIf([LastName] Like 'Smith', 'True', 'False')", "table‐Name", "[PrimaryKey] = 7")

***************************

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[plog]

Never use FIRST, LAST, DFirst() or DLast(). They just don't work like you think they do.

You're post drowned me in code and irrelevant details, so I am not clear how to help you specifically, so I will in general. In general, instead of LAST in a query, you should build a subquery using MAX, then use that query in another query to produce the exact result you want:

SampleData
SalesDate, SalesPerson, SalesQuantity
1/1/2025, Fred, 7
1/1/2025, Sally, 10
2/2/2025, Sally, 4
2/2/2025, Dave, 9
3/3/2025, Sally, 8
4/4/2025, Dave, 3

To get the last sale record for everyone you would first determine the date of each persons sale:

SELECT SalesPerson, MAX(SalesDate) AS LastSale FROM SampleData GROUP BY SalesPerson

Let's call that query 'LastSalesDate'. It produces:

LastSalesDate
SalesPerson, LastSale
Fred, 1/1/2025
Sally, 3/3/2025
Dave, 4/4/2025

Then you build a new query on LastSalesDate and SampleData to get all the data of the last sale for each person:

SELECT SampleData.SalesDate,  SampleData.SalesPerson, SampleData.SalesQuantity
FROM SampleData
INNER JOIN LAstSalesDate ON SampleData.SalesDate = LastSalesDate.LastSale AND SampleData.SalesPErson = LAstSalesDate.SalesPerson

That will produce all the data of the last sale of each person. When you want it in a form or in VBA you use a DLookup into that final query to retrieve it.

Edit: Fixed first SQL by adding GROUP BY, thanks tvan.

 

[tvanstiphout]

SELECT SalesPerson, MAX(SalesDate) AS LastSale FROM SampleData

That query needs another clause: GROUP BY SalesPerson

 

[Luclarie]

Never use FIRST, LAST, DFirst() or DLast(). They just don't work like you think they do.

You're post drowned me in code and irrelevant details, so I am not clear how to help you specifically, so I will in general. In general, instead of LAST in a query, you should build a subquery using MAX, then use that query in another query to produce the exact result you want:

SampleData
SalesDate, SalesPerson, SalesQuantity
1/1/2025, Fred, 7
1/1/2025, Sally, 10
2/2/2025, Sally, 4
2/2/2025, Dave, 9
3/3/2025, Sally, 8
4/4/2025, Dave, 3

To get the last sale record for everyone you would first determine the date of each persons sale:

SELECT SalesPerson, MAX(SalesDate) AS LastSale FROM SampleData GROUP BY SalesPerson

Let's call that query 'LastSalesDate'. It produces:

LastSalesDate
SalesPerson, LastSale
Fred, 1/1/2025
Sally, 3/3/2025
Dave, 4/4/2025

Then you build a new query on LastSalesDate and SampleData to get all the data of the last sale for each person:

SELECT SampleData.SalesDate,  SampleData.SalesPerson, SampleData.SalesQuantity
FROM SampleData
INNER JOIN LAstSalesDate ON SampleData.SalesDate = LastSalesDate.LastSale AND SampleData.SalesPErson = LAstSalesDate.SalesPerson

That will produce all the data of the last sale of each person. When you want it in a form or in VBA you use a DLookup into that final query to retrieve it.

Edit: Fixed first SQL by adding GROUP BY, thanks tvan.

I thank you for your post, but think you could be a little bit kinder in your replies... very rude --- tell me, don't they teach etiquette and code where you come from? I guess that's why it says banishment pending next to your profile....?

PS: I was trying to be as specific as possible for anyone else who has this same issues and, if you look at it at the bottom it says NOTES, which are the different ways I researched on how to create a Dsum / Dlast entry...

 

[NauticalGent]

Welcome aboard, as you can see, some of us are acquired tastes, but the willingness to assist is genuine, that I can assure you.

 

[GPGeorge]

Don't let the 1st impression dissuade you from taking seriously the good advice offered.

DLast, DFirst, etc. are seldom useful when looking at historical data. In fact, it's hard to think of a situation where they are viable, although I'm sure there will be one I haven't thought of. The reason for that is that these expressions are sensitive to the current sort order of a recordset. They refer only to relative position within that current sort order.

You've noted the problem with sequential values in AutoNumbers and in TransactionDates, where they sort differently depending on which field you choose. That's exactly why DFirst, DLast, and so on are not appropriate.

Min and Max and their partners are more reliable. The Max value in a field is always the biggest value, regardless whether it is sorted to the first position, the last position, or somewhere in between.

 

[theDBguy]

Hi. Welcome to AWF!

PS. I have moved your thread out of the introduction forum to avoid confusion. Please feel free to post an intro there if you are inclined to.

Cheers!

 

[Gasman]

Why do people join a forum, then not post for 15 years?

 

[Mark_]

Hi, intermediate novice here and I'm stumped trying to create a very simple DLAST in a query using SQL.

The plan is to create this into vb SQL but for now working with the query grid...

Every day we enter kg. amounts into a tbl, usually it's once a day, but it can sometimes be more than once a day. I'd like to create a dlast to get the last kg. entry in the future entry (it's descending) but I don't want to use the detail ID, what if someone forgets an earlier entry and then has a earlier date but later detail id?

entry_date

So I forced the entry_date into a serial in qry 1, and now in qry 2 trying to create SQL query for Dlast using serial1_date and serial2_date as it would seem the most logical thing to do -- What I'm not sure of is the formatting, thus the code could be wrong...

tbl_daily_entry
entry_date (format date - nothing special)
entry_amount_kg (format single, fixed 2 (looks like this: 00.00)

qry_1
entry_kg:entry_amount_kg
serial1_date: (CDbl([entry_date]))
serial2_date: (CDbl([entry_date]))


qry2:
LAST_ENTRYLast("[entry_kg]","qry_1", [serial1_date] < [serial2_date])

Questions
Is the tbl fields correct formats?
Since serials are now double format shouldn't they register as numbers?

&&&&&&
Any suggestions would be most helpful -
thank you!



DSum (Dlast) NOTES:
' *************************** '

' ***************************
' Typical Use
' Numerical values. Replace "number" with the number to use.
variable = DLast("[FieldName]", "TableName", "[Criteria] = number")

' Strings. Numerical values. Replace "string" with the string to use.
variable = DLast("[FieldName]", "TableName", "[Criteria]= 'string'")

' Dates. Replace "date" with the string to use.
variable = DLast("[FieldName]",, "TableName", "[Criteria]= #date#")

***************************

Referring to a control on a form

' Numerical values
variable = DLast("[FieldName]", "TableName", "[Criteria] = " & Forms!FormName!ControlName)

' Strings
variable = DLast("[FieldName]", "TableName", "[Criteria] = '" & Forms!FormName!ControlName & "'")

' Dates
variable = DLast("[FieldName]" , "TableName", "[Criteria] = #" & Forms!FormName!ControlName & "#")

***************************
Combinations
Multiple types of criteria
variable = DLast("[FieldName]", "TableName", "[Criteria1] = " & Forms!
[FormName]![Control1] _
& " AND [Criteria2] = '" D & Forms![FormName]![Control2] & "'" _
& " AND [Criteria3] =#" D & Forms![FormName]![Control3] & "#")

' Use two fields from a single record.
variable = DLast("[LastName] & ', ' & [FirstName]", "tblPeople", "[PrimaryKey] = 7")

' Expressions
variable = DLast("[Field1] + [Field2]" d , "tableName", "[PrimaryKey] = 7")

' Control Structures
variable = DLast("IIf([LastName] Like 'Smith', 'True', 'False')", "table‐Name", "[PrimaryKey] = 7")

***************************

Looks like you want the TOP value
SELECT TOP 1 entry_date, entry_amount_kg
FROM Tbl_Daily_Entry
ORDER BY Tbl_Daily_Entry.entry_date

Alternate you could use MAX which implicitly only returns the greatest (TOP) value.
In SQL, MIN and MAX are often used, but TOP with either asc or desc on the sort order works just as well plus allows for you to return multiple results when needed.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[The_Doc_Man]

@Luclarie, the biggest problem with using FIRST and LAST is that they change in unexpected ways. We have seen many cases where you edit or update a record in the middle of your recordset and it suddenly becomes the LAST record - because it was the last one touched, chronologically speaking. There is no inherent "date last updated" in a recordset unless YOU programmed one into it.

IF you have a query that includes an ORDER BY clause, THEN you can make some sense out of FIRST and LAST. Without imposing a specific order on your recordset, you cannot trust FIRST/LAST.

Another factor is that if the recordset is updated twice in the same day and you don't include both date AND time in your update time field, you can still get easily confused.