concatenate with + sign for both leading and trailing characters

[btappan]

I have some data that I need to concatenate that each field requires both leading and trailing data for XML purposes.



My query looks like this:

"<NAME>" & [tABLE].Name & "</NAME><AGE>" & [tABLE].Age & "</AGE><GENDER>" & [tABLE].Gender & "</GENDER>


Which outputs:

"<NAME>Bob</NAME><AGE>31</AGE><GENDER>Male</GENDER>


I've tried using the + operator instead of the & in various ways, but don't get a clean output when a field is blank so that no XML tags are present on a blank field. Any Help?

[Dal Jeanis]

If you are building a query in VBA in a string it would look like this...

sqlSTR = "<NAME>" & [tABLE].Name & "</NAME>" & _
         "IIF(IsNull([tABLE].Age, '', '<AGE>' & [tABLE].Age & '</AGE>') & _
         "IIF(IsNull([tABLE].Gender, '', '<GENDER>' & [tABLE].Gender & '</GENDER>') ...

Or if you are building a query in SQL view it would look like this

"<NAME>" & [tABLE].Name & "</NAME>" & IIF(IsNull([tABLE].Age, """", "<AGE>" & [tABLE].Age & "</AGE>") & IIF(IsNull([tABLE].Gender, """", "<GENDER>" & [tABLE].Gender & "</GENDER>") ...

YOu'll have to figure out how many quotes go where...

[ssanfu]

when a field is blank so that no XML tags are present on a blank field.

@Dal
Shouldn't the code be??

sqlSTR = "<NAME>" & [tABLE].Name & "</NAME>" & _
         "IIF(IsNull([tABLE].Age, "", "<AGE>" & [tABLE].Age & "</AGE>") & _
         "IIF(IsNull([tABLE].Gender,"", "<GENDER>" & [tABLE].Gender & "</GENDER>") ...

If the age is null, don't show the tags, if not null, show the tags.
If the gender is null, don't show the tags, if not null, show the tags.

[Dal Jeanis]

Yup. I started out thinking of using NZ, and never mentally flipped the order. Also, I had a completely incorrect number of quotes for that to work - forgot to double all the internal double quotes.

I've fixed it to use single quotes inside the doubles, but the code is still aircode.

[btappan]

Thanks guys, I'm using SQL view, is your code below correct then or not? I'm getting an Syntax error

Full query copy and pasted from SQL view:

SELECT "<FIRST_NAME>" & [Table].FirstName & "</FIRST_NAME>" & IIF(IsNull([Table].Age, """", "<AGE>" & [Table].Age & "</AGE>") & IIF(IsNull([Table].Gender, """", "<GENDER>" & [Table].Gender & "</GENDER>") AS Test From Table

And yes, my testing table is named "Table" and my field names are FirstName, Age, and Gender

If you are building a query in VBA in a string it would look like this...

Or if you are building a query in SQL view it would look like this

"<NAME>" & [tABLE].Name & "</NAME>" & IIF(IsNull([tABLE].Age, """", "<AGE>" & [tABLE].Age & "</AGE>") & IIF(IsNull([tABLE].Gender, """", "<GENDER>" & [tABLE].Gender & "</GENDER>") ...

YOu'll have to figure out how many quotes go where...

[btappan]

Never mind, figured it out, too many quotes and unbracketed FROM table. Thanks guys.

SELECT IIf(IsNull([Table].[FirstName]),"","<FIRST_NAME>" & [tABLE].[FirstName] & "</FIRST_NAME>") & IIf(IsNull([Table].[Age]),"","<AGE>" & [tABLE].[Age] & "</AGE>") & IIf(IsNull([Table].[Gender]),"","<GENDER>" & [tABLE].[Age] & "</GENDER>") AS Testi
FROM [Table];

[Dal Jeanis]

Good job! Please mark thread solved. Top of page, under "thread tools".