I have a parameter query asking the

[blln]

I have a parameter query asking the user to enter an Invoice Number. My parameter query is as follow:

Field: InvoiceNum
Table: Invoices
Criteria: In (select top 1 [InvoiceNum] from Invoices where [AccountNum]=[Customers].[AccountNum] and [InvoiceNum]<[Enter Invoice Number] order by [InvoiceNum] DESC)

Field: AccountNum
Table: Invoices

Field: Payment
Table: Invoices

When a user enters an Invoice Number, the result will give the lastest invoice that has an Invoice Number less than the Invoice Number the user entered for each Account Number as well as the Payment the customer paid for each of those invoice.

This works great except when the user happens to enter the smallest Invoice Number. Meaning there is no other Invoice Number smaller than the Invoice Number the user entered. The query will give no result.

That is not what I want. I want the query to return the result as stated above if there are invoices with Invoice Number less than what the user entered, else if there is no other Invoice Number smaller than the one entered by the user then return only the Invoice Number, Account Number, and Payment of that particular Invoice Number entered by the user.

Any help on this is greatly appreciated. Thank you.

 

[Steve101]

Based on your statement .... else if there is no other Invoice Number smaller than the one entered by the user then return only the Invoice Number, Account Number, and Payment of that particular Invoice Number entered by the user., why would'nt your criteria test simply become:

Criteria: In (select top 1 [InvoiceNum] from Invoices where [AccountNum]=[Customers].[AccountNum] and [InvoiceNum]<=[Enter Invoice Number] order by [InvoiceNum] DESC)

All I've done is change the less than operator to less than or equal, which will allow your parameter invoice number to be included in the query results.

Hope this helps,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cmmrfrds]

You may get by with an OR condition. I believe when the first OR is true the second OR is not evaluated. So, the order is important with the < first.

Criteria: In (select top 1 [InvoiceNum] from Invoices where ([AccountNum]=[Customers].[AccountNum] and [InvoiceNum]<[Enter Invoice Number]) OR
([AccountNum]=[Customers].[AccountNum] and [InvoiceNum]=[Enter Invoice Number])
order by [InvoiceNum] DESC)

 

[blln]

Thank you Steve and cmmrfrds for your replies. Both of your solutions work great. It does return the smallest Invoice Number when no other Invoice Number is smaller than the Invoice Number entered by the user.

However, I still have a related question you might be able to help me. What I was trying to do with this query is to create a report from this query so that it shows the payment a customer paid for the previous invoice (the invoice before the invoice with the Invoice Number entered by the user). If there is an invoice with an Invoice Number less than the Invoice Number entered by the user then it correctly shows the payment for the previous invoice. But if the Invoice Number entered by the user is already the smallest (this is the solutions you both gave me), the payment won't be correct because it obviously has no other previous payment since it is the very first invoice for this customer. How can I make it so that the query solutions you both gave me go one step further by returning the payment as zero when the Invoice Number is the smallest.

Thank you again for any help.

 

[Steve101]

OK. Try the following. It actually takes two queries; the second incorporating the first.

(a) Create the first query, as follows:

   SELECT CI.*
   FROM tblCustomerInvoice AS CI
   WHERE CI.Invoice= (Select Max(Invoice) 
                      From tblCustomerInvoice
                      Where Customer = CI.Customer
                      AND Invoice < [Enter Last Invoice No]);

Call this query qryLastPaidInvoices01

(b) Create a second query, as follows:

    SELECT C.Customer, qLPI.Invoice, qLPI.InvoiceDate, 
           qLPI.InvoiceAmount
    FROM tblCustomer AS C 
    LEFT JOIN qryLastPaidInvoices01 AS qLPI 
    ON C.Customer = qLPI.Customer;

Call this query qryLastPaidInvoices02JoinCustomer

Note that the above examples assume a couple of things about your table names, and the field names within the tables. Change these as appropriate to your own situation.

The first query uses whats called a correlated subquery to extract the appropriate Invoice details record for each customer (that has such an invoice record).

The second query forms an &quot;outer join&quot; between the first query and the Customer table, ensuring that all customers are represented in the query, even if they dont have a corresponding set of prior invoice details.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[blln]

Hello Steve,

Unfortunately your solution is still not running correctly as expected.

To make it easier, I came up with an idea - coding. I created a function so that it goes straight into the Invoice form. I created a new field in the Invoice table with the name PrevPayment. Every time a user enters a payment for an invoice in the form, this function would be triggered so that it moves to the previous record, copies that record's payment, moves back to the current record, and pastes the previous record's payment into the current record's PrevPayment field.

The function correctly copies the previous payment. The problem is it pastes that payment into every and all records PrevPayment field. This results in all invoices of all account numbers to have the same previous payment amount. I want it to paste only into the current record's PrevPayment. The function is below. Please take a look and let me know how to fix. Thank you.

Public Function PreviousPayment()
On Error GoTo Err_PreviousPayment

Dim str As Currency
Dim frm As Form 'Declare object
Set frm = Forms(&quot;MainForm&quot(&quot;SubForm&quot.Form
Set rst = frm.RecordsetClone 'Set sub form's recordset

rst.Bookmark = frm.Bookmark

rst.MovePrevious
If rst.BOF Then
str = &quot;0&quot;
Else
str = rst![Payment]
End If

rst.MoveNext
frm![CopyPayment] = str

rst.Bookmark = frm.Bookmark

rst.Close

Exit_PreviousPayment:
Set rst = Nothing 'Deassign all objects.
Exit Function

Err_PreviousPayment:
MsgBox &quot;Error number &quot; & Err.Number & &quot;: &quot; & Err.Description
Resume Exit_PreviousPayment

End Function

 

[blln]

Hello Steve,

Sorry for any inconveniences. I've just solved my problem. Thank you very much for all your help.