Trimming blank Space query

[Oz1127]

I've used the trim, left and Mid functions before but, I am having some trouble trimming blanks out of a field. The field is formated as such: Doe , John. Notice the blank spaces? I tried different variations (Mid([Wnab],InStr([Field4],',')+1) of functions and arguments but to no avail. Not sure why the data was entered in this fashion, makes no sense to me. Anyway, if someone could help me out with deleting the blanks that would be great.Thanks in advance gyz.

 

[PHV]

What about this ?
Trim(Mid([yourField], InStr([yourField],',')+1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Oz1127]

Tried it but it removes everything before the ,. Doe , John replaced by John. Thanks a lot though, you helped get me started PHV. I'll try playing with the replace function to see if that helps. Not sure whether the Trim function will do it or not. Thanks again.

 

[PHV]

What was the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Shouldn't this be in the query forum (forum701)?

Yup, I'd think the replace function too, which would probably need a "wrapper" function in a standard module (in VBE - Insert Module)

[tt]public function myreplace(byval v_varIn) as variant
myreplace=replace(v_varIn " ,", ",")
end function[/tt]

the call it with myreplace([yourfield]) within the query.

Roy-Vidar

 

[Oz1127]

I was trying to get the original (Doe , John) replaced with (Doe,John) just ommitting the blank spaces in the fields. Sry for not posting in the correct forum

 

[PHV]

So, you wanted this ?
Trim(Left(yourField, InStr(yourField,",")-1)) & "," & Trim(Mid(yourField, InStr(yourField,",")+1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Or alter the replace string in the function from [tt]" ,"[/tt] to [tt]" , "[/tt]

Roy-Vidar

 

[Oz1127]

You're the bomb PHV. That worked like a charm. I owe you 10, LOL. Thanks.