edit of record data with expression

[benzhu]

Here is the situation: A data field holding the concentrate values, which range from 20% (0.2) to few ppb (1e-12) - very wide range. I try to have user enter data in corresonding range: for example: in unit of %, or ppm, g/Ton... But like the database always store the consistent data without unit information. I was not able to bound the field to any expression - that prevented it from editing. Is there work around for these situations?

Thanks
:banghead:

 

[spikepl]

Machines are not people and do not deal well with mixed-type information. If you wish to store a value and also the unit do so in two separate fields.

 

[benzhu]

As you suggested, the database store the grade and the unit in two seperated field. Now when display the rate, and the unit is displayed following the grade data like:
o.10 %, or 0.000000021 ppm
in second case, it will be hard for user to figure out how many 0s leading the number. So what I want to find a way to display grade (0.000000021) according to the unit (% or ppm, or ppb user selected) -- like 0.021 ppm, or 21 ppb. And when encouter high grade like 0.12, user will be able to use % to enter data like 12 %. The unit is a seperate field display behind the value. The problem I have is access will record the 12 instead 0.12 without conversion - or I don't know how to make it work

 

[spikepl]

Have a third field in the table - a field to hold the actual value to be used in calculations.
E.g. user inputs 0.01 and % and in the AfterUpdate event of each control you then set that third field to 0.0001 etc etc

Edit: In fact, a third field is redundant. For calculations, the content of the first two fileds is enough for you to be able to calculate at any time the absolute numerical value

 

[benzhu]

here is data
field: metal grade(absolute) unit(%, ppm and so on) unit symbo
record 1 Au 0.0000012 0.0000001 g/T
record 2 Fe 0.32 0.01 %

Form control: | metal name | | grade| symbol
the unit/symbol is used based on metal type - in case for Au, want to display:
Au 1.2 g/T
For iron,
Fe 32 %
Grade field display value = grade / unit. However, if I use expression in grade field, the grade textbox became read only (not editable). If grade text box control by grade, it will display 0.0000012 for Au - it is difficult for user to count 0s. Is any way I can keep record in grade, but control the user input in sync with unit?

 

[spikepl]

do not store grade(absolute) but calculate it from grade(userinput) and unit.