DLast won't return value higher than 255?

[dcabrera]

Good day! First off, I want to thank the community as a whole for the invaluable information you have provided. I have been teaching myself Access/VBA while building a database/checkout system in the last few weeks and would have nothing to show for it had it not been for the questions and insightful answers I've found on this site. So you're awesome.

That being said, I've finally come across an issue for which I can find neither question or answer.

The cliff's notes of the problem is this (Using Access 2013):

When I run DLast on one of my tables, it has continued to give the result as 255 ever since I reached record #255. I don't know if it's a coincidence that it's exactly 255 or if that means something.

It is a problem, because I use DLast to populate a field on a separate form (It reads the last ID# and uses it to populate a combobox on the main form, saving me from typing a name multiple times to assign it).

This technique was working up until I hit record #255. Since then, it's remained at 255. I've tried storing the DLast statement as a variable first with no luck. I understand 255 is the maximum characters for Short Text, but this doesn't deal with strings or text, just an integer.

I tested with a msgbox and it returns 255 no matter what. When I changed the parameters to a different table, but the same kind of Autonumber ID field, it returned the actual last number (well above 255).

For more info, here's the background of the project:

I am putting together a database/checkout system for a book room at an Elementary School. Unlike a library, the the book room has fewer titles, but many more copies than a library would, since teachers regularly check out between 4-16 copies of each book (since they have to be distributed to multiple students). The pertinent book information is:

Title
Author
Reading Level
Genre (fiction,non,bio,folktale,etc)
Usage (guided reading,big book,book/tape)
Location (we have two buildings with two book rooms)

Reading Level, Genre, Usage and Location are all 1-many relationships, and Authors is a many-many relationship with tblJunctionBookAuthor table connecting them.

After much tooling around, youtube viewing and forum crawling, I have a solid form for entering new books into the database.

The form has a subform which allows me to assign multiple authors from an existing list (pulls from tblAuthors) or, if the author doesn't exist, add a new one (List-Limited with a popup form linked to tblAuthors). Up until I hit AuthorID=255, the system was working flawlessly. I would enter my new author information in the popup form, then when I hit "done", it would save the record, setfocus to the AssignAuthors subform, and populate my combobox with the data that corresponded with whatever my new DLast value was. Since #255, however, when I add a new author, the dlast function returns 255 and the combobox populates with the same wrong name each time.

As it stands, I have over 600 titles entered in this database, with 263 authors and 800-some book-author associations. However, I'm not too far past #255 that I couldn't delete the records back to that point, but that would be relatively time consuming (it's only 13 authors back, but it's over 50 titles up the list).

If anyone has encountered this or if something clicks, any information would be great. As it is, I can type over the incorrect name each time, but it adds a step to data entry, and as we all know, every extra step gets multiplied by the number of records, and I still have 5000 to go...

Any help would be awesome. And of course, if specific information is needed, please ask.

Thanks!

[dcabrera]

Don't know why I didn't think of this sooner:

As a workaround, I've switched to DMax and that works. Since I'm using the ID field, it should always return the latest entry. Still doesn't explain why DLast wouldn't do the same thing...

[twinnyfo]

I was just going to suggest using the DMax Function instead, but I guess you already figured that one out....

Great news!

[Seth Schrock]

I just tried using DLast() on one of my databases and it returned a far larger value than 255. We would need to see your code (please use the [CODE/] button to post your code) in order to see what is wrong. 255 is the highest value that can be stored in a single byte, so I would guess that it has something to do with how it is being used.

[dcabrera]

Here's the code that uses the DLast function:

Private Sub Form_Close()
    Forms![frmBookEntry02]![frmSubJunctionBookAuthor]![cboAuthorSelect] = DLast("[AuthorID]", "tblAuthors")
 
End Sub

My test button uses basically the same format and returns the same result:

Private Sub Command0_Click()
    Dim n As Integer
    n = MsgBox(DLast("[BookAuthorID]", "tblJunctionBookAuthor"), vbOKOnly, "Here's your stuff.")
End Sub
 

Now to further complicate things, it appears it's doing this on all of my tables, but the result of DLast varies from table to table. I'm thinking the 255 is just a coincidence.

If I run:

Private Sub Command0_Click()
    Dim n As Integer
    n = MsgBox(DLast("[BookID]", "tblBooks"), vbOKOnly, "Here's your stuff.")
End Sub
 

I get 660, though my last record in tblBooks is 718.

Similarly, if I run:

Private Sub Command0_Click()
    Dim n As Integer
    n = MsgBox(DLast("[BookAuthorID]", "tblJunctionBookAuthor"), vbOKOnly, "Here's your stuff.")
End Sub
 

I get 743, while the actual last value is 910.

Interestingly, BookID 660 doesn't correlate to AuthorID 255 and neither of those correlate to BookAuthorID 743, so it's not like all of the tables started doing this at the same junction record.

Again, I can just use DMax, but it is a bit worrisome that a function is returning incorrect values and my hope is that there isn't some sort of corruption in the db. Or if this is even how it would manifest. Granted, if that IS the only manifestation of a corrupt database, I'll take it if it means I don't have to redo the whole thing.

Thanks for the help!

[twinnyfo]

If I remember the DLast Function correctly, it has nothing to do with the actual value, only the last item in the list. So, if you sort your table/query one way, you will get one result, and sorting another way gives another result.

So, you have to know exactly what you are looking for and how your table/query is sorted first.

This is why the DMax function would be preferred in this case, when assigned to the AutoNumber ID field of the table/query.

Either way, you are still getting very strange results with this function.

[Seth Schrock]

I just tried it myself, and twinnyfo is correct. It depends on the order of the records. So if you have the following data

27704.23
892550.41
12555.07
210.19
9374.89
282076.09
25167.2
271313.22

DFirst() = 27704.23
DMin() = 210.19
DMax() = 292076.09
DLast() = 271313.22

If you sort this data, then your DMax() and DLast() would be equal and DFirst() and DMin() would also be equal.

[twinnyfo]

Still can't fathom the "255" issue, though....

[Seth Schrock]

We would have to see the data and how it was sorted. Sometimes users put sorts on the table instead of doing through queries and this would affect it.

[dcabrera]

Right, I get the idea of DLast versus the rest. I got that from here, initially:

http://www.accessallinone.com/database-functions/

So, just for kicks, I took screen shots of the end of each of my tables, (and the relationships view) to show that the number of records and the Autonumber are different (because of deleted records, obviously). But none of the numbers are matching the DLast result:

Based on this information, if I'm using the ID field for my DLast command, it shouldn't return the values it's returning. It should return the same thing as DMax. Again, not terribly concerned as long as DMax does what I need it to do. Though if I need to use DLast later and it's still glitchy, it might be an issue.

Edit: I should add that the "sort order" for the reocrds is the order in which they've been added to their respective tables. So while the titles look alphabetical, it's because I'm entering them from an alphabetized list, not because they're sorted that way. And actually, on the printed list, they're sorted by Level first, then title. But what you see in those lists is the order in which the data has been entered.

[dcabrera]

Just double checked my tables. The "Order By" box in the table properties is blank for all of them.