Decimals ending in zero

[duecesup]

I have a table labeled tblcatalogs. There is a num field that tracks the accession numbers of a collection of artifacts. duplicate artifacts are given a decimal place.

I.E. item # 10 has two duplicate items labeled 10.01 and 10.02

For items with over 9 duplicates I am having a problem with the ending zero not staying in the numeric field.

IE 12.10 is converted to 12.1 automatically. I would create a mask that places an ending zero in a query however if I ever had 100 duplicates, the ending zeros would convert to .1 and would create a duplicate entry. Is there a way to make access keep the ending zero.

Converting to a text field is not an option because the catalog number will not sort correctly but will sort as

1
10
11
2
20
3
30

Thanks for the assistance

 

[gemma-the-husky]

i suppose you could split the reference

have a field for main category, and a field called subcategory

i suppose thats what it really means anyway - its not atrue decimal reference, is it?

and that way, you could easily group similar artifacts

 

[Mike375]

Could you make a another field that is a number field and use that for the sort? Use an update query to fill the number field with the values you entered in the text field or a little afterupdate event that set the value of the number field when you made an entry in the text field.

 

[duecesup]

Interesting Idea Mike but no because the sort in the text field is still off when placed in a report or form

 

[statsman]

Try changing the format on your field to 0.00 (zero point zero zero).

This will require a whole number plus 2 decimals. The two decimals will be zero unless you enter a number after the decimal.

The downside is that your first entry for an item will be the whole number (example 12). The second will have to be 12.01

 

[duecesup]

Thanks Statsman,
a whole number for the first entry is not a problem. but I do question how the format will print in a form or report; will 12 print as 12.00 and will 12.10 print as 12.1?

 

[boblarson]

You should have the number stored as text if you want it a certain way. Then you can have the same field added into the query and use CDbl([YourField] to convert it to a number and set the sort on it.

 

[Atomic Shrimp]

If there's any remote possibility of more than 99 duplicates, you may need to rethink your numbering strategy anyway - because 12.10 and 12.100 are the same thing, when stored in a numeric field - and there's no way around that - because it's a fact of mathematics, not database design.

You could decide that 12.1 should actually be entered as 12.001, but then if you get more than 999 instances of the same artifact, you're back up against the same problem.

I think I'm right in saying that you're actually storing two pieces of data in a single field here, which is a (OK slight) violation of normal form. It would probably be better to store the accession numbers alone in one field, then the duplicate IDs (default value zero) in another, so you'd be able to perform a nested sort on accession number, then duplicate ID.

You can set a mask so that zero in the duplicate ID field comes out as blank, and you can always text-concatenate the two fields for reporting (but still do a nested sort as above), then it will come out as:
12
12.1
12.2
12.3
...

12.9
12.10
...
12.99
12.100
etc

 

[statsman]

Thanks Statsman,
a whole number for the first entry is not a problem. but I do question how the format will print in a form or report; will 12 print as 12.00 and will 12.10 print as 12.1?

If you set the format for the text boxes in the report or form the same way (0.00) it should work fine. It always has for me.

 

[jwhite]

OP:
Seems to me you have (2) values in (1) field -- breaking them apart will simplify the formatting/sorting for you. And converting them to Text is best because they are references - not 'live numbers', as Bob and several UA members have suggested to you.

 

[Llobid]

Have you considered formatting the field as 0.000 so that even up to 999 duplicates would be handled? Then your original would be 12.000, then your first duplicate would be 12.001, next 12.002, etc. The sorting would work fine also. If you went into the properties of your field in the form, you can tell it to format as 0.000 also and it should display fine.

 

[Atomic Shrimp]

I think there's a very real danger that a user would enter 12.1 or 12.01 when they were meant to enter 12.001.

The duplicate ID is a separate value from the accession number and really needs its own field.

 

[boblarson]

I think there's a very real danger that a user would enter 12.1 or 12.01 when they were meant to enter 12.001.

The duplicate ID is a separate value from the accession number and really needs its own field.

Gotta throw my agreement behind Mike here.