Bug in Str() (1 Viewer)

[Cotswold]

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.

    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"

 

[arnelgp]

i don't think it is a bug.
see: MS Access: Str Function (techonthenet.com)
you can use CStr() to convert to string?
see: MS Access: CStr Function (techonthenet.com)

 

[KitaYama]

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 :

AllStr = LeftStr & MidStr & RightStr

and the result is :
01234567890119876543210 <This has the extra space

 

[CJ_London]

Not really a bug, just old tech. The space is there in place of a +

Str will only take a number whilst cstr takes an expression

see this link

Access 2007 Cstr() Versus Str() SQL Functions - Microsoft: Access Other topics - Tek-Tips

Hello, I was wondering what the difference was between using Cstr() and Str() in Access 2007 queries. Where would you use either? From what I've heard, str conv

www.tek-tips.com

 

[Cotswold]

Yep!........CStr() does it of course

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.

 

[Pat Hartman]

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.

 

[Isaac]

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."

Str function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

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

 

[Cronk]

CJ has it right

Try changing

MidStr = Len(LeftStr)

to

MidStr = -Len(LeftStr) 'negative

 

[isladogs]

The leading space for positive numbers ensures they are aligned with negative numbers

 

[Mindflux]

+ is used for addition for concatenation of numbers you should be using ampersand.

 

[isladogs]

+ is used for addition for concatenation of numbers you should be using ampersand.

Whilst as a general rule you are correct, both work and give exactly the same output in examples like post #1