Format within Query

[ViRi]

Hi

I have a Query that calculates a value SubTotal using one of two values depending on the state of a check box called Euro.

SubTotal: IIf([Euro]=True,[Quantity]*([UnitPrice]*[ExchangeRate])-[Discount],[Quantity]*[UnitPrice]-[Discount])

This works fine. The value is calculated correctly.

I now need to include the currency sign be it either € or
£ in front of the value generated according to the state of Euro. If Euro is True then € if not True then £

Euro is set by clicking on one of two command buttons btnEuro or btnGBP.


ViRi

 

[neileg]

Why not do this in your form where you display the data. This will work much better than doing it in the query.

 

[ViRi]

Hi

Any idea on how to go about this.

ViRi

 

[ViRi]

Hi

I've tried this code on the On Current Event of the subform. It works for the first record once the Form is refreshed, only to change back once the Form is refreshed again by selecting a new record for example.

Private Sub Form_Current()

If Me.Euro = True Then
Me.Total.Format = "€0.00"
Else
Me.SubTotal.Format = "£0.00"
End If

DoCmd.RunCommand acCmdSaveRecord


End Sub

ViRi