IIF statement format percent

  • Thread starter Thread starter Octet32
  • Start date Start date
O

Octet32

I have an expression like this % shipped: [Picked]/[Shipped]-1 that is
formatted as a Percent.
I want all results to show without any negative amounts -0.08 should be
0.08 how can I do this with an IIf statement ?

can anyone help out?
 
Use the Abs function to remove the sign bit:

shipped: Format(ABS([Picked]/[Shipped]-1), "Percent")
 
i tryed this Test: IIf([Boxes]/[Shipped]-1<0,[Shipped]/[Boxes]-1) but it only
worked for the negitive fields all other field are blank?
 
That worked Thanks!

Klatuu said:
Use the Abs function to remove the sign bit:

shipped: Format(ABS([Picked]/[Shipped]-1), "Percent")
--
Dave Hargis, Microsoft Access MVP


Octet32 said:
I have an expression like this % shipped: [Picked]/[Shipped]-1 that is
formatted as a Percent.
I want all results to show without any negative amounts -0.08 should be
0.08 how can I do this with an IIf statement ?

can anyone help out?
Click to expand...
Click to expand...
 
Klatuu i have this querry linked to a report my Conditional format no longer
works?

Klatuu said:
Use the Abs function to remove the sign bit:

shipped: Format(ABS([Picked]/[Shipped]-1), "Percent")
--
Dave Hargis, Microsoft Access MVP


Octet32 said:
I have an expression like this % shipped: [Picked]/[Shipped]-1 that is
formatted as a Percent.
I want all results to show without any negative amounts -0.08 should be
0.08 how can I do this with an IIf statement ?

can anyone help out?
Click to expand...
Click to expand...
 
I rarely if ever apply a format in a query. Save the formatting for the
report or form so the value remains numeric rather than string.
shipped: ABS([Picked]/[Shipped]-1)

Always check to see if your field is left or right aligned. Dates and
Numbers should be right aligned.

--
Duane Hookom
Microsoft Access MVP


Octet32 said:
Klatuu i have this querry linked to a report my Conditional format no longer
works?

Klatuu said:
Use the Abs function to remove the sign bit:

shipped: Format(ABS([Picked]/[Shipped]-1), "Percent")
--
Dave Hargis, Microsoft Access MVP


Octet32 said:
I have an expression like this % shipped: [Picked]/[Shipped]-1 that is
formatted as a Percent.
I want all results to show without any negative amounts -0.08 should be
0.08 how can I do this with an IIf statement ?

can anyone help out?
Click to expand...
Click to expand...
Click to expand...
 
Generally, the IIF( ) function in VB requires three parameters
1. an expression that evaluates to True or False
2. a True part (what you want to return if the expression evaluates to True)
3. a False part

What I have recently found is that the IIF( ) function used in JET SQL only
requires the first two parts, and returns a NULL if the expression evaluates
to false.

Having cleared that up. I wasn't certain that I understood what you wanted
to do initially, but now I know that I don't have a clue what you are trying
to do!

OK, I intrepreted [Boxes]/[Shipped] as an attempt to identify the ration of
the total number of boxes to those shipped. I would normally expect this to
be a number greater than one (could be anything), but if you want a
percentage, I would have thought you might want the [Shipped]/[Boxes].
Generally this would always be < 1.

Can you describe (in words) what you really want this field to reflect? Is
it the percentage of boxes received that have been shipped? If so,
[Shipped]/[Boxes] is what you want.

----
HTH
Dale



Octet32 said:
i tryed this Test: IIf([Boxes]/[Shipped]-1<0,[Shipped]/[Boxes]-1) but it only
worked for the negitive fields all other field are blank?

Octet32 said:
I have an expression like this % shipped: [Picked]/[Shipped]-1 that is
formatted as a Percent.
I want all results to show without any negative amounts -0.08 should be
0.08 how can I do this with an IIf statement ?

can anyone help out?
Click to expand...
Click to expand...
 
Back
Top