Need some help with a For Loop

[tssparky]

Hi everyone,


I'm using this piece of code to remove any "zero's" at the start of whatever number a person puts into the text box (Text38), this works great.

Dim RemZero As Integer
 For RemZero = 1 To Len(Text38)
 If Left(Text38, 1) = "0" Then
 Text38 = Right(Text38, Len(Text38) - 1)
 End If
 Next RemZero

I tried to modify it to remove any Spaces as well and although it seems to work, im not sure if the loop is ending as the code screen flickers like its running code. (Only works for spaces at the start of string)


(I used this to remove the space at the beginning of the string but not sure if it's correct even though it seems to work..)

Dim RemSpace As Integer
 For RemSpace = 1 To Len(Text38)
 If Left(Text38, 1) = " " Then
 Text38 = Right(Text38, Len(Text38) - 1)
 End If
 Next RemSpace

What im trying to achieve is to remove any spaces in the "string" used as a filter to run a search query..

ie if imputed number was.. (00 1234 1234 1) I need to remove the zero's (at the start of the string only) and also any spaces it the whole string.

So above would look like this prior to the filter running. (123412341)








Hope you understand what im after and thanks for looking.


Regards


Nick

 

[Gasman]

I'd use the Replace function to get rid off all spaces, then use your zero function.
Also you could exit the loop when you find a non zero value?

 

[CJ_London]

you can also use the int function to remove initial zeros, no loop required

int(replace(Text38," ",""))

this will return a number rather than text, if you want text then add a ""

int(replace(Text38," ","")) & ""

 

[Gasman]

CJ_London,

What about non leading zeroes?
I know there were none in the example, but still...?

you can also use the replace function to remove zeros

replace(replace(Text38," ",""),"0","")

 

[CJ_London]

not sure what happened there - I changed it to int before saving it

 

[tssparky]

you can also use the int function to remove initial zeros, no loop required

int(replace(Text38," ",""))

this will return a number rather than text, if you want text then add a ""

int(replace(Text38," ","")) & ""

Thank you for that, unfortunately i'm getting an error.
When i paste

int(replace(Text38," ",""))

I get

Compile Error
Expected: Identifier

And "int" is highlighted
Tried as many ways as I could think of (still a beginner), even crashed access twice lol.....

Any others ways to write this??

Thanks again.

 

[CJ_London]

not sure why you are having a problem - this is what I get in the immediate window

So suspect it is to do with your Text38

 

[tssparky]

not sure why you are having a problem - this is what I get in the immediate window

So suspect it is to do with your Text38

Yeah it's strange, not sure why.

 

[isladogs]

You got an error because you didn't tell Access what to do with that line. Try

Me.text38=int(replace(Text38," ",""))

 

[CJ_London]

also, although not the reason for the error you have placed the code after your loop, so it won't prove anything. You use it instead of your loop

 

[tssparky]

You got an error because you didn't tell Access what to do with that line. Try

Me.text38=int(replace(Text38," ",""))

Got it all working, Thanks a lot

Don't know what i was thinking to miss that..

Me.Text38 = Int(Replace(Me.Text38, " ", ""))

Ended up using this as it's a string and works as intended.

Me.Text38 = Replace(Me.Text38, " ", "")

Thank you all for the help