Tuesday, July 24, 2007
Concatenate function
Oh, man, I can't tell how useful that concatenate function is.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.
For example, look at this print screen:
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.
One more print screen:
In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.
For example, look at this print screen:
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
=PERSONAL.XLS!concatfunc(A1:A4,CHAR(34) & " or " & CHAR(34))
, and the return string from this function is listed in cell B1.Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.
One more print screen:
In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.
Labels: String Operations
