trailing null characters

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi

I have fields where the data has trailing null characters, how can I "trim
them away"? In mysql I would use SELECT TRIM(BOTH NULL FROM FieldName) ...

I don't know in Access wich is the syntax.

Tim.
 
Tim said:
Hi

I have fields where the data has trailing null characters, how can I "trim
them away"? In mysql I would use SELECT TRIM(BOTH NULL FROM FieldName) ....

I don't know in Access wich is the syntax.
Click to expand...

Trim([FieldName])
 
Trim removes blanks, but it doesn't remove my null chars :-(


Rick Brandt said:
Tim said:
Hi

I have fields where the data has trailing null characters, how can I "trim
them away"? In mysql I would use SELECT TRIM(BOTH NULL FROM FieldName) ...

I don't know in Access wich is the syntax.
Click to expand...

Trim([FieldName])
Click to expand...
 
HI:

A null is a blank

what characters do you see?


Tim said:
Trim removes blanks, but it doesn't remove my null chars :-(


Rick Brandt said:
Tim said:
Hi

I have fields where the data has trailing null characters, how can I "trim
them away"? In mysql I would use SELECT TRIM(BOTH NULL FROM FieldName) ...

I don't know in Access wich is the syntax.
Click to expand...

Trim([FieldName])
Click to expand...
Click to expand...
 
See if this will work.

Trim(Replace([FieldName], vbNullString, ""))

--
Wayne Morgan
MS Access MVP


Tim said:
Trim removes blanks, but it doesn't remove my null chars :-(


Rick Brandt said:
Tim said:
Hi

I have fields where the data has trailing null characters, how can I "trim
them away"? In mysql I would use SELECT TRIM(BOTH NULL FROM FieldName) ...

I don't know in Access wich is the syntax.
Click to expand...

Trim([FieldName])
Click to expand...
Click to expand...
 
Tim said:
Trim removes blanks, but it doesn't remove my null chars :-(
Click to expand...

There is no such thing as a Null character. If there is padding of
"blanks" in the field then they are either spaces or some other
non-printing character. Non-printing characters normally show up as boxes
or vertical bars though.

I suppose if there were non-printing characters (other than spaces) that
were being displayed as blanks that the Trim() function would ignore them,
but I have never heard of that.
 
I guess that you are getting these fields from a source other than Jet. I think
that to do what you want you are going to have to write a vbfunction to trim the
null characters.

Here is a little sample function to show you the concept

Public Function fMakeStringWithNullCharacters()
Dim strTest As String
Dim lCount As Long


strTest = "ABC"
MsgBox Len(strTest)

strTest = strTest & vbNullChar & String(20, 0)
MsgBox Len(strTest)

For lCount = Len(strTest) To 1 Step -1
If Mid(strTest, lCount, 1) <> vbNullChar Then
strTest = Left(strTest, lCount)
Exit For
End If
Next lCount

MsgBox Len(strTest)
End Function
 
Back
Top