vba not recognizing str = " "

[Full_Williams]

Hi,

I've searched thru the forums, but haven't found any problem similar to mine.

I'm trying to strip a field of spaces...certain fields have one space as the string and the code doesn't seem to recognize these fields. If str = " " the code doesn't recognize it. Here it is:

str = rst0![SchoolComments]

If lenstr = "" Then
rst0.Edit
rst0![SchoolComments] = Trim(str)
rst0.Update
ElseIf lenstr = " " Then
rst0.Edit
rst0![SchoolComments] = Trim(str)
rst0.Update
End If

Has anybody ever had this problem? Any suggestions would be appreciated.

Thanks,
Full Williams

 

[dcx693]

What is lenstr?

 

[Mile-O]

Also, why bother checking for spaces? Just Trim it anyway.

 

[Full_Williams]

sorry I copied the wrong piece of code....
rst0.movefirst
Do Until rst0.eof
If rst0![SchoolComments] = "" Or rst0![SchoolComments] = " " Or rst0! [SchoolComments] = " " Or rst0![SchoolComments] = " " Then
rst0.Edit
rst0![SchoolComments] = Null
rst0.Update
End If
rst0.movenext
Loop

I tried just trimming and it didn't work. My main problem is that the code isn't recognizing the fields (rst0![SchoolComments]) when it = " " ...it just passes on thru.

Any thoughts?

 

[Mile-O]

If rst0![SchoolComments] = "" Or rst0![SchoolComments] = " " Or rst0! [SchoolComments] = " " Or rst0![SchoolComments] = " " Then

[/QUOTE]

Why make the same comparison three times?

Have you tried:

If IsNull(rst0![SchoolComments]) Then

 

[dcx693]

Why do you have rst0![SchoolComments]=" " four times in your If statement?

And, it's been a while, but I don't think
rst0![SchoolComments] = Null
will work. I don't think you can set the value of a text field to Null. You should try to set it to "" (two double quote marks with nothing in between).

If the code isn't recognizing rst0![SchoolComments], then there's something very basic that's wrong. Are you sure SchoolComments is the name of a field in the recordset? Try doing MsgBox or Debug.Print rst0![SchoolComments] in your code so you can see what's going on.

 

[Bat17]

Are you sure that it is a space there? where did the data come from? With imported data you can get hard spaces and things which show up as a gap but are not " ".

If the fields should be longer than 1 char or empty then you could always check for length with the len() function instead.

Peter

 

[Full_Williams]

rst0![SchoolComments] is in the if statement 3 times because it's looking for 0, 1 or 2 spaces ("", " ", " ") - just experimenting with different things to try to make this work.

The data came from a webform and was submitted with ASP code. Guess I should trim that going forward before the dump. Seems as though the user accidentally hit the space bar in the html text field but then decided not to write anything.

the code recognizes rst0![SchoolComments] it's just doesn't recognize when the length (Len()) = 1 and is = to " ". Maybe it's a hard space? What can i do if it's a hard space?

Thanks for your responses...

Any other thoughts?

 

[Bat17]

you can use the asc() function to find out what the charecter are and then replace them

Peter

 

[Full_Williams]

Thanks Peter - that worked!!

Appreciate the help.

Full Williams