Bug in Str()

Cotswold

Active member
Local time
Today, 02:00
Joined
Dec 31, 2020
Messages
649
This has probably been there for ever but I've only just found it.
I have a complex function that concatenates a contiguous string, that I have used for some time in another language. I have just converted it into Access and it was falling about, for no apparent reason. In this routine at specific locations, specific numbers are inserted, which are read back from another function later.

In Access it does appear that the Str() function carries with it an additional leading space that other languages do not. This has the result of moving the converted number to the right. In most cases this will not matter or be noticed as it is probably displayed on the screen or printer in a TTfont.
However, in this example it does matter as it places the numbers at an incorrect position and also extends the length of the completed string with each number inserted.

I doubt that MS will correct this but if anyone is using Str() in a concatenated string they will need to use Trim(Str()) to get it right.

I have prepared a simple example showing how Str() is not correct in Access. It is the same in Access2010, 2019 32bit and 2021 64bit.

Code:
    Dim LeftStr, RightStr, AllStr, LenStr As String
    Dim MidStr As Long
    
    LeftStr = "01234567890"
    RightStr = "9876543210"
    MidStr = Len(LeftStr)
    AllStr = LeftStr + Str(MidStr) + RightStr
    
    MsgBox AllStr + "     <This has the extra space"
    LenStr = Trim(Str(MidStr))
    AllStr = LeftStr + LenStr + RightStr
    MsgBox AllStr + "     < with extra space removed"
    AllStr = LeftStr + Trim(Str(MidStr)) + RightStr
    MsgBox AllStr + "     < with extra space removed"
 
I had a problem like this, but never thought it may be something about str()
I solved it by using &

In your case you can use :
Code:
AllStr = LeftStr & MidStr & RightStr

and the result is :
01234567890119876543210 <This has the extra space
 
Yep!........CStr() does it of course:mad:

If I transfer code from PureBasic in the main it goes Ok, with some Function differences of course. In this case I spent too long looking and not seeing.
 
Dim LeftStr, RightStr, AllStr, LenStr As String
1. Only LenStr is defined as a string with this statement. All the others are variants.
2. The & is the standard VBA concatenation operator. The + will work for concatenation as long as both operands are not numeric. It also respects Null whereas the & does not. What that means is xyz & Null = xyz But xyz + Null = Null. This behavior is very handy when you are concatenating several strings and don't want multiple spaces between first and last names as happens when middle name is null.
 
MS: "When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied."


Glad to read your post, though, I had never noticed this. I think I've usually used CSTR
 
CJ has it right

Try changing
Code:
MidStr = Len(LeftStr)

to
Code:
MidStr = -Len(LeftStr) 'negative
 
The leading space for positive numbers ensures they are aligned with negative numbers
 
+ is used for addition for concatenation of numbers you should be using ampersand.
 

Users who are viewing this thread

Back
Top Bottom