Displaying a single field or two fields together

[JJ1109]

I have a chemical database that I'm working on, and in a report on each
chemical, I would like it to display the contents of two fields, like this:
Label: [field one] & [field two]. However, not all records have the second
field. So I'd like for the ones with only field one used to just display:
Label: [field one], but the ones with both to display as I've shown above. Is
this possible?

thanks!
JJ

 

[JJ1109]

Thanks Jeanette, worked like a charm! A couple of follow-up questions...

Can I get a comma between the two fields, but only if the second one is
used? ie [Field 1], [Field 2]

and...

I just found records that don't use either field. Can I get it to display
"N/A" if neither field is used?

thanks again
JJ

 

[JJ1109]

Sorry, I wasn't clear: Say I have "8" in Field 1 and "3" in Field . I want to
display "8 & 3". However, if I have only 8 in Field 1, I want just "8", and
if there's nothing in either field, I want "N/A".

What I currently see is "8 3"

thanks
JJ

 

[Jeanette Cunningham]

It is getting more complicated.
Try this one-->

Expr1: IIf(Len([Field 1])>0,IIf(Len([Field 2])>0,[Field 1] & (", " & [[Field
2]),[Field 1]),"N/A")



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Rick Brandt]

Sorry, I wasn't clear: Say I have "8" in Field 1 and "3" in Field . I want
to display "8 & 3". However, if I have only 8 in Field 1, I want just "8",
and if there's nothing in either field, I want "N/A".

What I currently see is "8 3"

=Nz([Field1] & (" & " + [Field2]),"N/A")

 

[JJ1109]

Hi Rick, thanks for that. It works when there's no data in the fields, or
data in field 1, but not when there's data in Field 1 and Field 2 - it
returns #Error in the report.

JJ

 

[Rick Brandt]

Hi Rick, thanks for that. It works when there's no data in the fields, or
data in field 1, but not when there's data in Field 1 and Field 2 - it
returns #Error in the report.

Hmm, maybe try doubling the & since that is normally a special character...

=Nz([Field1] & (" && " + [Field2]),"N/A")

 

[JJ1109]

It still returns #Error - I changed the & sign to the word "and", still get
error.

thanks for your help! If all else fails, I'll just leave it without the
symbol between them, however that's not ideal!

cheers
JJ

Hi Rick, thanks for that. It works when there's no data in the fields, or
data in field 1, but not when there's data in Field 1 and Field 2 - it
returns #Error in the report.

Hmm, maybe try doubling the & since that is normally a special character...

=Nz([Field1] & (" && " + [Field2]),"N/A")



.

 

[John Spencer]

Build it a step at a time until it fails
In a control that is named txtShowText (avoid name conflicts)


= [Field1] & " & " & [Field2]

If the above works then you can go on to something more complex such as the
following. If the above does not get rid of the #Error then the expression is
not the problem. Something else is.

= [Field1] & (IIF([Field1] Is Null,""," & ") + [Field2])

If the fields could be zero-length strings, then you will need to test the
length of Field1 and the length of Field2 instead of relying on them being null.

= [Field1] & (IIF(Len([Field1] & "") = 0,""," & ") + IIF(Len([Field2] &
"")=0,Null,[Field2]))

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

It still returns #Error - I changed the & sign to the word "and", still get
error.

thanks for your help! If all else fails, I'll just leave it without the
symbol between them, however that's not ideal!

cheers
JJ

Hi Rick, thanks for that. It works when there's no data in the fields, or
data in field 1, but not when there's data in Field 1 and Field 2 - it
returns #Error in the report.

Hmm, maybe try doubling the & since that is normally a special character...

=Nz([Field1] & (" && " + [Field2]),"N/A")



.

 

[JJ1109]

Thanks John.

Here's the results.

= [Field1] & " & " & [Field2]

works, when there's data in both fields. Else it displays "Field1 & " or
simply "& " if there's one or no data.

Both of the next two expressions work when there's only data in Field1. As
soon as there's data in Field2, the expressions calculate to #Error.

The field types are both Number, field size Double, format General Number,
if that would have any impact.

thanks
JJ

 

[John Spencer]

OK. Let's try a more complex expression that tests for null or not null and
depending on the results generates a value.

= IIF([Field1] is Null and [Field2] Is Null, Null,
IIF([Field1] is Not Null and [Field2] is Null,[Field1],
IIF([Field1] is Null and [Field2] is Not Null, [Field2],
[]Field1 & " & " & [Field2])))
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John.

Here's the results.

= [Field1] & " & " & [Field2]

works, when there's data in both fields. Else it displays "Field1 & " or
simply "& " if there's one or no data.

Both of the next two expressions work when there's only data in Field1. As
soon as there's data in Field2, the expressions calculate to #Error.

The field types are both Number, field size Double, format General Number,
if that would have any impact.

thanks
JJ

Build it a step at a time until it fails
In a control that is named txtShowText (avoid name conflicts)


= [Field1] & " & " & [Field2]

If the above works then you can go on to something more complex such as the
following. If the above does not get rid of the #Error then the expression is
not the problem. Something else is.

= [Field1] & (IIF([Field1] Is Null,""," & ") + [Field2])

If the fields could be zero-length strings, then you will need to test the
length of Field1 and the length of Field2 instead of relying on them being null.

= [Field1] & (IIF(Len([Field1] & "") = 0,""," & ") + IIF(Len([Field2] &
"")=0,Null,[Field2]))

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

 

[Marshall Barton]

OK. Let's try a more complex expression that tests for null or not null and
depending on the results generates a value.

= IIF([Field1] is Null and [Field2] Is Null, Null,
IIF([Field1] is Not Null and [Field2] is Null,[Field1],
IIF([Field1] is Null and [Field2] is Not Null, [Field2],
[]Field1 & " & " & [Field2])))

I prefer the slightly more cryptix, but shorter:

=Mid((" & " + Field1) & (" & " + Field2), 4)

 

[John Spencer]

Nice, but it does take some understanding of what is going on using the
different concatenation operators.

And when I try using it with numbers and not strings, it generates a type
mismatch error. (Access2003)

" & " + Field1 works with string or numbers or nulls
" & " + Field2 works with string or numbers or nulls

Add in the call to MID generates an error if either field1 or field2 is a
number type.

NOTE: There was an error in brackets in my posting
= IIF([Field1] is Null and [Field2] Is Null, Null,
IIF([Field1] is Not Null and [Field2] is Null,[Field1],
IIF([Field1] is Null and [Field2] is Not Null, [Field2],
[Field1] & " & " & [Field2])))

Alternative VBA Statement
IIF(ISNull(Field1) and IsNull(Field2), Null,
IIF(Not(IsNull(Field1))and IsNull(Field2),Field1,
IIF(IsNull(Field1) and Not(IsNull(Field2)),Field2
,Field1 & " & " & Field2)))


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

OK. Let's try a more complex expression that tests for null or not null and
depending on the results generates a value.

= IIF([Field1] is Null and [Field2] Is Null, Null,
IIF([Field1] is Not Null and [Field2] is Null,[Field1],
IIF([Field1] is Null and [Field2] is Not Null, [Field2],
[]Field1 & " & " & [Field2])))

I prefer the slightly more cryptix, but shorter:

=Mid((" & " + Field1) & (" & " + Field2),4)

 

[JJ1109]

Hi John and all,

The last one worked perfectly! I removed the if field1 is null and field2 is
not, as that won't happen, changed the variable if both are null to display
"None" and it works exactly right.

thanks everyone for your help
JJ