473,287 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

How to use the criteria on a calculated field?

If I have a calculated field, [amount due], calculated from [Total] -
[TotalPayment], then on the same query, I used [amount due] > 0. It
prompts me for value for [amount due]. I have to use another query,
copy everything into the new query then use [amount due] > 0. Is there
a better way to do this?
Thank you very much, ming.

Nov 13 '05 #1
4 6447
swingingming wrote:
If I have a calculated field, [amount due], calculated from [Total] -
[TotalPayment], then on the same query, I used [amount due] > 0. It
prompts me for value for [amount due]. I have to use another query,
copy everything into the new query then use [amount due] > 0. Is there
a better way to do this?
Thank you very much, ming.


([Total] - [TotalPayment]) > 0

--
[OO=00=OO]
Nov 13 '05 #2
swingingming wrote:
If I have a calculated field, [amount due], calculated from [Total] -
[TotalPayment], then on the same query, I used [amount due] > 0. It
prompts me for value for [amount due]. I have to use another query,
copy everything into the new query then use [amount due] > 0. Is there
a better way to do this?
Thank you very much, ming.


([Total] - [TotalPayment]) > 0

--
[OO=00=OO]
Nov 13 '05 #3
Hi Travor,
It doesn't work. Because I have Sum in the query. Here's the SQL from
the Query:

SELECT tblCustomers.CustomerID, tblCustomers.CompanyName,
Sum(tblOrders.Total) AS TotalOfSum, Sum(qrySumOfPayments.TotalPayments)
AS TotalPaymentsOfSum, nz([TotalOfSum])-nz([TotalPaymentsOfSum]) AS
AmountDue
FROM tblCustomers INNER JOIN (tblOrders LEFT JOIN qrySumOfPayments ON
tblOrders.OrderID = qrySumOfPayments.OrderID) ON
tblCustomers.CustomerID = tblOrders.CustomerID
GROUP BY tblCustomers.CustomerID, tblCustomers.CompanyName;

But if there's no better way, I'll stay with my solution for now and
throw away elegency.
ming

Nov 13 '05 #4
Hi Travor,
It doesn't work. Because I have Sum in the query. Here's the SQL from
the Query:

SELECT tblCustomers.CustomerID, tblCustomers.CompanyName,
Sum(tblOrders.Total) AS TotalOfSum, Sum(qrySumOfPayments.TotalPayments)
AS TotalPaymentsOfSum, nz([TotalOfSum])-nz([TotalPaymentsOfSum]) AS
AmountDue
FROM tblCustomers INNER JOIN (tblOrders LEFT JOIN qrySumOfPayments ON
tblOrders.OrderID = qrySumOfPayments.OrderID) ON
tblCustomers.CustomerID = tblOrders.CustomerID
GROUP BY tblCustomers.CustomerID, tblCustomers.CompanyName;

But if there's no better way, I'll stay with my solution for now and
throw away elegency.
ming

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Jack | last post by:
Hi, I have posted this problem before. Apprently, the suggestion took care of the problem. However, still I am getting the above error message. I am using a session variable to transfer a value...
2
by: Enterprise | last post by:
I'm stuck. I use Access 2000. I have a date field in a query. What I need done is if a Limit_Date function returns a true, I want the date field to be Between 1/1/02 and 1/1/03, otherwise I want it...
0
by: swingingming | last post by:
If I have a calculated field, , calculated from - , then on the same query, I used > 0. It prompts me for value for . I have to use another query, copy everything into the new query then use >...
5
by: jillandgordon | last post by:
I hope this group does not mind the most basic questions. I am trying to teach myself Access in two different versions. My company now uses Access 97 but, in the next few months, will be...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
1
by: meekie | last post by:
Hi I have had difficulty with this for some time. I have a database which has the fields COST, GST and RRP. The issue is that RRP is calculated using the other two fields (COST and GST) I...
2
by: dympna | last post by:
Hi can anyone suggest a fix for this... as I am a novice in access. I have created a training table with the following fields Employee Name - joe Training Received - Fork lift Date Received...
23
klarae99
by: klarae99 | last post by:
In Access 2003 I am creating an Inventory Database from scratch. There are two tables (tblProductInformation and tblSubProducts) that I am trying to use in a queary to determine the number of Items...
17
by: sharsy | last post by:
Hello guys, I would like some help in generating query criteria that will identify credit cards that have expired on an access database. The specific Field is formatted with a Data Type of...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.