Can't make number to format as currency

[michael c]

I have a report with a field that I'd like to show up as
currency but it won't when I either choose currency format
or when I put $#,###.0;-$#,###.0[Red];$0 in the format box.

The fields that won't format properly are pulled from a
query. In that query I have an expression that looks like
this:

AVGQTDDollarsShipped: Nz((Abs(Sum((DatePart("q",
[InvoiceDate]) Between DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-1 And DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-4)*[DollarsShipped]))/4)*Forms!
Dashboard!PercentOfQuarter,0)

I've set this query column's format to currency but it
still produces values that look like: 345.2342142

I've tried AVGQTDDollarsShipped: Format(Nz((Abs(Sum
((DatePart("q",[InvoiceDate]) Between DatePart("q",Forms!
Dashboard!FirstDayOfQuarter)-1 And DatePart("q",Forms!
Dashboard!FirstDayOfQuarter)-4)*[DollarsShipped]))/4)
*Forms!Dashboard!PercentOfQuarter,0),"$#,##0")

....but I get a string instead of a value.

Any suggestions would be great! Thanks!

 

[Duane Hookom]

In your report, use
=Val(AvgQTDDollarsShipped)
Then set the format.

 

[michael c]

Duane, Solid advice from you as always. Thanks! I ended up
putting val in the query and the report ended up
formatting properly. Thanks again.

-----Original Message-----
In your report, use
=Val(AvgQTDDollarsShipped)
Then set the format.

--
Duane Hookom
MS Access MVP

I have a report with a field that I'd like to show up as
currency but it won't when I either choose currency format
or when I put $#,###.0;-$#,###.0[Red];$0 in the format box.

The fields that won't format properly are pulled from a
query. In that query I have an expression that looks like
this:

AVGQTDDollarsShipped: Nz((Abs(Sum((DatePart("q",
[InvoiceDate]) Between DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-1 And DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-4)*[DollarsShipped]))/4)*Forms!
Dashboard!PercentOfQuarter,0)

I've set this query column's format to currency but it
still produces values that look like: 345.2342142

I've tried AVGQTDDollarsShipped: Format(Nz((Abs(Sum
((DatePart("q",[InvoiceDate]) Between DatePart ("q",Forms!
Dashboard!FirstDayOfQuarter)-1 And DatePart("q",Forms!
Dashboard!FirstDayOfQuarter)-4)*[DollarsShipped]))/4)
*Forms!Dashboard!PercentOfQuarter,0),"$#,##0")

...but I get a string instead of a value.

Any suggestions would be great! Thanks!

.

 

[cherab]

Thanks. This helped me, also!

-----Original Message-----
In your report, use
=Val(AvgQTDDollarsShipped)
Then set the format.

--
Duane Hookom
MS Access MVP

I have a report with a field that I'd like to show up as
currency but it won't when I either choose currency format
or when I put $#,###.0;-$#,###.0[Red];$0 in the format box.

The fields that won't format properly are pulled from a
query. In that query I have an expression that looks like
this:

AVGQTDDollarsShipped: Nz((Abs(Sum((DatePart("q",
[InvoiceDate]) Between DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-1 And DatePart("q",Forms!Dashboard!
FirstDayOfQuarter)-4)*[DollarsShipped]))/4)*Forms!
Dashboard!PercentOfQuarter,0)

I've set this query column's format to currency but it
still produces values that look like: 345.2342142

I've tried AVGQTDDollarsShipped: Format(Nz((Abs(Sum
((DatePart("q",[InvoiceDate]) Between DatePart ("q",Forms!
Dashboard!FirstDayOfQuarter)-1 And DatePart("q",Forms!
Dashboard!FirstDayOfQuarter)-4)*[DollarsShipped]))/4)
*Forms!Dashboard!PercentOfQuarter,0),"$#,##0")

...but I get a string instead of a value.

Any suggestions would be great! Thanks!

.