String: space but not a space?

[April15Hater]

Hello all-

Having some trouble with some of my code below.

There are times where the strAccountPrefix_LOB variable is either blank or 2 characters. Because it is being dimensioned as 2 characters, when it is blank, it gives me spaces. The spaces return an asc() of 32, but the spaces will not go away using the replace command. Any help is appreciated.

FWIW, I set the string length for speed purposes because this is going to be a user defined function in a query. I have it as a string because sometimes it will be alphanumeric.

Function RetentionPeriod(strAccountPrefix As String, _
                            strCenterPrefix As String, _
                            Optional strDivision As String, _
                            Optional strLedger As String) As Double
'Standard dimensions
Dim strCenterSearch As String
Dim strSearchString As String
Dim strAccountSearch As String
Dim intAccountPrefix_Len As Double
Dim strRetention As String
Dim intAccountLevel As Integer
Dim intCenterLevel As Integer
'AccountPrefix dimensions
Dim strAccountPrefix_ClassCode As String * 1
Dim strAccountPrefix_GroupCode As String * 1
Dim strAccountPrefix_DetailCode As String * 3
Dim strAccountPrefix_LOB As String * 2
'CenterPrefix dimensions
Dim strCenterPrefix_Division As String * 2
Dim strCenterPrefix_LineofBusiness As String * 1
Dim strCenterPrefix_Marketing As String * 2
Dim strCenterPrefix_Other As String * 3
'Account Number Processing------------------------------------------------------------------------------
'Define Standard Variables
strAccountPrefix = Trim(Left(strAccountPrefix, 7))
strCenterPrefix = Trim(Left(strCenterPrefix, 8))
intAccountPrefix_Len = Len(strAccountPrefix)
'Break out account for ClassCode, GroupCode, DetailCode, and LOB
strAccountPrefix_ClassCode = Replace(Mid(strAccountPrefix, 1, 1), " ", "") '1 position
strAccountPrefix_GroupCode = Replace(Mid(strAccountPrefix, 2, 1), " ", "") '2 position
strAccountPrefix_DetailCode = Replace(Mid(strAccountPrefix, 3, 3), " ", "") '3-5 position
strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), Chr(32), "") '6-7 position

 

[mdlueck]

Maybe in this area:

  'Break out account for ClassCode, GroupCode, DetailCode, and LOB
strAccountPrefix_ClassCode = Replace(Mid(strAccountPrefix, 1, 1), " ", "") '1 position
strAccountPrefix_GroupCode = Replace(Mid(strAccountPrefix, 2, 1), " ", "") '2 position
strAccountPrefix_DetailCode = Replace(Mid(strAccountPrefix, 3, 3), " ", "") '3-5 position
strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), Chr(32), "") '6-7 position

try...

  'Break out account for ClassCode, GroupCode, DetailCode, and LOB
strAccountPrefix_ClassCode = Replace(Mid(strAccountPrefix, 1, 1), Chr(32), vbNullString) '1 position
strAccountPrefix_GroupCode = Replace(Mid(strAccountPrefix, 2, 1), Chr(32), vbNullString) '2 position
strAccountPrefix_DetailCode = Replace(Mid(strAccountPrefix, 3, 3), Chr(32), vbNullString) '3-5 position
strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), Chr(32), vbNullString) '6-7 position

for consistency.

Also I see that there is another character code for the space character. So perhaps you are dealing with Unicode data?

"chr(32) = space? integer, ascii, local characters?"
http://www.vbforums.com/showthread.php?t=611878

 

[spikepl]

#1 What do you actually wish the fixed-length string should contain? Logically speaking, the thing holds on to the " ", for what else is there? You can force it to be virgin again, by replacing " " with Chr(0).

#2 You did not run/try your code to see what it gives, did you?

 

[speakers_86]

Completely unrelated, but what part of Tampa are you from? I am from Land O Lakes. Only a few minutes from Wiregrass Mall.

 

[gemma-the-husky]

can you not just use trim, ltrim, or rtrim to strip out unneeded leading/trailing spaces?

 

[April15Hater]

@mdleuck:
I tried that code and no luck.

@spikepl,
It is ultimately going to be used in a query's criteria. So the query is failing because of the space. Otherwise two alpha-numerics will be in there. I tried the following codes and no luck there either:

strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), Chr(32), Chr(0))

strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), " ", Chr(0))

@speakers86,
Feather Sound...St. Pete area

@Gemma,
I tried the following codes that did not work either:

strAccountPrefix_LOB = Trim(Mid(strAccountPrefix, 6, 2))

strAccountPrefix_LOB = LTrim(Mid(strAccountPrefix, 6, 2))

strAccountPrefix_LOB = RTrim(Mid(strAccountPrefix, 6, 2))

All,
Is there a carriage return character that it might be?

Thanks,

Joe

 

[mdlueck]

All,
Is there a carriage return character that it might be?

If you are not sure what character is actually there, then I would suggest going through the string character by character by character and decode using Asc:

Dim MyNumber
MyNumber = Asc("A") ' Returns 65.
MyNumber = Asc("a") ' Returns 97.
MyNumber = Asc("Apple") ' Returns 65.

Perhaps do that in a Debug.Print and receive the output in the Immediate window of the VBA editor.

 

[April15Hater]

I originally tried that, the spaces return an asc() of 32 which is what is so darn frustrating about this problem.

From immediate window:

?asc(right(strAccountPrefix_LOB, 1))
 32 
?asc(right(strAccountPrefix_LOB, 2)) 
 32

 

[spikepl]

Did you read my post in full? You did not answer the question as to what you want to be inside this fixed-length string. "No luck" does not signify anything to any outsider. WHat is it to be?
You cannot set it to vnNullstring, that's for sure.

 

[April15Hater]

It is going to be either 2 alphanumeric characters or blank- "". That is why I am trying to get rid of the spaces.

 

[spikepl]

"" is not blank - it is an empty 0-length string. As long as you have a fixed-length string then you cannot force it to shed its length.

 

[mdlueck]

"" is not blank - it is an empty 0-length string. As long as you have a fixed-length string then you cannot force it to shed its length.

"I would like to order 32 meg worth of NULL." Gee, I wonder why the box is so light!

 

[spikepl]

#12 Yeah. Or two times empty, as the case may be here