如何在MS Access表的查询设计中创建小数点？ [英] How to create decimal points in query design of MS Access table ?

问题描述

我在MS Access中有一些表，我尝试在该特定表下创建新查询。我比较了另一个表中的值，并在此查询中加入了此处。我得到整数格式的值（456,567.3），但我需要显示十进制值（456.00,567.30）。如何完成这项任务。



这是我在Access表中的SQL查询

  SELECT  
 CustomerName，
 OrderNumber，
 InvoiceAmount，
（ SELECT  TotalPaid  FROM  InvoiceList  WHERE  InvoiceList.InvoiceId = NewInvoice_1.InvoiceId） AS  AmountPaid，
（InvoiceAmount-Total） AS  AmountDue，
 InvoiceStatus 
  FROM  NewInvoice_1; 

输出

 CustomerName OrderNumber InvoiceAmount AmountPaid AmountDue Status 
 Galaxy  65412   6300 。 00   5000   1300  Paritally Paid 

这个InvoiceAmount是数据库列，因此它显示为十进制，AmountPaid& AmountDue是查询列，但它显示为Integer但我需要显示为deciaml（5000.00& 1300.00）如何执行此操作？



提前致谢，

Srihari

解决方案

试试这个：

格式（（InvoiceAmount-Total），  0.00 ） AS  AmountDue 

阅读更多： MS访问：格式功能（带数字） [ ^ ]

您可以使用 FORMAT 功能。在您的情况下： FORMAT（表达式，固定）。固定格式固定为2位小数（没有千位分隔符）。



你的SQL应该是这样的：

  SELECT  
客户名称，
订单号，
 InvoiceAmount，
 格式（（  SELECT  TotalPaid  FROM  InvoiceList  WHERE  InvoiceList.InvoiceId = NewInvoice_1.InvoiceId）， 已修复）  AS  AmountPaid，
  FORMAT（（InvoiceAmount-Total）， < span class =code-string>已修复）  AS  AmountDue，
 InvoiceStatus 
  FROM  NewInvoice_1; 

请参阅： MS ACCESS：FORMAT FU NCTION（带字符串） [ ^

Hi, I have some tables in MS Access, I try to create new query under that particular table. I compare values from another table and joined here in this query. Am getting values in integer format(456, 567.3) but I need to show that values in decimal(456.00, 567.30). How to complete this task.

This is my sql query in Access table

SELECT 
      CustomerName, 
      OrderNumber, 
      InvoiceAmount, 
      (SELECT TotalPaid FROM InvoiceList WHERE InvoiceList.InvoiceId=NewInvoice_1.InvoiceId) AS AmountPaid, 
      (InvoiceAmount-Total) AS AmountDue,
      InvoiceStatus
FROM NewInvoice_1;

Output

CustomerName OrderNumber InvoiceAmount AmountPaid AmountDue Status
Galaxy       65412       6300.00       5000       1300      Paritally Paid

this InvoiceAmount is database column so it display as decimal, AmountPaid & AmountDue is query column but it display as Integer but i need to display as deciaml(5000.00 & 1300.00) how to do this ?

Thanks in advance,
Srihari

解决方案

Try this:

format((InvoiceAmount-Total), "0.00") AS AmountDue

Read more: MS Access: Format Function (with Numbers)[^]

Hi,

You could use a FORMAT function. In your case: FORMAT (Expression, "Fixed"). Fixed format is fixed to 2 decimal places (without thousand separator).

Your SQL should look like this:

SELECT
      CustomerName,
      OrderNumber,
      InvoiceAmount,
      FORMAT ((SELECT TotalPaid FROM InvoiceList WHERE InvoiceList.InvoiceId=NewInvoice_1.InvoiceId), "Fixed") AS AmountPaid,
      FORMAT ((InvoiceAmount-Total), "Fixed") AS AmountDue,
      InvoiceStatus
FROM NewInvoice_1;

Refer to: MS ACCESS: FORMAT FUNCTION (WITH STRINGS)[^]

这篇关于如何在MS Access表的查询设计中创建小数点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！