char datatype? (not varchar) (1 Viewer)

[madEG]

Hello,

Does MS Access support the char datatype? I just don't mean strings/text in general, but a fixed length string - vs varchar?

I need to build a loadfile for our health care provider and their fixed format loadfile prefers that certain information start on certain columns. e.g. The first 9 chars are SSN, then the next 25 are for fname, the next 25 for lname...

123456789FIRSTNAME                LASTNAME                 [end of line]

... the idea is to pad out the remaining characters with blank spaces, having the data values be left adjusted.

Of course I could grab the length of the characters and subtract that from the fields available length, and concat the needed spaces... but... well. I was wondering if I didn't have to.

All I see if the text datatype. Is there something more in Acc2010?

Thanks!

-Matt G.

 

[jdraw]

Ms access text Datatype is for char /string

You can build a "loadfile" as fixed width, or you can use csv etc.

 

[gemma-the-husky]

having said that there is only a string, many "char-type" operators act on the first char in a string

eg asc(string) will return the ascii value of the first char.


generating a fixdth width text file is a trivial problem though. you will find it quite straightforward

 

[Banana]

Just to be clear here; there's two different factors here:

1) can we store a fixed length string?

2) can we output a fixed length string?

Both questions are actually different because as gemma implied, you can export a text file that's a fixed-width and base it off data that are variable length but padded with spaces during the export. That meant you don't need to store the paddings within the database so you could just use Access' Text data type and specify maximum length (e.g. 25 characters?). Your data would only take as much as they actually need and your file would be smaller but when you export out into a text file, you can have it to be padded with spaces so it's always 25 character wide for that one column.

To answer the first question, yes it is possible but not interactively. The table designer let you select "Text" which is actually varchar. To create a char column, you must use DDL like "CREATE TABLE t (c CHAR(20));". But as explained above, you don't actually need it to be fixed length to be able to export a fixed width text file.

HTH.

 

[madEG]

Thanks folks.

I think I'll go with making the tables using the standard text datatype (effectively varchar) and then figure out the export using fixed width (char). This will be easier for the person after me to maintain, I imagine.

Fantastic - Thanks again folks!