Displaying a single field or two fields together

  • Thread starter Thread starter JJ1109
  • Start date Start date
J

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
 
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
 
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
 
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
 
JJ1109 said:
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"
Click to expand...

=Nz([Field1] & (" & " + [Field2]),"N/A")
 
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
 
JJ1109 said:
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.
Click to expand...

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

=Nz([Field1] & (" && " + [Field2]),"N/A")
 
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

Rick Brandt said:
JJ1109 said:
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.
Click to expand...

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

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



.
Click to expand...
 
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

Rick Brandt said:
JJ1109 said:
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.
Click to expand...
Hmm, maybe try doubling the & since that is normally a special character...

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



.
Click to expand...
Click to expand...
 
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
 
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

John Spencer said:
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
Click to expand...
Click to expand...
 
John said:
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])))
Click to expand...


I prefer the slightly more cryptix, but shorter:

=Mid((" & " + Field1) & (" & " + Field2), 4)
 
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

Marshall said:
John said:
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])))
Click to expand...


I prefer the slightly more cryptix, but shorter:

=Mid((" & " + Field1) & (" & " + Field2),4)
Click to expand...
 
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
 
Back
Top