Combining Fields in a Query

[mikej0077]

Hi there,

It's been a long time since I used Access, so I am having to familiarize myself with it. So apologies if this is an obvious question.

I am trying to combine a number of fields into one field to create the body, (in HTML), of my product descriptions.

If the field is populated I want it to put:
'SOME TEXT' - 'THE CONTENTS OF THE FIELD'<br />

If the field is blank, I don't want anything for this part. At the moment, when the field is blank, I get:
'SOME TEXT' - <br />

Here is what I have put:
Body (HTML)1: "<p>" & [ShortDescription] & "<br />" & [LongDescription] & "</p>" & ("Age restriction - "+[comp age restriction]+"<br />")

The age restriction bit is what I am stuck on, as it returns the following for all the blank fields:
Age restriction - <br />

I want to do this for all the COMP fields but am just asking about the COMP AGE RESTRICTION for simplicity sake.

I have attached a copy with some sample data.

 

[pbaldy]

The trick you're using works if the field is Null, which yours isn't. I added this to your query:

Expr1: Nz([COMP AGE RESTRICTION],"null")

and it doesn't return "null" for any of the fields returned. You'll have to use an IIf() or something to test for an empty string.

 

[theDBguy]

Hi. Welcome to AWF! It might be easier to use an UPDATE query to change all your blank fields to Null, so your original code would work. Just a thought...

 

[pbaldy]

I thought about that, but you'd have to run it every time to account for new data, and/or stop it at the source.

 

[mikej0077]

Hi. Welcome to AWF! It might be easier to use an UPDATE query to change all your blank fields to Null, so your original code would work. Just a thought...

That sounds like a plan... I'm still familiarizing myself with access so if you could tell me how I do this, I would appreciate it!

 

[theDBguy]

That sounds like a plan... I'm still familiarizing myself with access so if you could tell me how I do this, I would appreciate it!

Hi. Assuming the blank [comp age restriction] where actually empty strings, then you could try this (make sure you have a backup copy first):

UPDATE TableName SET [comp age restriction] = NULL WHERE [comp age restriction]=""

Hope it helps...

 

[mikej0077]

The trick you're using works if the field is Null, which yours isn't. I added this to your query:

Expr1: Nz([COMP AGE RESTRICTION],"null")

and it doesn't return "null" for any of the fields returned. You'll have to use an IIf() or something to test for an empty string.

I had tried a couple things along these lines... but I suspect the same thing is causing them not to work:
Body (HTML)2: IIf(IsNull([COMP BOX QUANTITY]),"","Box quantity - " & [COMP BOX QUANTITY] & "<br />")
and
Body (HTML)2: IIf(Nz([COMP BOX QUANTITY]),"","Box quantity - " & [COMP BOX QUANTITY] & "<br />")

 

[pbaldy]

This appears to work:

Body (HTML)1x: "<p>" & [ShortDescription] & "<br />" & [LongDescription] & "</p>" & IIf(Nz([comp age restriction],"")="","",("Age restriction - "+[comp age restriction]+"<br />"))

 

[mikej0077]

Hi. Assuming the blank [comp age restriction] where actually empty strings, then you could try this (make sure you have a backup copy first):

UPDATE TableName SET [comp age restriction] = NULL WHERE [comp age restriction]=""

Hope it helps...

Is there a quick way to do that for the entire table?

 

[mikej0077]

This appears to work:

Body (HTML)1x: "<p>" & [ShortDescription] & "<br />" & [LongDescription] & "</p>" & IIf(Nz([comp age restriction],"")="","",("Age restriction - "+[comp age restriction]+"<br />"))

That looks like it has done the trick! Thanks mate!

 

[theDBguy]

Is there a quick way to do that for the entire table?

Hi. That was for the entire table. But, don't worry about it, if you already got it sorted out by using the above solution from Paul. Good luck with your project.

 

[pbaldy]

That looks like it has done the trick! Thanks mate!

Happy to help!