CSTR concatenation error

[snow-raven]

I have a question about a specific behavior of the CSTR() function. I was using STR() to convert integer fields to text before concatenating the converted fields, a text field, and some new text with "&". However, this added a space before the text numeral that I couldn't have in my export table.

Code looked like this:

Blow_Counts: IIf(IsNull([Query].[First_Integer] & [Query].[Second_Integer] & [Query].[Third_Integer] & [Query].[Fourth_Integer]),"",(Str([Query].[First_Integer]) & ("\n"+Str([Query].[Second_Integer])) & ("\n"+Str([Query].[Third_Integer])) & ("\n"+Str([Query].[Fourth_Integer])) & ("\n"+[Query].[Text])))

Output looked like this:
1\n 3\n 4\n 5\n90/2
Desired output:
1\n3\n4\n5\n90/2

Following mdlueck's thread "Str() seems to add a space character, why?", I switched my STR functions to CSTR.

However, that created error if one of the integer fields was blank. (For example, "1\n3\n4\n5" and "1\n3\n4\n5\n90/2" were fine, but I got an error on "1\n3\n4" or "1\n3\n4\n90/2"). I do not get the same error for these entries if I use the STR() function, so what is it about the CSTR function that changes the behavior with the & operator?

Note: I do not need assistance with a new function. I went back and used mdlueck's TRIM(STR()) solution. I just want to understand why CSTR was giving me an error if an integer field was blank.

 

[isladogs]

Hi snow-raven

This was moderated for some reason. Posting to trigger email notifications

Suggest you post a link to md lueck's code. Although I know the user name, i've no idea what code you're referring to and others may have the same issue

As you have less than 10 posts, you may not be able to post a link without it being moderated again. If so, someone can approve it for you

 

[pbaldy]

I just want to understand why CSTR was giving me an error if an integer field was blank.

CStr() will error if the value is Null, which is what an integer field will be if blank. Perhaps adding the Nz() function will help. Note your test for Null concatenates the fields together, so it wouldn't be met if any of them have a value. You might also use the fact that + propagates Null, while & does not.

 

[The_Doc_Man]

Here is why STR() does what it does:

From this article:

https://support.office.com/en-us/article/str-function-7bae2c01-47ff-4bdc-a1b6-756fbb515841

The relevant excerpt:

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() further absolutely requires that you give it a number as input. For instance, if you gave STR() a string input, that would signal an error. STR() as a function has been around since BASIC was still called Beginner's All-purpose Symbolic Instruction Code. I'm thinking late 1960s. And it has ALWAYS been that way.

CSTR() is different in that it works on any expression you supply but is cognizant of the data type. If you give it an ambiguous data type, you will get an ambiguous result.

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

Relevant quote:

Use the CStr function to provide conversions from any data type to a String subtype. CStr forces the results to be expressed as a String.

Here's the problem: If you give CSTR() a Boolean variable (as opposed to a Boolean value stored in a Variant variable), you get back "TRUE" or "FALSE" because CSTR() honors the data type of the input, EVEN THOUGH internally, VBA stores TRUE as -1 and FALSE as 0. If you give CSTR() an empty Variant, you might get back blanks.

And hint, almost all functions return Variants. If you store the results of a function into a variable that isn't a Variant, an implied conversion occurs across the equals sign, so the variable is OK and doesn't change data type. But if you supply a function as direct input to CSTR() and that function returns nothing for some reason, I don't know how to predict what you would get.

 

[snow-raven]

Yes, thank you both. I'm having some forum errors for not having made enough posts. I'd better get busy trying to be helpful.

isladogs, I had initially tried to pull the link but received an error message, which is why I replaced it with the name of the thread. I don't think it will even let me get to the moderator stage. Maybe because I was starting a new thread? I'll try it here. (NOpe, I was again forced to delete the link in order to post. If you want to see the intended link it was on this forum at /forums/showthread.php?t=221839)


pbaldy, thank you also. I realized right after posting that the "CSTR was giving me an error if an integer field was blank" part of my own message should have given me the clue. I couldn't think of a way to null-proof it that was more elegant than mdlueck's TRM(STR()) solution but I will play around with NZ(). Thanks also for your other tips; yes, I do want a blank cell only if there are blanks in all four cells. I suppose I could drop it in this version now, it was initially meant to chase out some spare text in a sister-field that I stole my formula from...

Thanks, both!

 

[isladogs]

Thanks. Here's the link if anyone is interested
https://www.access-programmers.co.uk/forums/showthread.php?t=221839

 

[snow-raven]

Thanks, also, Doc_Man. The parts about str() adding a space for positive numbers were covered in the thread I attempted to cite, but I can't post links yet, so I need to make more replies. So here I am, replying!!

I'm afraid I'm not understanding your comments about cstr() behavior for Boolean or Variant variables. The only thing I'm supplying cstr() is an integer field, not a function. If the field is blank, the returned value is #Error

Thanks for your time replying.