fixed width text imports

[BigWheels]

In a table I have imported from a fixed width text file, one field is defined
(in the import specs) as a text field 2 characters wide. Various text
characters are imported correctly, like "NP" or "00". However, when it runs
across the two characters "0 " (that's zero with a space following) it trims
the field to just "0". Well, I need that space! Why is Access getting rid of
it? It's a text field, not a number field. (I have a field that sometimes has
a space before other characters and it leaves it alone.) The problem is,
later queries will look for the second character in that field which sometime
should be a space.

Any suggestions? Is there another field format that will treat the space as
a character to keep track of?

Thanks!

 

[John Nurick]

Access's Jet database engine routinely trims trailing spaces from text
fields as you import them, and as far as I know it's not possible to
stop it. It also trims trailing spaces entered via the user interface,
but not via VBA or a query.

Probably the best thing would be to modify your other queries so they
don't expect the trailing spaces. Alternatively, you can restore the
spaces by running an update query, e.g.

UPDATE MyTable
SET MyField = MyField & " "
WHERE MyField='0'
;

though if the user edits the fields the trailing spaces will be lost
again.