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. 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]
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]
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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 >...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
| |