link to numeric values stored as text in Access

Hi Peter, real and float numeric fields can't accept text, that's why you can't map the float or real field types to text fields in Access. The only real difference between float and real is the precision but both are specifically for numeric data only (IEEE 754 spec I think?). The Numeric field is the original Micromine Numeric field that is really just a text field in the DAT text file and can therefore handle text, for example Numeric field types can handle a minimum detection of <0.005 whereas the real and float data types need -0.005, or you can use N/A or N/S instead of some esoteric number like -9999 or -7777 to define these. This is why you have the Numeric Exceptions button on many forms - to tell MM how to handle the text in a Numeric column. This is also why the Numeric field worked for you - it is essentially one text field talking to another. The trouble is the DAT files can become very big so MM went from a simple text file to a binary file to reduce the size of the file and that allowed them to introduce the Real and Float field types. I assume that if the Assay fields have been set up in Access as text fields it is because the data contains < / NA / NS type text to define below detection / not assayed / not sampled data. I can think of no other reason as to why you would make Numeric data text in a database, Access will not accept text values into a Float or Double numeric field for the same reason Float and Real in Micromine does not accept them. This being the case you will only ever be able to map the Numeric field data to your Access Database fields.

That said you probably should get an error from Micromine that tells you that you can't do it (and why?) and to use Numeric data type instead. Perhaps this is one of those occasions when the developers simply assume the user would know the difference and not actually try to map the data this way rather than acknowledging that many users would not automatically know this.

Thanks Ronald. I knew how the Numeric type works. What threw me off was that the link form gave me the option of using Reals or Floats. I figured that if the software gives me the option, it must have a way of handling it. I imagined that perhaps MicroMine would do some sort of on-the-fly conversion. Anyway, now I know.

As for the reason why I use text fields for numeric data in Access, it's because I want the database in Access to contain exactly what is shown on, say, an assay certificate. It isn't just the matter of representing "<". It's also the fact that any software can only store real numbers as approximations. Very precise and accurate approximations, yes, but still approximations. I don't want to load "3.53" from an assay certificate into Access and then find that when I export it to some other software to work with it I'm getting "3.529999999997" or some such thing.

If the certified value is "3.53" and my database contains something else, no matter how close, then I don't have the certified value any more.

When I want to do calculations within Access I convert the text to numbers on the fly.

I feel your pain - learnt the hard way when I was a DBA that 3.53 in real or float doesn't always mean 3.53. I have never found anyone who has been able to definitively explain to me why 3.53 might go into one field as 3.530000 but in another field down the hole it goes in as 3.5299999 - Why is nothing ever simple??????