Combining Fields in a Query (1 Viewer)

mikej0077

Registered User.
Local time
Today, 08:14
Joined
Aug 19, 2019
Messages
15
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.
 

Attachments

  • Database1 - Copy.accdb
    572 KB · Views: 5

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:14
Joined
Aug 30, 2003
Messages
34,746
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

I’m here to help
Staff member
Local time
Today, 00:14
Joined
Oct 29, 2018
Messages
13,202
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

Wino Moderator
Staff member
Local time
Today, 00:14
Joined
Aug 30, 2003
Messages
34,746
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

Registered User.
Local time
Today, 08:14
Joined
Aug 19, 2019
Messages
15
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

I’m here to help
Staff member
Local time
Today, 00:14
Joined
Oct 29, 2018
Messages
13,202
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):
Code:
UPDATE TableName SET [comp age restriction] = NULL WHERE [comp age restriction]=""
Hope it helps...
 

mikej0077

Registered User.
Local time
Today, 08:14
Joined
Aug 19, 2019
Messages
15
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

Wino Moderator
Staff member
Local time
Today, 00:14
Joined
Aug 30, 2003
Messages
34,746
This appears to work:

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

mikej0077

Registered User.
Local time
Today, 08:14
Joined
Aug 19, 2019
Messages
15
Hi. Assuming the blank [comp age restriction] where actually empty strings, then you could try this (make sure you have a backup copy first):
Code:
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

Registered User.
Local time
Today, 08:14
Joined
Aug 19, 2019
Messages
15
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

I’m here to help
Staff member
Local time
Today, 00:14
Joined
Oct 29, 2018
Messages
13,202
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.
 

Users who are viewing this thread

Top Bottom