Odd behaviour of recordset (1 Viewer)

[John Sh]

On occasions I have the need to copy an entire record from a data table. Since the accession number is unique and cannot be repeated, it is necessary to change the accession number before creating the new, copied, record.
If the accession number has no decimal point or the decimal point goes no higher than .2, there is no problem. If, however, the decimal point is higher than .2 the results can be incorrect.
I.e copying 10886 will return 10887. 10886.1 will return 10886.2 and 10886.2 will return 10886.3 consistently and 10886.5 will return 10886.6.
in my example db 10886.1 returns 10886.2, 10886.3 and 10886.6 depending on the table and method used. 10886.6 is the correct result since 10886.5 already exists.

In the sample db attached, the file tTemp is a cut down copy of my main data table. The table tTemp2 is a copy of the five highest numbers in tTemp so the data in both tables has been copied and pasted from the original table for a degree of consistency.

My question basically is why the different results and how do I ensure consistency in the results regardless of the data presented?

My descriptions sometimes leave a bit to be desired so I will let the code do the talking.

 

[plog]

You started out explaining one thing, then you threw this form at us that is somehow related but I can't figure out. I think the issue with your function is that you are just loading data into recordsets but you are not explicitly stating an order. You never filter or sort the recordset you just take what it gives you and there is no way of knowing what the first nor next number is going to be. It could be sorting alphabetically by Family or Genus and the numbers it is returning happens to correspond with those records when they are put in that order.

Again though, I don't think that matters because I don't think that's the way to achieve what you want. I think all you really need is a DMax(https://www.techonthenet.com/access/functions/domain/dmax.php) to get the largest existing number. Then all you need to do is add .1 to it to get your next number.

Right? You only care what the largest existing number is and then you want to add .1 to it for the new record.

 

[John Sh]

You started out explaining one thing, then you threw this form at us that is somehow related but I can't figure out. I think the issue with your function is that you are just loading data into recordsets but you are not explicitly stating an order. You never filter or sort the recordset you just take what it gives you and there is no way of knowing what the first nor next number is going to be. It could be sorting alphabetically by Family or Genus and the numbers it is returning happens to correspond with those records when they are put in that order.

Again though, I don't think that matters because I don't think that's the way to achieve what you want. I think all you really need is a DMax(https://www.techonthenet.com/access/functions/domain/dmax.php) to get the largest existing number. Then all you need to do is add .1 to it to get your next number.

Right? You only care what the largest existing number is and then you want to add .1 to it for the new record.

Unfortunately it's not that easy. The base of the number must remain the same where there is a dot point and only the dot number must increment. If a number higher than the one being copied exists then the subsequent number would have the wrong base and probably the wrong dot point.

Sorting the recordset has fixed the problem but why would the "findfirst" construct not find the new number in the subsequent iterations?
Thank you for your help. It is appreciated.

 

[The_Doc_Man]

The obvious question to me was "What is the database's data type of this accession number?" I peeked at the DB and the answer is DOUBLE, so your problem is possibly due to rounding issues. You NEVER store a number as you described in DOUBLE format precisely because of potential rounding effects.

What is the possible range of the number? (How many digits? How many decimals will you need?) Because there are ways to store it in one format and yet display it in another format.

 

[Galaxiom]

I would strongly recommend you split the Accession number into two fields, both integers. The main number and the revision number (or whatever it is after the dot). Much simpler to manage that way.

Then concatenate them with a dot for display.

 

[arnelgp]

change your AccessionNumber field from Double to Decimal (scale = 5)

 

[John Sh]

Thank you all for your input. Much to think about.
So far, sorting the recordset seems to have solved my initial problem.
Doc Man: The numbers involved will be unlikely to exceed 25,000 and the possibility of more than one decimal point is extremely remote.
Arnelgp: I notice that the decimal type is slower than double but I doubt this would have any impact on overall performance of this system as there are no calculations within the system apart from that outlined in the O.P.
Glaxiom: Splitting the number would create a violation of the unique field as it would require multiple copies of the same number.

 

[Galaxiom]

Splitting the number would create a violation of the unique field as it would require multiple copies of the same number.

Place a compound unique index on the pair of fields.

 

[arnelgp]

Arnelgp: I notice that the decimal type is slower than double but I doubt this would have any impact on overall performance of this system as there are no calculations within the system apart from that outlined in the O.P.

post #4 is right, there is always a problem with double datatype.
i tried using Dlookup() when your data is still double, and most of the
numbers with decimals (already in table) are missed by Dlookup (saying it does not exists).

changing it to decimal and using Dlookup again, nothing is missed.

if you are not convinced import Form1 from this db and run it.
input 10886.1, and it will show numbers already in the table.
change the field to decimal (table tTemp) and try again.