Parsed Data has space

[Guest]

I have used
AuditNum: Right([AuditName],Len([AuditName])-InStr(1,[AuditName],"= "))
to return the right most numbers from this string:
Update ID = 1824 The number can vary from 1 digit to 10 digits.

For some reason, I get a leading space with the returned results. How can I
get rid of this extra space?

 

[Jeff Boyce]

If you cannot be certain that the string will ALWAYS contain a space after
the "=", surround the expression with
Trim( ...)
to remove leading and trailing spaces.

If you can be ABSOLUTELY certain there'll be a space, add 1 to the position
that your Instr(...) function returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff,
Trim was easy and did just what i wanted.

If you cannot be certain that the string will ALWAYS contain a space after
the "=", surround the expression with
Trim( ...)
to remove leading and trailing spaces.

If you can be ABSOLUTELY certain there'll be a space, add 1 to the position
that your Instr(...) function returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have used
AuditNum: Right([AuditName],Len([AuditName])-InStr(1,[AuditName],"= "))
to return the right most numbers from this string:
Update ID = 1824 The number can vary from 1 digit to 10 digits.

For some reason, I get a leading space with the returned results. How can
I
get rid of this extra space?

 

[Guest]

I think you can see what is going on if you create a new stand-alone module,
and paste this test function in:

Sub Foobar()

Dim AuditName As String
AuditName = "Update ID = 1824"

Debug.Print Len(AuditName)
Debug.Print InStr(1, [AuditName], "= ")
Debug.Print "Right([AuditName], " & Len(AuditName) - InStr(1, AuditName, "= ")
Debug.Print Right([AuditName], Len([AuditName]) - InStr(1, [AuditName], "= "))

End Sub

Open the Immediate window (Ctrl G). With your cursor anywhere within the
subroutine, press the F5 key to run it. You should see results like this:

16
11
Right([AuditName], 5
1824

As you can see, your expression for the given string is evaluating to 5
characters.

Try this instead (split on two lines to avoid word wrap in an inappropriate
place):

AuditNum:
Right([AuditName], Len([AuditName]) - (InStr(1, [AuditName], "= ") + 1))


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________