Set data type of field

[BobJones]

I have a query to go through a table and pick up the amount of money earnt during a period then I use that field to calculate commission, but the commission field just shows up as numbers and not as currency as the set field in the table does.
Is there anyway in SQL to say what sort of data type a field should be...
Example of sql string

SELECT Sum([Work].[Earnings]) AS [Period Earnings], [Period Earnings]*(17.5/100) AS [Commisssion] FROM Policy

 

[raskew]

Hi -

With the query in design view:
1) Highlight the field in question.
2) Right-click to bring up the shortcut menu and select then Properties.
3) Under Field Properties select Format and then Currency.

If you're going the SQL route, try:

SELECT Sum([Work].[Earnings]) AS [Period Earnings], format([Period Earnings]*(17.5/100), "Currency") AS [Commisssion] FROM Policy

HTH - Bob

 

[BobJones]

Thanks man.

You are a star!