Cstr function versus Str function.

[jal]

I thought the Str function was supposed to change a number to a string. It didn't throw any errors in my query - but it just didn't return any records on the join. I was joining on an ID number, but the ID number was datatyped as string in one table, and as integer in the other table. So I did something like this:

INNER JOIN ON Str(table1.ID) = table2.ID

Returned zero records. So I just put a "C" in front of the "Str" function (i.e. I used the Cstr function). This worked fine.

So this leaves me wondering, what exactly does the Str function do? Because I'd like to know why it didn't help me do the join.

 

[neileg]

Str includes a leading space for a positive number. This may be the issue.

 

[jal]

Str includes a leading space for a positive number. This may be the issue.

Brilliant. Yes, you're right that extra spacing is the issue, because I just tested what you said, by adding the TRIM function to the join - in this case Str works fine. Thank you!

On the other hand I find this behavior a little weird and unexpected. Developers have enough headaches and surprises to deal with already, so I hope Microsoft had a good reason for this one.

But thanks again.

 

[Banana]

FWIW, whenever you want to do a conversion, the functions should be C-something (e.g. Cstr, Clng, Cint, Cbool).

But yes, they could have made it a bit more clear.

 

[neileg]

The space is there so that the string length is the same for a negative number. The minus sign occupies the space.