Access 2007 Cstr() Versus Str() SQL Functions 1

[chunter33]

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 converts a number to a string but leaves an additional trailing blank space to accommodate negative numbers with the minus symbol at the beginning (i.e -500). However, I tested this out in Access 2007 and compared the results 2 identicle queries, one with cstr and one with str and they gave the exact same results, even for negative numbers!

I guess what I'm really asking is, can one get by simply by using cstr exclusively? Are there any occasions that you can think of where str is absolutely necessary? Because I've yet to see it...

Thanks in advanced!

 

[lameid]

As far as I know they are equivalent...

Jet SQL (Native Access) supports VBA (Visual BASIC for Applications) functions. Str is a function that dates back to BASIC (think 8 bit computing, DOS /Commodore Vic 20's and possibly things older than I remember). cstr on the otherhand is newer and made alongside the companion conversion ("c" functions; i.e. clng, cdbl etc.).

Because STR is the older version I recommend to use cstr exclusively. If there is a differnce, I would expect it to be in how long of a result that can be returned. Cstr being written originally post 8 bit computer MIGHT be a bigger or more dynamically sized result.

 

[chunter33]

Thanks lameid, they certainly do seem to be equivalent.

Can anyone confirm that cstr handles negative numbers (with the minus symbol) the same way the str function the same within Access 2007?

From what I've seen, it does, but a second or third opinion certainly never hurts.

 

[MajP]

They are not equivalent AFAIK. I believe CSTR will cast any data type to a string sub data type. STR works only on numbers and returns the string representation of a number.

 

[MajP]

http://msdn.microsoft.com/en-us/library/aa263370(v=vs.60).aspx

http://msdn.microsoft.com/en-us/library/0zk841e9(v=vs.85).aspx

 

[lameid]

I didn't even think about it but MajP is definitle correct that STR only takes numeric inputs.... However for dates I would recommend the format function instead so you get the text you want every time.

 

[chunter33]

Hmmm, seems like it. I think my chances are better off with cstr, however, despite the extra work that is being done behind the scenes. I don't feel very comfortable with the white space that str is adding into the mix.

 

[TheAceMan1]

How are ya chunter33 . . .

There's another important difference.

[blue]Str[/blue] always provides a leading space for the sign of the value if it positive (+).

[blue]CStr[/blue] stripes all leading and trailing spaces! ... Try it in the immediate window! ... Examples:

[tt]Str( 12345 ) = [COLOR=black red] [/color]12345
Cstr( 12345 ) = 12345[/tt]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chunter33]

The question is whether or not the space is ever truly needed. Cstr interprets a nagtive sign perfectly fine. Where would we ever really need to explicitly have the positive sign in there, I find it to be a little redundant to be perfectly honest as the lack of a negative symbol usually denotes a positive value anyways.

 

[TheAceMan1]

chunter33 . . .

I agree. However it does come into play as far as formating is concerned ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]