Full Version: DCount vs DMax
UtterAccess Forums > Microsoft® Access > Access Forms
I have been using Dcount to generate an autonumber which seems to work quite well, but was told the Dmax would work better, but I cant seem to get the syntax correct...this is what the Dcount statement looks like...
e.FormID = DCount("DataID", "tblMain", "[MainDate] = #" & Format(Now, "mm/dd/yyyy") & "# AND [Typeid]='TTA'") + 1
when I replace DCount with Dmax I get 82092...can anyone help
thanks very much
Check out How to Create an Incrementing Record Number for a working example.
Hi thanks for the quick response, I am looking at the example, and it seems the Table counter method is best one to use, but it seems very complicated..is there not an easier way around this..also is Dmax better than Dcount when handling this type of problem...
I believe DMax is considered the best practice method as it doesn't need to count through all records in the database, rather just look for the last one.
More than that - Count doesn't imply distinctness.
It's entirely possible for a row to exist which is the 100th row entered - but has an associated ID of 101. (For whatever reason - data entry not through a form - whatever).
The DCount would return 100 rows - making the next ID 101. However that ID already exists in a previous row.
DMax guarantees that you're hitting the highest value entered thus far. The only avenue for duplication is for another user to be entering a new row at the same time.
Max may have a slight performance advantage, however there are distinctly faster ways of retrieving the Count of records in a table than DCount anyway. :-D
(DMax too for that matter lol - but counting all rows should have the edge when not in Domain land).
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.