? on Formatting of Text in Field---Mixed Text (Numbers & Letters) 1

[jodphd]

I am having trouble properly formatting a field in an Access table defined as text. It is defined as text since some records contain both numbers and letters. These records were imported as comma-delimited text into Access.

The specific problem is on long numbers instead of displaying the complete string it displays the following example:
4.24615E+12

This should be a number only display. If it was not for the fact that some fields contain letters I would set up this field as a number.

Does anyone have any suggestions?

Thanks.

 

[MichaelRed]

Is the 'scientific" notation the ONLY case where alpha characters are in the field? If so, Ms. Access should be able to intrepert them as "numbers" - but NOT integer types. Set the field type to NUMBER, but the sub-type as Double and it SHOULD work (If the letters are just the scientific notation).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jodphd]

This field contains characters other than "scientific notation." Does anyone have any other suggestions?

Thanks

 

[MichaelRed]

Fall back to the basic. Import everything as TEXT. Groom (VERIFY and VALIDATE) each record field. Where errors/problems are found, either 'repair' the entry or move it to a temporary table. When 'all is good', move the remaing entries to where they will be used/processed. Return the "BAD" records to the source for review and, where possible, correction and re-processing.

I generally advise (and adopt) this process as the default for accepting froegin information anyway. It is a big time saver in the end, as the production processes are a lot more efficient when they do not need to contend with non-standard fields. The V&V processing follows fairly simplistic guidelines, like:

[tab]Text fields are trimmed
[tab]Text fields are checked for length (compared to The TARGET DB length spec)
[tab]Numeric fields are checked for IsNumeic
[tab]Date fields aare chedked for IsDate

If specific ranges of fields are known, thes are also checked, such as If dates should all be between Date() and #1/1/1995#, this is part of the process.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over