Need to change data type to Currency 1

[Fozzy9767]

I tried to do this in the make table query, and while it displays as currency, the field data type is text. I have 4 problem fields that are created while wrapped in FormatCurrency().
Table is MerchantSettlementPage1, Fields are SPS, Gift, MonMin and Fee. I know I can go manually change the data type, but I'd prefer to create the table with the correct data type, or as a second choice, be able to automate the change as a separate process. Any help would be appreciated.

Ken

 

[rjoubert]

Use the CCur function in your make table query, instead of the FormatCurrency function.

 

[Golom]

FormatCurrency (or any Format function) will always return a text data type. You can create a numeric data type in a MAKE TABLE with something like

Select Val(Format([CurrencyField],"0.00")) As CurField

but that will probably be typed as single or double rather than currency. Probably the easiest thing to do is use the above to create a numeric field and then

ALTER TABLE myTable ALTER COLUMN CurField Currency

after you have created the table

 

[Fozzy9767]

Thanks rjoubert, that worked perfectly.