Is Not Null Doesn't work in my query

[Denise]

In my table the field is a text field. Training 01, Training 02, to Training
06

In my form the field is a combo box.

When I created the report, the Training 06 field is blank.

How do I not have this field show up in my report if it is blank.

Thanks.

 

[BruceM via AccessMonster.com]

You could set the Can Shrink property of the text box and the Detail section
of the report (or whatever section contains the text box) to Yes. If there
is no data, the text box and its associated label will not appear. Note that
this works if the text boxes are one above the other, but may not work if
they are side by side. For that situation you would need to use code,
probably in the report's Print event.

 

[Jeff Boyce]

Denise

Are you certain that "blank" = Null? It could also = "" (a zero-length
string) or even multiple spaces (i.e., "blanks")?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Denise]

Hi Jeff,

Would you suggest that I set the fields up differently in the table?

The "type of training topics" are listed in a separate table.

The table that I am using includes the informationn for that day's training.

Training Topics: Emergency Evacuation, CPR, First Aid, Digsafe, etc.

I have six fields called Training Topic 01, Training Topic 02, etc.

What should I do so my query will work?

Thanks for your help.

Denise

 

[Douglas J. Steele]

What you've got is called a repeating group, and it's definitely not
recommended for relational databases. What happens if you need a seventh
training topic?

You should have two tables: the first being all the other fields currently
in the table, and the second being a related table with one row for each
topic. If an individual wants only one topic, there will be one row in the
second table, if the individual wants 10 topics, there will be ten rows.

 

[BruceM via AccessMonster.com]

I don't know if you saw my response or if it was of any interest. If you
have Training Topic 01, 02, etc. in one table you proably do not have an
effective table design, but then you say you have the type of training topics
in a separate table, so it is a little unclear how your tables are structured.
In any case, even if you can exclude fields that are Null (or contain a zero-
length string, or whatever criteria you choose), the combo box on the report
is not going to go away unless you make provisions for that to happen.

You can use a query to exclude records in which a field is null. However, if
you want to exclude fields that are null, but show the rest of the record,
you will probably need to set the SQL for each record. That is, you
essentially have to rewrite the query for each record as you go. That would
be quite difficult, I expect. It would be better just not to show the
control if its Record Source is Null.

Hi Jeff,

Would you suggest that I set the fields up differently in the table?

The "type of training topics" are listed in a separate table.

The table that I am using includes the informationn for that day's training.

Training Topics: Emergency Evacuation, CPR, First Aid, Digsafe, etc.

I have six fields called Training Topic 01, Training Topic 02, etc.

What should I do so my query will work?

Thanks for your help.

Denise

Denise

[quoted text clipped - 19 lines]

.

 

[Denise]

Doug,

I do have a table that lists all the types of training.

In another table is here I have a field for Training 01, Training 02, etc.

What should the field be in the second table if I might need to have more
than one type of training?

Thanks.

 

[Douglas J. Steele]

It sounds as though you're describing a many-to-many relationship (one
person may take multiple courses, one course may be taken by more than one
person). That means you need to introduce a table to resolve the
many-to-many relationship.

You may wish to check out some of the resources related to Database
Normalization that Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101