Setting as Currency in Make Table Query

[marklord]

How do I set an empty field as curreny with 2 decimal places within a make table or update query.

This is the query I have at the moment:

SELECT Order.[Order Number], Order.[Contract Number], Order.[Company Name], Order.[Contact Name], Order.[Date Required], Order.[Net Amount], "" AS [Delivered Amount], "" AS [Invoiced Amount] INTO [Print All Orders]
FROM [Order]
ORDER BY Order.[Order Number];

And I need to make both 'Delivered Amount' and 'Invoiced Amount' curreny fields with two d.p.


Any help would be greatly appreciated.

Cheers,

M

 

[RichO]

For an update query, are the 2 amount fields already formatted for currency or are you saying you want to change them from another format (number or text for instance) to currency?

If they are already currency fields, you should be able to use 0 as opposed to "" and the table will format it as $0.00.

 

[marklord]

The table is being created by the query:


SELECT Order.[Order Number], Order.[Contract Number], Order.[Company Name], Order.[Contact Name], Order.[Date Required], Order.[Net Amount], "£0.00" AS [Delivered Amount], "£0.00" AS [Invoiced Amount] INTO [Print All Orders]
FROM [Order];


I have updated this so the default data is '£0.00', however the fields are still not specifically set-up as data-type 'Currency' and therefore when I use a further query to update 'Delivered Amount':

UPDATE [Delivery Ticket], [Print All Orders] SET [Print All Orders].[Delivered Amount] = [Delivery Ticket]!Amount
WHERE ((([Delivery Ticket]![Order Number])=[Print All Orders]![Order Number]));


The information is copied accross as 'x.xx' instead of '£x.xx', despite the field that it is being copied from being set-up as a 'Currency' field.

My main question is...is there a way to modify my initial SELECT query so as to specifically set the 'Delivered Amount' and 'Invoiced Amount' fields as currency so as any data copied into them will be in the format of '£x.xx'?

I hope this has made my question slightly clearer,


Cheers for the help,


M

 

[RichO]

Check this out - I think you'll find your needed information:

http://www.cyberiapc.com/master/vblesson6-1.htm

 

[marklord]

OK, I've not been able to get this to work, so have opted for a slight;ly less tidy method of having a permanent table with the data-types pre-defined and using a delete query to remove any data before using an append query to re-populate the table ready for the 'Delivered Amount' and 'Ordered Amount' to be added.


Thanks for everyones time, it is greatly appreciated.


M

 

[EMP]

You can use the CCur() function i.e.

CCur(0) AS [Delivered Amount]