Importing fields with fixed blank spaces!! URGENT!

whats the field type in the table that you're importing into?

The field type is text.. it just wont keep the spaces in there.  I'd have to manually put the spaces in.

In your example "323    ", that has 7 total characters.  Would that field ALWAYS have 7 characters.  So:
"1234567"
"123456 "
"12345  "
etc
or would the total length vary?

Walt

Access will strip any prepending and trailing spaces by default when the values are stored in the table. Nothing you can do.

However if the fields are fixed width as Walt is already asking you can create a query that format the data, you can then use this query to save the data back. So for a 7 character column you use the following format:

select format(fieldOne, "!@@@@@@@"), fieldTwo from yourTable

Hi dudinatrix,

And would the Spaces be always after the Numbers

I hope this helps.

jaffer

hey Walt,

Saw your Q yesterday about the MS Cal 8.0, how'd you get it working.. tried to sus it out, but before i was done i saw you deleted the Q.  Just curious..

Dave

If the number is fixed, import into a temp table.  Use an append query to move it to the final table.  This query will pad your data:

SELECT Table3.yourField, padString([yourField],5) AS newField
FROM Table3;

Where the 5 is the length you need it to be after padding spaces.
Here's the code to paste into a module:
Public Function padString(ByVal currentString As String, desiredLength)
Dim newstring, j
Dim curLen
newstring = currentString
curLen = Len(currentString)
For j = 0 To desiredLength - curLen - 1
    newstring = newstring & " "
Next j
padString = newstring
End Function


Make sense?
Walt

Well done heer.

flavo,
yea I probably should have left it up.  Sorry about that.
There are some hidden properties.  I put this in the onLoad:
Me.cldDate.GridFont.Size = 8.5
Me.cldDate.DayFont.Size = 8.5
Me.cldDate.TitleFont.Size = 12

dudinatrix:
You have enough to go on?
Walt

ahh... found the GridFont in the Obj borwser, but it wanted IFontDisp..  Checked the stdole32 lib but no luck there, i was close i spose, just needed the data tpyes used in IFontDisp, same prob you had i guess..

Glad you got it out anyway.

Dave

Thanks for all the help guys...

heer:  I tried your advice, using

"select format(fieldOne, "!@@@@@@@"), fieldTwo from yourTable"

But its still coming out blank.




walterecook:  I like the sound of what you're saying, but I've never used Modules in ms access before. I'm do use VB, so its familiar to me.. but how do I use the modules?  I put your code into a module called padString... and I tried calling it from a query, but it gives me an "Undefined function "padString"" error.  any more advice?


Thanks again... hopefully I can get this done with your help!

>>But its still coming out blank.
Could you be a bit more specific?

The format function will pad the field with spaces, the exclamation mark puts the spaces behind the value instead of in front. If you export this query to a comma delimited file the values will be padded.

have you tried creating a import specification to see if that helps?

In MSAccess, go to Get External Data/Import, select CSV spreadsheet,
Selected Delimited, clicked on Next
At this point I clicked on Advanced
This takes u to specification, now just play with the settings
you can set fieldnames, datatypes, skip fields etc
might be something there to keep spaces

then save this specification


then when importing, always use this specifcaiton

>>>>But its still coming out blank.
>>Could you be a bit more specific?

By "blank" I mean an empty field.  In my first post, I said one of the fields is going from "         " to "" on the import.  I tried using your !@@@@@@@@@@ command to change that "" back into "          ", but it still comes up as the blank "", with no data in the field.

Same for the "323    " string.  It still comes out "323"



>>have you tried creating a import specification to see if that helps?

Yup, no luck!

Did you export the query to a comma delimited text file?

Thanks you guys!  In the end, it was walterecook's padstring function that did it.. so thank you very much!

Thanks to all of you for your help.. you're all lifesavers.