Can't make number to format as currency

  • Thread starter Thread starter michael c
  • Start date Start date
M

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


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


.
Click to expand...
 
Back
Top