UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Tables And Query, Access 2016    
 
   
geoffk7828
post Sep 2 2019, 02:45 AM
Post#1



Posts: 30
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


Hi All,

I am trying to compare the data in matching Fields in 2 Tables, then Highlight the Field in the Form by changing its colour to Red.

The Tables and Fields are Table 1 is "Payments" with Field "Payment" and Table 2 is "PaymentsDue" with Field "PaymentDue".

If Field "Payment" and Field "PaymentsDue" are equal then no action, if they are not equal then change Form Field "PaymentsDue" would become red.

I have tried using an IIF statement Expr1: IIf([Payments].[Payment]<>[PaymentsDue],"Yes","No") (I am using Yes and No as a starting point to get it to work).

Thanks

Geoff
Go to the top of the page
 
arnelgp
post Sep 2 2019, 05:15 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


there must be a common Particular for both
payment and paymentDue, say Invoice number:

select payments.invoiceNumber, payments.payment, paymentsDue.paymentDue, iif(Nz(payments.payment, 0) < nz(paymentsDue.paymentDue,0), "No", "Yes") As Paid
from payments left join paymentsdue on payment.invoicenumber = paymentsdue.invoicenumber;

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 2 2019, 07:34 AM
Post#3


UA Admin
Posts: 36,179
Joined: 20-June 02
From: Newcastle, WA


As Arnel points out, you need to relate the two tables in some way. He suggests InvoiceNumber, which is a likely candidate, but without knowing more about the tables, that's just a starting assumption. One could also guess that these are payments on a loan, with a schedule of payments due at some interval on that loan. And I guess there might be other kinds of arrangements, like a group membership, where payments for membership must be paid regularly.

In any case, payments are made for a particular purpose, are they not? However PaymentsDue are generated, they must be related to something like an invoice, a loan or a membership. So that's how you join the tables. You use whatever field represents that invoice in both tables, or that loan in both tables, etc.

That means the basic syntax suggested would apply, but would need to be based on the actual values in your tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Sep 2 2019, 09:32 AM
Post#4


UtterAccess VIP
Posts: 10,146
Joined: 25-October 10
From: Gulf South USA


Hi Geoff: I could interpret ...
QUOTE
then Highlight the Field in the Form by changing its colour to Red
... to mean that you already have a form with a record source that properly supplies the two fields in controls. If this is the case, then you might try Conditional Formatting on the PaymentDue with the CF Expression of ...

[Payment]<>[PaymentDue] ... and the text, say, set to Red for this control. (Note that this will highlight even overpayments.)

But I might even add a BalanceDue control and CF that instead when it shows a balance due.

I guess we need some further guidance from you about where you are with the form development - and possibly a db to show us where you are now overall so we can assist you better.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
geoffk7828
post Sep 4 2019, 02:34 AM
Post#5



Posts: 30
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


Thank you for your replies.

The objective of this is to have a form display a list of work tasks that:
1. Have been completed and not paid
2. Display and highlight tasks have been completed and paid but do not match

The payments are for work done, each work task is paid at a set rate with "Non Standard Install" being an exception, which can be any value.

I have attached a sample of the DB, in Table "Customer_Complete_JobOutcome" is the work task values with "Non Standard Install" set at "1".

I have used Conditional Formatting as suggested by Joe which is working: one of the lines in the form has a "PaymentDue" of 266.00 and a "Payment" of 269.00 it has highlighted the field red, the line immediately below, which is a "Non Standard Install" has a "PaymentDue" which is blank and a "Payment" of 400.00, regardless of what value is entered into the Field "PaymentDue" of Table "Customer_Complete_JobOutcome", Field "Non Standard Install" it does not update the Field "PaymentDue" of the form.

Date Job_ID Suburb Job_Outcome Tech_Name Comments PaymentDue Payment
1/08/2019 1579172 Sandy Creek Completed Install John 266 296
1/08/2019 1578944 Sandy Creek Non Standard Install John 400

The next step of this procedure is have the unmatched row remain in the "Payments_subform"

The Query "Customer_Completed Without Matching Payments" is the control for the form "Payments_subform", the Field "[Payments].[JobNo]" criteria: is normally set to "Is Null".

Just noticed that when the "Is Null" is added to the Query the Field [Payments].[Payment] does not populate is there any reason for this.
Attached File  Customer_Complete___2.zip ( 32.74K )Number of downloads: 4

This post has been edited by geoffk7828: Sep 4 2019, 03:10 AM
Go to the top of the page
 
RJD
post Sep 4 2019, 08:42 AM
Post#6


UtterAccess VIP
Posts: 10,146
Joined: 25-October 10
From: Gulf South USA


Hi again: Take a look at my revision to your last db. Perhaps this will answer your questions.

First, you made Payment and PaymentDue text data types. I changed these to currency.
Second, in the queries, I used the NZ function to force a zero when the value is Null (in the case of payments, no payment in the table for that Job).

These were then accommodated in the forms. When a comparative value is Null, a logical comparison for CF cannot be made the way you are doing it, but can when the comparative value is zero.

I also note that you have a field named PaymentDue in two different tables. You should check whether this is appropriate, or perhaps if one needs to be renamed for clarity. Just something to look at in this.

See if this gives you what you need, or give us more direction from here.

HTH
Joe
Attached File(s)
Attached File  Customer_Complete_2_Rev1.zip ( 34.26K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
geoffk7828
post Sep 7 2019, 09:33 PM
Post#7



Posts: 30
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


Hi Joe,

I have been working with your suggestions and have come to the decision that using 1 Form is getting too complicated, I have decided to use 2 Forms:

Payments_subform and Payments_Confirm_subform using 2 Queries.

Payments_subform and its Query are working exactly as I require so I believe needs no more work.

Payments_Confirm_subform is where I want displayed only "Unmatched" payments which is the result of [PmtDue]<>[Payments].[Payment], which seems to be working ok, it gives me the correct results for the entire Table, when I try to display only the "Unmatched" records I get no results, I have been trying to use Is Not Null and variations of IIF statements, but all I get is no records.

Geoff
Attached File  Customer_Complete___2.zip ( 33.82K )Number of downloads: 1
Go to the top of the page
 
RJD
post Sep 7 2019, 10:52 PM
Post#8


UtterAccess VIP
Posts: 10,146
Joined: 25-October 10
From: Gulf South USA


Hi Geoff: The match between the PaymentDue and the Payment cannot be satisfied because of the data type of the Payment, which is text (with Nulls when there is no value). You must either correct that in the table (as Currrency, for example, with a zero default value), or do as I have done and use the CCur(NZ(... approach. Take a look at my modification to your db, attached. See the query and the slight mod to the form as well. Also note that I removed the Is Not Null as well. See if this is what you are trying to do.

HTH
Joe
Attached File(s)
Attached File  Customer_Complete_2_Rev2.zip ( 31.21K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
geoffk7828
post Sep 7 2019, 11:24 PM
Post#9



Posts: 30
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


Hi Joe,
Just found the answer:

In my reply Posted Sep 4 2019, 08:34 AM I mentioned that "Customer_Complete_JobOutcome" Field "Non Standard Install" has a "PaymentDue" which is blank, while trying different approaches to solve my problem I discovered that "Non Standard Install" was in fact in Table "Customer_Complete_JobOutcome" typed as "Install Non Standard" hence the blank.

After making the correction everything fell into place, so I now have what I originally wanted both unpaid and unmatched payment in the 1 form.

Thank you for all your help I have learnt a lot from your feedback

I have included the working DB for you.

Geoff

Attached File  Customer_Complete___2a.zip ( 30.17K )Number of downloads: 5
Go to the top of the page
 
RJD
post Sep 8 2019, 08:17 AM
Post#10


UtterAccess VIP
Posts: 10,146
Joined: 25-October 10
From: Gulf South USA


You are welcome, Geoff. Glad you got that working.

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 01:33 PM