How to create decimal points in query design of MS Access table ?

Question

0.00/5 (No votes)

See more:

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

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;

Output

SQL

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

Posted 21-Mar-14 2:54am

srihari1904

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-03-21T03:20:00

Solution 1

Try this:

SQL

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

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

Posted 21-Mar-14 3:20am

Peter Leow

Updated 21-Mar-14 3:36am

v3

Comments

srihari1904 22-Mar-14 5:41am

Hi Thank you. working but in empty data's it showing error "#Error" like this ? how to solve it. That is "TotalPaid column is null for another Id" in this time access showing error "#Error"

Andrius Leonavicius · Accepted Answer · 2014-03-21T03:51:00

Solution 2

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:

SQL

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)[^]

Posted 21-Mar-14 3:51am

Andrius Leonavicius

Updated 21-Mar-14 4:16am

v3

Comments

srihari1904 22-Mar-14 5:41am

Thank you very much its working fine

Andrius Leonavicius 22-Mar-14 5:57am

You're welcome. :)

srihari1904 22-Mar-14 7:58am

Hi Thank you. working but in empty data's it showing error "#Error" like this ? how to solve it. That is "TotalPaid column is null for another Id" in this time access showing error "#Error"

Andrius Leonavicius 22-Mar-14 8:29am

I think that FORMAT function should handle NULL values without error, but you can replace NULL values to 0 like this:
IIF(ISNULL(ColumnName),0,ColumnName)

srihari1904 24-Mar-14 6:36am

Hi, Sorry am new to access how to make this query ?

IIF(ISNULL(AmountPaid),0,AmountPaid),
FORMAT ((SELECT TotalPaid FROM InvoiceList WHERE InvoiceList.InvoiceId=NewInvoice_1.InvoiceId), "Fixed") AS AmountPaid,

this code didn't working help me

Andrius Leonavicius 24-Mar-14 6:52am

It should be like this:
FORMAT ((SELECT IIF(ISNULL(TotalPaid),0,TotalPaid) FROM InvoiceList WHERE InvoiceList.InvoiceId=NewInvoice_1.InvoiceId), "Fixed") AS AmountPaid

srihari1904 24-Mar-14 6:48am

FORMAT ((SELECT IsNull(TotalPaid,0) FROM sumoftotalpayment WHERE sumoftotalpayment.InvoiceId=NewInvoice_1.InvoiceId), "0.00") AS AmountPaid

I tried this code but showing error "Wrong number of argument is used with function in query expression 'IsNull(TotalPaid,0'"

Andrius Leonavicius 24-Mar-14 6:59am

No, you can't use IsNull function like that in MS ACCESS. IsNull ( expression ) function returns TRUE if the expression is a null value. Otherwise, it returns FALSE...