Remove space format

[Clandon]

I have attempted to format an imported text field to be a fixed length of 9 for some fields that contain spaces.

ex:
sub-id
56892
000056 78
0000A 123

I thought the Format(Trim([sub-id],"000000000&quot) was great and have used it sucessfully in the past-or so I thought.

The format works great for the data that has leading spaces, but not at all for the spaces in the middle of the field. I've also tried the IIf(InStr([sub-id]," &quot,Left([sub-id],InStr([sub-id]))-1,(stuck at this point)) to get the space.

Any advice or ideas as to why the format is not working with the spaces in the middle of the field? Can anyone provide the format expression to correct this?


I'm ready to this field!!!
Thanks in advance-
C

 

[Golom]

IIf(InStr(1,[sub-id]," &quot>0, _
Left([sub-id],InStr(1,[sub-id]," &quot-1) & _
Mid$([sub-id],InStr(1,[sub-id]," &quot+1), [sub_id])

will remove an embedded space in the field. If there's more than one then it gets a bit messy.

Another way to Pad a string (besides Format) is

Left$("000000000" & [sub_id], 9)

 

[BiggerBrother]

Try:

replace(myString, " ","&quot

removes any blank spaces from any string element.

BB

 

[Clandon]

Thanks Golom and BB.

BB: Access doesn't like the replace command in design view.

Golom: Your formatting code worked great except for cases that I had 2 spaces, so I tried to alter the one space with 2, but can't quite seem to get it to work. Here's what I used.Test: IIf(InStr(1,[sub-id]," &quot>0,Left([sub-id],InStr(1,[sub-id]," &quot-1) & Mid$([sub-id],InStr(1,[sub-id]," &quot+1),IIf(InStr(1,[sub-id]," &quot>0,Left([sub-id],InStr(1,[sub-id]," &quot-2) & Mid$([sub-id],InStr(1,[sub-id]," &quot+2),[sub-id]))

Any other words of wisdom? Thought I'd need the 2 to account for moving over the 2 spaces, but I guess that's not what it wants.

 

[Golom]

I had assumed that you were doing this with SQL. If you are writing code then you have a bit more control.

Do Until InStr(1,[sub-id]," &quot = 0
[sub-id] = Trim$(Left$([sub-id],InStr(1,[sub-id]," &quot-1) & Mid$([sub-id],InStr(1,[sub-id]," &quot+1))
Loop

"Replace" is what you really need but it's not available in Access97, only in Access 2000 or better.

 

[Clandon]

Hi Golom:

Thanks for the response. I can't use sql as I am creating an upload query from a raw txt file. I'm getting close, but having trouble keeping the format of 9 digits to stick on the fields with 2 spaces. I will remember the advise for the Replace when I am writing SQL as I do have MSA2000.

 

[GoDawgs]

Golom's code should do the trick...still no luck?

 

[Clandon]

Hi GoDawgs:

My boss wants the raw data files to have certain processes. First I import the raw data into tables in MSA2000, then I am manipulating and formatting using queries to load to SQL. He wants this as automated as possible, and wants them in a query.

I have just noticed that the format is not working on fields that contain 2 spaces and an Alpha character,(even though the field is held as text in the table),so I'm trying diff formats for now. Wish I could use Goloms code but that is not an option for me. Any other formatting tips you could provide would be great.

thanks for all of your responses.

 

[GoDawgs]

So you're importing a text file into a table first...then using queries to format and such, correct? You can call functions in a query...I would set up Golom's code as a function that you call in the query, then that field should be formatted correctly. Let me know what I missed.

Kevin

 

[Clandon]

Kevin:

Guess I'm not used to calling procedures, be glad to learn from you how to proceed with it though,

thanks,

Cheryl

 

[GoDawgs]

Well stealing Golom's code (although I changed it a bit, I'm not sure if the original way works or not), I would do something like this:

Function FixIt(TheData as string) as string
Dim MyString as string

MyString=TheData

Do Until InStr(1,MyString," &quot = 0
MyString= Trim$(Left$(MyString,InStr(1,MyString," &quot-1) & "0" & Right$(MyString,len(MyString)-InStr(1,MyString," &quot))
Loop

FixIt=MyString

End Function

Then in your query you would just create a column that looks like this:

TheResult: FixIt([sub-id])

I'm not positive about the function working, so you might have to walk through that a bit and figure that out, but it'll get you started at least. Hope that helps.

Kevin

 

[Clandon]

Thanks Kevin,

I'll give it a shot and let you know how it goes!

Thanks