473,287 Members | 1,650 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 6445
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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:
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
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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...

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.