Error in syntax

[ezybusy]

Can someone please tell me what i am doing wrong here:

Me.LstItemList.RowSourceType = "Table/Query"
Me.LstItemList.RowSource = ("SELECT ITEM.ItemName AS [ITEM / ARTICLE], FORMAT(Item.ItemPrice, "¢ #,##0.00") AS PRICE, SaleTransDetail.QtyOrdered AS QTE, FORMAT([ItemPrice]*[QtyOrdered], "¢ #,##0.00") AS [COST]" & _
     " FROM SaleTransaction INNER JOIN (ITEM INNER JOIN SaleTransDetail ON ITEM.ItemID_PK = SaleTransDetail.ItemID_FK) ON SaleTransaction.SaleTransID_PK = SaleTransDetail.SaleTransID_FK" & _
     " WHERE SaleTransaction.ClientTableID_FK=" & Me.ClientTableID_FK & _
     " ORDER BY Item.ItemName ")
  Me.LstItemList.Requery

The code works just fine without the FORMAT function. But once i add it the line become red.
Am i missing something? Or this is not the right way to use the FORMAT function?

NB: I am only trying to format the price and the cost fields to match a specific currency format.



Thanks in advance.

[orange]

Show us the design of your tables
SaleTransaction
Item
SaleTransDetail

[ezybusy]

@Orange

here is the design

[orange]

I tried simpler formats to see the result

?format(12345,"$0000.00")
$12345.00

?format(12345,"$##,000.00")
$12,345.00

?format(12345,"¢ #,##0.00")
¢ 12,345.00

I would include the CurrentSellingPrice or AgreedToPrice in the SaleTransDetail table. If you rely on the ItemPrice from the Item table, you'll find that historic values (Invoices/Transactions) will have errors. If you change the ItemPrice in the Item table, which you will do at some point, then your old SaleTransDetail records will use the latest ItemPrice which isn't the Price of that Item when the original Item was sold.

[ezybusy]

@Orange

Still the same error for the FORMAT function.

Regarding the ItemPrice, i have setup an arching table where all details are posted when the user clicks on a button called "END TRANSACTION". And most queries are based on that archiving table. so no worries for that.

[orange]

How is ItemPrice defined in the Table?

[ezybusy]

here.

[orange]

?? Why do you need the format if it works well without it?

[ezybusy]

yeah you right.

But what of the calculated field in the the query ([ItemPrice]*[QtyOrdered]) ?

[CJ_London]

since you are building a string, your format function needs to use single quotes

FORMAT(Item.ItemPrice, '¢ #,##0.00') AS PRICE

Also not sure about using the opening/closing brackets for the entire string

Me.LstItemList.RowSource = ("SELECT.....

[ezybusy]

@Ajax

It works. Thank you a bunch for your help. NB: the opening/closing brackets have no problem at all on my side.

@Orange

Thank you for the consideration.