Format Field output of a complex expression in query.

[Spam]

I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).

So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like

36.7768595041322
38.6666666666667
15.6448202959831
etc


the expression i use is:

Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)

Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?

 

[Rickster57]

format

Try using the format function with "#.##"

 

[Spam]

No joy I'm afraid

Nothing I type in the format option of the field properties dialogue seems to have any effect, and the dropdown list of common formats doesn't appear when the field is an expression.

 

[alastair69]

Have you tried to select the hole colunm then right clich and properties.

Alastair

 

[Rickster57]

format

Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",format(([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100),"#.##")


That doesn't work?

 

[Spam]

Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",format(([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100),"#.##")


That doesn't work?

Nope, get an error - "The expression you entered has a function containing the wrong number of arguments."
Likewise if i take the values of the margin field to another colum and try formatting it there - eg..

Margin2: Format([Margin],"#.##")
it just bombs out and displays no results :|

I can use the values from the margin column in another colum and trim it to 4 characters using

Margin2: Left([Margin],4)

which is better, but surely access should be able to format this properly?

 

[Rickster57]

Here is the code used in my query that worked fine:


Margin: IIf([price]=0,"",Format((([price]-[cost]))/[price]*100,"#.##"))

 

[Spam]

Aha!

The extra set of brackets did the trick - thanks a lot - much appreciated