SQL Code Modification

[ElPerson]

Hi
Please I need to modify The following Code in order to show an error message when this condition is not Equal "Sales.[InvioceDate]=Revenues.[InvioceDate])"

the code is

Private Sub Form_AfterUpdate()


Dim strSql As String


strSql = "UPDATE Sales INNER JOIN Revenues ON (Sales.[CustomerName]=Revenues.[CustomerName]) AND (Sales.[InvioceDate]=Revenues.[InvioceDate]) SET Sales.[PaidOn] = Revenues.[PaymentDate] WHERE Sales.[PaidOn] Is Null;"
CurrentDb.Execute strSql, dbFailOnError
Me.Refresh


End Sub

Thank you

[Micron]

are these form fields? [InvioceDate] and [InvioceDate]
I don't get what you're trying to do. That is just a normal sql statement (although I'm not sure about the join portion) that doesn't get any data from the form, so I suppose you want to run it anyway as long as the 2 above mentioned fields on the form are equal?

[CJ_London]

you can't generate a error as such and like Micron, I don't see how your query works unless a customer never has more than one invoice on any given day.

So lets say you have customer X who has two invoices, one on 1st Jan and a second one on 1st Feb. Based on your requirement, you want the 1st Feb invoice reported as an error when looking at 1st Jan invoice and the 1st Jan invoice reported as an error when looking at the 1st Feb invoice.So both get reported as an error. To me that does not make sense.

Also, not sure why you have a paidon field in sales when you already have it noted in revenue. Why the duplication?

Suggest you provide some example data to illustrate what your problem is and what you are trying to achieve.

[orange]

ElPerson,

Please describe what you are trying to do in simple terms, and provide a clear sample showing before and after picture/statement to ensure we are all talking about the same thing. As others have said, your query seems suspect so clarity is important.
Good luck.

[ElPerson]

Hi Guys
Sorry for the late reply
I'm a bigginer in Access and I created this database for sales management.
The database have 2 tables, #Sales, #Revenues.
The #Sales table have "customerName, invoicedate, quantity, paidon"
The paidon column mostly be empty when inserting the sales.
The #Revenues table have "customerName, paymentdate, invoicedate,"
I already made access to fill the paidon column in the sales table automatically by the SQL Code, which when the customer name and invoice date are equal it fill paidon = payment date.
I want access to stop the data filling to the revenues table by the form if the customer Name and invoice date in the form are not equal to any data in the sales table.
I hope I cleared the Idea.

[CJ_London]

I want access to stop the data filling to the revenues table by the form if the customer Name and invoice date in the form are not equal to any data in the sales table

based on the sql you provided, that won't happen. You haven't answered the questions or provided example data and I still do see the reason for doing this when you already have the data in the revenues table.

[ElPerson]

the PaidON Column on the Sales Table will be empty and filled only when the invoice paid
I want this to be a reference point, so any payment will be entered to both tables sales and revenues

based on the sql you provided, that won't happen. You haven't answered the questions or provided example data and I still do see the reason for doing this when you already have the data in the revenues table.

[CJ_London]

are you expecting a response? if so, please answer the questions and provide example data as requested.

[Minty]

Your data structure makes little sense to me I'm afraid. Mainly the PaidOn field. Suppose someone part pays an invoice?

Rather than trying to keep track of the PaidOn Date in your sales table, simply record the invoice number (unique I hope) and payment amount and payment date in the revenue table.
Then a simple summed query grouped by invoice number would be able to identify invoices that weren't paid in full, you have the payment date in the revenue table - don't store it again.