Format the result field in a make table query

  • Thread starter Thread starter oldtony
  • Start date Start date
O

oldtony

In a create table query I specify several columns with zero values which are
updated later by code to calculate the value of the commission or order entry
charge. I use StaffComm: 0.00 for the field entry in the query but the
resulting table displays 0. How do I enforce a data type and formatting? I
have used alter table in the subsequent code but still cannot see how to set
the decimal place to 2.
The SQL query is
SELECT orders.orderid, orders.altordernum, orders.orderdate,
orders.ExportDate, orders.ExportBatch, orders.marketingcodeid,
orders.ordertotal, customers.lastname, customers.postcode, users.Username, 0
AS StaffComm, 0 AS MNTComm, False AS Refund, False AS Omit, 0 AS OrderCharge
INTO tblMonthsOrders
FROM (orders INNER JOIN customers ON orders.custid = customers.custid) INNER
JOIN users ON orders.CreateUserID = users.UserID
WHERE (((orders.ExportDate) Between GetGlobalStartDate() And
GetGlobalEndDate()))
ORDER BY customers.lastname;

All and any help will be greatfully recieved
 
Don't use the make table query. Create the table first with the field data
types you want, then import your data into that table.
If you initialize the table with a 0 value, you will not see 0.0. To
display a value with a specific format, use the format function.
 
Back
Top