Price format printing in labels "$#,###.00"

[Thomas W]

Hello I am printing labels for are products with Price/Currency data.
The problem I am having is that in order to get the $ .00 to print I had to
add the code
=Format([MyFieldData],"$#,###.00")
I can not add text after the price.

The labels print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60
$119.60


My label code is:

=Trim([Collection] & " - " & [ProductName])
=Trim([Model] & " - " & [Size])
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " " & "Pcs - " & [SqFtPerCase]
& " " & "Box")
=Format([Sheet],"$#,###.00")
=Format([Box],"$#,###.00")

I would like the labels to print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60 Per Sheet
$119.60 Per Case

if someone could please help with what code would come after
or a better Expression for printing the $ sign and .00

The other problem I am having is that not all products have full case
pricing.
I can force the labels to print with the text after the price by adding the
text
myself once with the code

=Format([Sheet],"$#,###.00") & " " & [Per Sheet]
=Format([Box],"$#,###.00") & " " & [Per Case]

A window pops up and I type in Per Sheet and Per Case
However if there is no price the words Per Sheet or Per Case is still
printed.


Thanks in advance
Thomas

 

[Al Camp]

Thomas,
The Per Sheet and Per Case are text, so they should be concatenated as such. Using
[Per Sheet] tells Access it's a field, which Access does not recognize, so it prompts for
a value.

=Format([Sheet],"$#,###.00") & " Per Sheet"
=Format([Box],"$#,###.00") & " Per Case"
will do it. Make sure your field is wide enough to display the number and the text.

Also, you don't have to conactenate spaces (" ") individually. Incorporate them into
your next concatenated text.
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " Pcs - " & [SqFtPerCase] & " Box")

 

[John Spencer]

Pardon me.

If you don't want to print " Per Sheet" or "Per Case" if [Sheet] or [Box] is
null then you need to do some additional testing.

=IIF(IsNull([Sheet]),Null, Format([Sheet],"$#,###.00") & " Per Sheet")
=IIf(IsNull([box]),Null,Format([Box],"$#,###.00") & " Per Case")

Thomas,
The Per Sheet and Per Case are text, so they should be concatenated as
such. Using [Per Sheet] tells Access it's a field, which Access does not
recognize, so it prompts for a value.

=Format([Sheet],"$#,###.00") & " Per Sheet"
=Format([Box],"$#,###.00") & " Per Case"
will do it. Make sure your field is wide enough to display the number and
the text.

Also, you don't have to conactenate spaces (" ") individually.
Incorporate them into your next concatenated text.
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " Pcs - " & [SqFtPerCase] & "
Box")

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello I am printing labels for are products with Price/Currency data.
The problem I am having is that in order to get the $ .00 to print I had
to
add the code
=Format([MyFieldData],"$#,###.00")
I can not add text after the price.

The labels print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60
$119.60


My label code is:

=Trim([Collection] & " - " & [ProductName])
=Trim([Model] & " - " & [Size])
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " " & "Pcs - " &
[SqFtPerCase]
& " " & "Box")
=Format([Sheet],"$#,###.00")
=Format([Box],"$#,###.00")

I would like the labels to print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60 Per Sheet
$119.60 Per Case

if someone could please help with what code would come after
or a better Expression for printing the $ sign and .00

The other problem I am having is that not all products have full case
pricing.
I can force the labels to print with the text after the price by adding
the
text
myself once with the code

=Format([Sheet],"$#,###.00") & " " & [Per Sheet]
=Format([Box],"$#,###.00") & " " & [Per Case]

A window pops up and I type in Per Sheet and Per Case
However if there is no price the words Per Sheet or Per Case is still
printed.


Thanks in advance
Thomas

 

[Thomas W]

WoW

thank you both so much!!
and John your code works like a champ!

Pardon me.

If you don't want to print " Per Sheet" or "Per Case" if [Sheet] or [Box] is
null then you need to do some additional testing.

=IIF(IsNull([Sheet]),Null, Format([Sheet],"$#,###.00") & " Per Sheet")
=IIf(IsNull([box]),Null,Format([Box],"$#,###.00") & " Per Case")

Thomas,
The Per Sheet and Per Case are text, so they should be concatenated as
such. Using [Per Sheet] tells Access it's a field, which Access does not
recognize, so it prompts for a value.

=Format([Sheet],"$#,###.00") & " Per Sheet"
=Format([Box],"$#,###.00") & " Per Case"
will do it. Make sure your field is wide enough to display the number and
the text.

Also, you don't have to conactenate spaces (" ") individually.
Incorporate them into your next concatenated text.
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " Pcs - " & [SqFtPerCase] & "
Box")

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello I am printing labels for are products with Price/Currency data.
The problem I am having is that in order to get the $ .00 to print I had
to
add the code
=Format([MyFieldData],"$#,###.00")
I can not add text after the price.

The labels print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60
$119.60


My label code is:

=Trim([Collection] & " - " & [ProductName])
=Trim([Model] & " - " & [Size])
=Trim([PerCaseOrPc] & " - " & [PcsPerCase] & " " & "Pcs - " &
[SqFtPerCase]
& " " & "Box")
=Format([Sheet],"$#,###.00")
=Format([Box],"$#,###.00")

I would like the labels to print:

Anatollia - Black Sea
5002 - 16x16
Case - 6Pcs - 11Box
$21.60 Per Sheet
$119.60 Per Case

if someone could please help with what code would come after
or a better Expression for printing the $ sign and .00

The other problem I am having is that not all products have full case
pricing.
I can force the labels to print with the text after the price by adding
the
text
myself once with the code

=Format([Sheet],"$#,###.00") & " " & [Per Sheet]
=Format([Box],"$#,###.00") & " " & [Per Case]

A window pops up and I type in Per Sheet and Per Case
However if there is no price the words Per Sheet or Per Case is still
printed.


Thanks in advance
Thomas