Code when some fields have no data.

I am using Access 2002. I am creating a report and have three fields I need
to list with semicolons between them but they don't all have data in them.

They are txtPrimaryCompetency, txtSecondaryCompetency and
txtSupplementalCompetency. I've been trying to figure out how to list them
and not have the semicolon appear if one or two of them have no data. I have
tried using Not Null but can't get it to work. I tried using Len but I
couldn't get that to work either. I am a beginner to programming and am a
bit frustrated with trying to get this to work. I hope someone can help me.
Thank you in advance.

Hi Ann;
This is a piece of code that I use when there is no information and a comma
separating lname & fname. It must be set up as a text box.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
10 If Me.YourTextField = ", " Then
20 Me.YourTextField.Visible = False
30 Else
40 Me.YourTextField.Visible = True
50 End If
End Sub

Ann wrote:
I am using Access 2002. I am creating a report and have three fields I need
to list with semicolons between them but they don't all have data in them.

They are txtPrimaryCompetency, txtSecondaryCompetency and
txtSupplementalCompetency. I've been trying to figure out how to list them
and not have the semicolon appear if one or two of them have no data. I have
tried using Not Null but can't get it to work. I tried using Len but I
couldn't get that to work either. I am a beginner to programming and am a
bit frustrated with trying to get this to work. I hope someone can help me.
Thank you in advance.

--
Message posted via http://www.accessmonster.com

Try this expression. It relies on the difference between the + concatenation
operator and the & concatenation operator.

Mid(("; " + txtPrimaryCompetency)
& ("; " + txtSecondaryCompetency)
& ("; " + txtSupplementalCompetency),3)

This will fail if you have zero-length strings (ZLS) in the fields instead of
null. In the case of ZLS in the fields you are going to have to use IIF to
test the length of the field and decide whether or not to add a semi-colon.

How it works
"; " + txtPrimaryCompetency
will evaluate to Null if txtPrimaryCompetency is Null

For every field that has a value you are adding a LEADING semi-colon and
space. So the MID strips off the first semi-colon and space in the resulting
concatenation.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ann wrote:
I am using Access 2002. I am creating a report and have three fields I need
to list with semicolons between them but they don't all have data in them.

They are txtPrimaryCompetency, txtSecondaryCompetency and
txtSupplementalCompetency. I've been trying to figure out how to list them
and not have the semicolon appear if one or two of them have no data. I have
tried using Not Null but can't get it to work. I tried using Len but I
couldn't get that to work either. I am a beginner to programming and am a
bit frustrated with trying to get this to work. I hope someone can help me.
Thank you in advance.

Thank you John, that worked perfectly. I appreciate the help.

Thank you to "Afrosheen" I do the same thing with my names so I can use your
code too.

"John Spencer" wrote:

Try this expression. It relies on the difference between the + concatenation
operator and the & concatenation operator.

Mid(("; " + txtPrimaryCompetency)
& ("; " + txtSecondaryCompetency)
& ("; " + txtSupplementalCompetency),3)

This will fail if you have zero-length strings (ZLS) in the fields instead of
null. In the case of ZLS in the fields you are going to have to use IIF to
test the length of the field and decide whether or not to add a semi-colon.

How it works
"; " + txtPrimaryCompetency
will evaluate to Null if txtPrimaryCompetency is Null

For every field that has a value you are adding a LEADING semi-colon and
space. So the MID strips off the first semi-colon and space in the resulting
concatenation.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ann wrote:
I am using Access 2002. I am creating a report and have three fields I need
to list with semicolons between them but they don't all have data in them.

They are txtPrimaryCompetency, txtSecondaryCompetency and
txtSupplementalCompetency. I've been trying to figure out how to list them
and not have the semicolon appear if one or two of them have no data. I have
tried using Not Null but can't get it to work. I tried using Len but I
couldn't get that to work either. I am a beginner to programming and am a
bit frustrated with trying to get this to work. I hope someone can help me.
Thank you in advance.
.