DLAST FOR DATE SERIAL (1 Viewer)

[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.

 

[RonPaii]

I don't see were Plog was rude unless a post is missing, but stated that the code was hard to follow and provided a possible solution.

 

[Ken Sheridan]

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?

I would not rely on an autonumber column to indicate the sequence of insertion of rows into the table. As you've pointed out this would fall down if the rows are not inserted in entry_date order. The value cannot be manually corrected as autonumbers cannot be edited. Instead I'd suggest you add a date_time_stamp column to the table, with a DefaultValue property of Now().

Provided that rows are inserted in the correct sequence then the value inserted into the date_time_stamp column will usually be ok however the row is inserted, but do not rely on it being so in a form. If a user moves the form to an empty new record, then the default value will be the date and time at which they navigated to the new record. This will not necessarily be the time when they insert the row into the form, however, so you should put the following code in the form's BeforeInsert event procedure:

Me.date_time_stamp = Now()

This will override the value which appears in the bound control. If a user inserts a row out of order, then they can edit the value in the control to an earlier date and time. This will be a PITA for the user, but that will give them an incentive not to forget to insert rows.

The row with the latest date_time_stamp value can be returned with the following simple query:

SELECT *
FROM tbl_daily_entry
WHERE date_time_stamp =
    (SELECT MAX(date_time_stamp)
     FROM tbl_daily_entry);

 

[Luclarie]

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

You really want to know gasman?

Well... Up until about the age of 11 my mom would beat the living daylights out of me... Then in 1974 my dad imprisoned his entire family (being me and my sisters and my mother) for a year... Then my dad got caught and my mom abandoned us kids to the state so I was separated from my sisters and became a ward of the state, moving in and out of foster homes and group homes... So in a nutshell I was beaten, raped and incested... and because of that I suffer from multiple mental conditions, one being severe ptsd...

But what the ptsd did was help me to see patterns, so not having any formal education at all I went on to work various venues... I'm a medical assistant, a real estate agent, admin support and data design using ms access... At one point I was an independant worker helping companies organize certain things including making small specialty databases for places like SC Johnson, Johnson Diversey, AC Nielsen...

But since then, for the past decade I decided to help others in similar situations, mentoring and volunteering my time while I try to deal with day to day life...

Usually my db's are very simple... so when I need something a bit more complicated I look to forums like this to help me out...

So I hope that's okay with you...

 

[Luclarie]

I would not rely on an autonumber column to indicate the sequence of insertion of rows into the table. As you've pointed out this would fall down if the rows are not inserted in entry_date order. The value cannot be manually corrected as autonumbers cannot be edited. Instead I'd suggest you add a date_time_stamp column to the table, with a DefaultValue property of Now().

Provided that rows are inserted in the correct sequence then the value inserted into the date_time_stamp column will usually be ok however the row is inserted, but do not rely on it being so in a form. If a user moves the form to an empty new record, then the default value will be the date and time at which they navigated to the new record. This will not necessarily be the time when they insert the row into the form, however, so you should put the following code in the form's BeforeInsert event procedure:

Me.date_time_stamp = Now()

This will override the value which appears in the bound control. If a user inserts a row out of order, then they can edit the value in the control to an earlier date and time. This will be a PITA for the user, but that will give them an incentive not to forget to insert rows.

The row with the latest date_time_stamp value can be returned with the following simple query:

SELECT *
FROM tbl_daily_entry
WHERE date_time_stamp =
    (SELECT MAX(date_time_stamp)
     FROM tbl_daily_entry);

Hello Ken,

Thank you, yes, that's what I was thinking and not trying to confuse people,
please understand I'm doing my best here to explain which I would think this would be simple but maybe I've complicated:

For query 1, I'm using a table called "a0_dlast_table" and converting the entry date (time stamps) into usable distinct integers:

entry_date
S1_dbl: entry_date (time stamp) into dbl (but fraction much longer),
S1_rounded: round dbl to 5 digits (rounded)
S1_forced:force out decimal (forced)
S1_int change to integer (int) and use as dlast critera...
kg_amount

SELECT a0_dlast_table.kg_amount, a0_dlast_table.entry_date, CDbl([entry_date]) AS S1_dbl, Round([s1_dbl],5) AS S1_rounded5, Replace([S1_rounded5],".","") AS S1_forced, Int([S1_forced]) AS S1_int
FROM a0_dlast_table;

For query 2, has distinct timestamp inter and use with dlast formula:

SELECT qry1.entry_date, qry1.S1_int, DLast("[kg_amount]","[qry1]","[S1_int]<" & [S1_int]) AS kg_DLAST1, qry1.kg_amount
FROM qry1
ORDER BY qry1.entry_date DESC;

--

I just think I have my syntax (or whatever you call it) wrong in the query grid:
kg_DLAST1: DLast("[kg_amount]","[qry1]","[S1_int]<" & [S1_int])

Thanks for any response...

Lisa B.

 

[Ken Sheridan]

I would think this would be simple but maybe I've complicated

I think that's true. As far as I can see the entry_date column includes the time of day, so the first thing to do is ensure that, in table design view the column is indexed uniquely (no duplicates). You then don't need a date_time_stamp column. If you've set its DefaultValue property to Now(), note what I said about the unreliability of this in a form, and put code in the form's BeforeInsert event procedure to assign the return value of the Now() function to it.

The column can then be used as I described for a date-time_stamp column. So the user can change its value to an earlier date/time which slots in between two other rows if necessary. The query to return the last row would then be:

SELECT *
FROM tbl_daily_entry
WHERE entry_date =
    (SELECT MAX(entry_date)
     FROM tbl_daily_entry);

Note that in the above you must use the MAX operator not the LAST operator. The latter does not do what you'd think. To all intents and purposes it returns an arbitrary row. The same is true of the VBA DMax and DLast functions. You must use the former.

To return all rows, e.g. as a form's RecordSource, the query would be:

SELECT *
FROM tbl_daily_entry
ORDER BY entry_date DESC;

In a data entry form you can put the following in the form's AfterInsert event procedure:

Me.Requery

This will reload the form's recordset so the new row will become the first row in the form's sort order, i.e. at the top of the form in continuous forms or datasheet view. In single form view it will be the first record and the form will move to it.

 

[The_Doc_Man]

First: Looking at your manipulation of date/time numbers, it would appear that you are seeking some format of timestamp that would be more efficient, storage-wise? Otherwise, I'm unsure of why you are even bothering to "diddle" with time like that.

The problem I see is that, storage-wise, a date-time field (which is actually a typecast of numeric type DOUBLE) is about as efficient as you could get. It appears that you are looking at trying to truncate the number in hopes of making it shorter?

As of today's date, the Access "date of epoch" is 46,001 which is greater than 32767 (=2^15) and therefore takes 16 bits to represent. But there are 86,400 seconds in a day (greater than 65536=2^16), which takes 17 more bits. That means that a time accurate to the second needs 33 bits to represent it - and a LONG holds only 32 bits. Therefore, the date/time typecast (and its integerized numeric form) need the next size up from LONG. The date/time typecast to DOUBLE is capable of storing times precise to less than one millisecond, assuming you have access to the high-precision clock that is buried in most modern systems these days as part of the internal GHz time-pulse generator.

If your goal is to find a small timestamp, strategies exist to shorten the storage requirements that will work for a long while. Since the fractional part of a day can be manipulated to give you hours, minutes, and seconds, it would be possible to trim the time with a formula like

DIM TimeHM As Long
TimeHM = CLNG( Now() * 24 * 60 )

In this computation, you fold the hours and minutes into the time number but effectively slice off the SECONDS from the number when you then truncate it to LONG format. I.e. that gives you a time stamp accurate to the MINUTE. This truncation will give you a more compact timestamp (half the size of a date/time stamp) and the math for this approach would be valid until some time in the year 2079, at which time it would require another bit to store the adjusted number.

The reason I'm suggesting this is that you were truncating using decimal scaling, which gives you "unnatural" time units. BUT those "integers" aren't integers. Your S1_Rounded is a DOUBLE, 64 bits. S1_Forced is a STRING (because that is what REPLACE returns) of 10 digits which would take not less than 10 bytes or 80 bits. Your S1_Int could be a LONGLONG - but again, that number requires more than 32 bits. If you are on a system that supports LONGLONG numbers, that integer can exist. But if you are on a 32-bit version of access, that number would become a problem.

As to your problem with getting the wrong format back from a DLast, I'm not sure why that would happen. However, you are picking up a value of a query that picked up a value of a table. Normally, the formatting is derived from the source field, but when a query of a query is involved, the source details (including display format) might be unavailable for some reason, leading to a truncation for display purposes. If you can encapsulate the DLast in a FORMAT function, you might see the digits you need. This is a guess because it is hard to trace properties back through layered queries sometimes.