Automatically removing lines when a field has no value

[timmygrover]

So, I'll begin begin as I'm sure everyone else does and say that I'm new to Access. I try to Google as much as I can, but some questions I just don't know how to ask.



SO

I'm using a query to concatenate data from a table into a specific HTML format. I would like, however, to find a way to remove lines of HTML when there is no data in a specific field.

Example:

If I were to create a list of available colors of a shirt like so:

<ul>
<li>[Color1]</li>
<li>[Color2]</li>
<li>[Color3]</li>
<li>[Color4]</li>
</ul>

but if there were only 3 available colors, I'd end up with this:

<ul>
<li>[Color1]</li>
<li>[Color2]</li>
<li>[Color3]</li>
<li></li>
</ul>

I need to find a way to get rid of that last vacant tag. I image there's some kind of code I could write (if x is null, clear line17, or something like that) but I wouldn't even know where to start with that.

Any help would be appreciated!

[jzwp11]

Welcome to the forum!

If you have articles (shirts) that can come in a number of colors but not all articles come in the same colors that describes a many-to-many relationship. (An article comes in many colors. A color can apply to many articles (but not necessarily all)). As such the color related to the article should not be fields in a table but records in a related table.

To properly set up the table structure for this type of relationship, you need 3 tables.

tblArticles (hold the clothing articles; each article would be record)
-pkArticleID primary key, autonumber
-txtArticleName

tblColors (holds all possible colors, each color is a record)
-pkColorID primary key, autonumber
-txtColorName

tblArticleColors (holds each different combination of article/color as records)
-pkArtColorID primary key, autonumber
-fkArticleID foreign key to tblArticle
-fkColorID foreign key to tblColor

So for your shirt example, you would have 3 records in tblArticleColors (one for each color for which the shirt is available). With that you would have your 3 lines to concatenate.

[timmygrover]

Thanks for the quick reply! I can see how your suggestion would work jzwp11, but I fear that my example may have been too simplistic. I'm not actually working on shirts, but on computer motherboards, which have SO many variations that maintaining a table with all possible variations would be impossible (especially since new ones pop up constantly). It would be more useful if I could find a solution that would work regardless of the variations. Another solution that's occurred to me would be to have the query run a mass-replace function to remove all the instances of <li></li>. Is there a way to have a query run a mass replace?

[jzwp11]

but on computer motherboards, which have SO many variations that maintaining a table with all possible variations would be impossible (especially since new ones pop up constantly).

I'm not sure what specific items are possible on a motherboard, but it would be easy enough to add new ones as they come along and it would be fairly easy to link the items to a specific motherboard. Perhaps you can provide some example data?

Another solution that's occurred to me would be to have the query run a mass-replace function to remove all the instances of <li></li>. Is there a way to have a query run a mass replace?

Removing a value or replacing a value in a field is accomplished with an update query. Even if you remove a value, you are still left with a field with a null value. A delete query will delete records not fields, so that will not help you here.

Without using the appropriate table structure as I suggested, the only other way is to step through each record and each field in Visual Basic for Application (VBA) code and test to see if the field contains a value and if so add it to the string.