How to Add and Concatenate When > Zero

[bw]

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is greater than
zero. Using IIF seems too complicated (for me) as I show below. Is there
an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the field
names in whatever formula is to be used.

The result should of course look like this, when the fields have nonzero
values
Meals=$12.76, Hotel=$127.65

Thanks for your help.
Bernie

 

[Allen Browne]

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields in
this table, which violates basic normalization rules. Each item of the bill
should be a *record* in a related table instead of a field in this table.
The related table will have fields such as:
BillID Number Relates to the primary key of your existing table
ItemType "Meals" or "Hotel" or whatever (drop-down list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items of the
account are similar to the order line items of the order. Northwind uses a
separate table for Order Details. That's the only way to design this kind of
database.

 

[bw]

Wow! Finding that there are two concatenation operators is like finding
gold. This is exactly what I was looking for.
As always, I really appreciate your help, sir.

Your suggestion about putting my "repeating" fields in another table on the
surface makes perfect sense to me.
However, I'm not sure how to implement this in practice. Currently, my
"repeating" fields are in a Datasheet (along with other data) as a SubForm1.
Does this new table appear as a combox box in the SubForm1, or should it be
in another SubForm2 (or something else)?

Thanks again for you expertise.
Bernie

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields in
this table, which violates basic normalization rules. Each item of the
bill should be a *record* in a related table instead of a field in this
table. The related table will have fields such as:
BillID Number Relates to the primary key of your existing table
ItemType "Meals" or "Hotel" or whatever (drop-down list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items of
the account are similar to the order line items of the order. Northwind
uses a separate table for Order Details. That's the only way to design
this kind of database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is greater
than zero. Using IIF seems too complicated (for me) as I show below. Is
there an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the
field names in whatever formula is to be used.

The result should of course look like this, when the fields have nonzero
values
Meals=$12.76, Hotel=$127.65

Thanks for your help.
Bernie

 

[Allen Browne]

Yes, changing the data structure could mean rethinking your interface as
well.

It's not possible to suggest an alternative without knowing more about your
structure, so you are probably the best person to decide that.

Since + is ambiguous (addition and concatenation), it is important to be
clear that you mean contatenation. That's what the Str() is doing in that
expression. Without it you could get type mismatch errors or just wrong
results. Str() handles nulls correctly (where CStr() cannot), but it does
have the side effect of adding a leading space to positive numbers (the
space that is used by the negative sign in negative numbers.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wow! Finding that there are two concatenation operators is like finding
gold. This is exactly what I was looking for.
As always, I really appreciate your help, sir.

Your suggestion about putting my "repeating" fields in another table on
the surface makes perfect sense to me.
However, I'm not sure how to implement this in practice. Currently, my
"repeating" fields are in a Datasheet (along with other data) as a
SubForm1.
Does this new table appear as a combox box in the SubForm1, or should it
be in another SubForm2 (or something else)?

Thanks again for you expertise.
Bernie

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields in
this table, which violates basic normalization rules. Each item of the
bill should be a *record* in a related table instead of a field in this
table. The related table will have fields such as:
BillID Number Relates to the primary key of your existing table
ItemType "Meals" or "Hotel" or whatever (drop-down list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items of
the account are similar to the order line items of the order. Northwind
uses a separate table for Order Details. That's the only way to design
this kind of database.

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is greater
than zero. Using IIF seems too complicated (for me) as I show below.
Is there an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the
field names in whatever formula is to be used.

The result should of course look like this, when the fields have nonzero
values
Meals=$12.76, Hotel=$127.65

Thanks for your help.
Bernie

 

[bw]

Okay Allen, I think I know how to proceed with the table now.

I tried your formula as follows:
=Str([Meals]) + Format([Meals], "Currency")
When the value of [Meals] is 1, this is what I get for the result: 1$1.00
What I want for the result is: Meals$1.00 (I can fix for spaces later as
necessary)

I want to use the field name as the literal in my expression.

If [Meals]=0, then the result of the above formula is: 0
When [Meals]=0 I don't want to display anything, including the name of the
field.

Can you help again?
Bernie

Yes, changing the data structure could mean rethinking your interface as
well.

It's not possible to suggest an alternative without knowing more about
your structure, so you are probably the best person to decide that.

Since + is ambiguous (addition and concatenation), it is important to be
clear that you mean contatenation. That's what the Str() is doing in that
expression. Without it you could get type mismatch errors or just wrong
results. Str() handles nulls correctly (where CStr() cannot), but it does
have the side effect of adding a leading space to positive numbers (the
space that is used by the negative sign in negative numbers.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wow! Finding that there are two concatenation operators is like finding
gold. This is exactly what I was looking for.
As always, I really appreciate your help, sir.

Your suggestion about putting my "repeating" fields in another table on
the surface makes perfect sense to me.
However, I'm not sure how to implement this in practice. Currently, my
"repeating" fields are in a Datasheet (along with other data) as a
SubForm1.
Does this new table appear as a combox box in the SubForm1, or should it
be in another SubForm2 (or something else)?

Thanks again for you expertise.
Bernie

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields
in this table, which violates basic normalization rules. Each item of
the bill should be a *record* in a related table instead of a field in
this table. The related table will have fields such as:
BillID Number Relates to the primary key of your existing
table
ItemType "Meals" or "Hotel" or whatever (drop-down list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items of
the account are similar to the order line items of the order. Northwind
uses a separate table for Order Details. That's the only way to design
this kind of database.

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is greater
than zero. Using IIF seems too complicated (for me) as I show below.
Is there an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the
field names in whatever formula is to be used.

The result should of course look like this, when the fields have
nonzero values
Meals=$12.76, Hotel=$127.65

Thanks for your help.
Bernie

 

[Allen Browne]

The difference in concatenation operators applies only if the value is Null.
If you have the value zero in the field, you cannot use this approach.

If you want the text "Meals", where will this come from? If you expect the
program to just know that Meals is 1, then you really need a lookup table,
and the proper relational approach will be simpler than a massive IIf() or
even a function call with a Select Case construct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Okay Allen, I think I know how to proceed with the table now.

I tried your formula as follows:
=Str([Meals]) + Format([Meals], "Currency")
When the value of [Meals] is 1, this is what I get for the result: 1$1.00
What I want for the result is: Meals$1.00 (I can fix for spaces later as
necessary)

I want to use the field name as the literal in my expression.

If [Meals]=0, then the result of the above formula is: 0
When [Meals]=0 I don't want to display anything, including the name of the
field.

Can you help again?
Bernie

Yes, changing the data structure could mean rethinking your interface as
well.

It's not possible to suggest an alternative without knowing more about
your structure, so you are probably the best person to decide that.

Since + is ambiguous (addition and concatenation), it is important to be
clear that you mean contatenation. That's what the Str() is doing in that
expression. Without it you could get type mismatch errors or just wrong
results. Str() handles nulls correctly (where CStr() cannot), but it does
have the side effect of adding a leading space to positive numbers (the
space that is used by the negative sign in negative numbers.)

Wow! Finding that there are two concatenation operators is like finding
gold. This is exactly what I was looking for.
As always, I really appreciate your help, sir.

Your suggestion about putting my "repeating" fields in another table on
the surface makes perfect sense to me.
However, I'm not sure how to implement this in practice. Currently, my
"repeating" fields are in a Datasheet (along with other data) as a
SubForm1.
Does this new table appear as a combox box in the SubForm1, or should it
be in another SubForm2 (or something else)?

Thanks again for you expertise.
Bernie

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields
in this table, which violates basic normalization rules. Each item of
the bill should be a *record* in a related table instead of a field in
this table. The related table will have fields such as:
BillID Number Relates to the primary key of your existing
table
ItemType "Meals" or "Hotel" or whatever (drop-down
list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items of
the account are similar to the order line items of the order. Northwind
uses a separate table for Order Details. That's the only way to design
this kind of database.

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is
greater than zero. Using IIF seems too complicated (for me) as I show
below. Is there an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the
field names in whatever formula is to be used.

The result should of course look like this, when the fields have
nonzero values
Meals=$12.76, Hotel=$127.65

 

[bw]

"Meals" is the name of the field, in the underlying table. I want to use
that name as a literal.
So if the field [Meals] has a value of 1, our result would be:
Meals=$1.00
and the result would be blank (null) if [Meals] has a value of zero.

a massive IIf() or even a function call with a Select Case construct.

I think you are indicating with this statement that I DO IN FACT have to
have an IIf or Select Case (which is what I was trying to avoid) to do what
I'm trying to do.

I'm sorry if I'm not making this clear.
Bernie

The difference in concatenation operators applies only if the value is
Null. If you have the value zero in the field, you cannot use this
approach.

If you want the text "Meals", where will this come from? If you expect the
program to just know that Meals is 1, then you really need a lookup table,
and the proper relational approach will be simpler than a massive IIf() or
even a function call with a Select Case construct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Okay Allen, I think I know how to proceed with the table now.

I tried your formula as follows:
=Str([Meals]) + Format([Meals], "Currency")
When the value of [Meals] is 1, this is what I get for the result: 1$1.00
What I want for the result is: Meals$1.00 (I can fix for spaces later as
necessary)

I want to use the field name as the literal in my expression.

If [Meals]=0, then the result of the above formula is: 0
When [Meals]=0 I don't want to display anything, including the name of
the field.

Can you help again?
Bernie

Yes, changing the data structure could mean rethinking your interface as
well.

It's not possible to suggest an alternative without knowing more about
your structure, so you are probably the best person to decide that.

Since + is ambiguous (addition and concatenation), it is important to be
clear that you mean contatenation. That's what the Str() is doing in
that expression. Without it you could get type mismatch errors or just
wrong results. Str() handles nulls correctly (where CStr() cannot), but
it does have the side effect of adding a leading space to positive
numbers (the space that is used by the negative sign in negative
numbers.)

Wow! Finding that there are two concatenation operators is like
finding gold. This is exactly what I was looking for.
As always, I really appreciate your help, sir.

Your suggestion about putting my "repeating" fields in another table on
the surface makes perfect sense to me.
However, I'm not sure how to implement this in practice. Currently, my
"repeating" fields are in a Datasheet (along with other data) as a
SubForm1.
Does this new table appear as a combox box in the SubForm1, or should
it be in another SubForm2 (or something else)?

Thanks again for you expertise.
Bernie

Access has 2 concatenation operators: & and +.
There is a subtle difference between the two:
Null & "A" = "A"
Null + "A" = Null

You can therefore use:
=Str([Meals]) + Format([Meals], "Currency") & Str([Hotel] +
Format([Hotel], "Currency")

However, the real issue here is that you have lots of repeating fields
in this table, which violates basic normalization rules. Each item of
the bill should be a *record* in a related table instead of a field in
this table. The related table will have fields such as:
BillID Number Relates to the primary key of your existing
table
ItemType "Meals" or "Hotel" or whatever (drop-down
list)
Amount Currency How much for this item.

If you compare this to the Northwind sample database, the line items
of the account are similar to the order line items of the order.
Northwind uses a separate table for Order Details. That's the only way
to design this kind of database.

In the Record Source of my report I have many fields that I want to
concatenate into a single field, when the value of the field is
greater than zero. Using IIF seems too complicated (for me) as I
show below. Is there an easier way to do this?

For example (with two fields):
Meals Hotel (These are both currency fields)

I'd like my new field to contain something like the following:
Expr: IIf([Meals]>0,"Meals=" & Format([Meals],"Currency"),"")
and then concatenate the next field
IIf([Hotel]>0,", Hotel=" & Format([Hotel],"Currency"),"")
and so on, and so on (for any number of fields I choose to include.

and instead of using the literal Meals and Hotel, I'd rather use the
field names in whatever formula is to be used.

The result should of course look like this, when the fields have
nonzero values
Meals=$12.76, Hotel=$127.65