[X]My Assistant

[geoffk7828]

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

[arnelgp]

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;

[GroverParkGeorge]

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.

[RJD]

Hi Geoff: I could interpret ...

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

[geoffk7828]

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.
 Customer_Complete___2.zip ( 32.74K )Number of downloads: 4

This post has been edited by geoffk7828: Sep 4 2019, 03:10 AM

[RJD]

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)

 Customer_Complete_2_Rev1.zip ( 34.26K )Number of downloads: 2

 

[geoffk7828]

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
 Customer_Complete___2.zip ( 33.82K )Number of downloads: 1

[RJD]

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)

 Customer_Complete_2_Rev2.zip ( 31.21K )Number of downloads: 3

 

[geoffk7828]

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

 Customer_Complete___2a.zip ( 30.17K )Number of downloads: 5

[RJD]

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

Continued success with your project.

Regards,
Joe