Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing and picking the highest of the two (2000/2002)

    I have two tables "tblSalesOrder" and "tblSalesOrderInvoice", That I have to compare two max values.
    The values to be compared are "SalesOrderNumber" and "SOInvoiceNumber".
    After the two max values have been compared I need the higher of the two to be placed into a field.

    Some along the lines of

    When entering data into the SalesOrder Form
    <pre>IIf(DMax([tblSalesOrder].[SalesOrderNumber]<[tblSalesOrderInvoice].
    [SOInvoviceNumber]),[tblSalesOrder].[SalesOrderNumber]+1,[tblSalesOrderInvoice].
    [SOInvoiceNumber]+1)</pre>



    After a Sales Order is process some of the data will be appended to the "SalesOrderInvoice" table with
    a ship date as the Invoive date. And some to the "SalesOrder" table as a back order.

    This is done until the customers Purchase Order is complete.

    When entering data into the SalesOrderInvoice Form (this is not done very much, maybe 24 times a year)
    I need to find the max value between the two tables and place it in the "SOInvoiceNumber" field.

    Could someone point me in the direction of getting this acomplished.
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing and picking the highest of the two (2000/2002)

    Dim lngMax1 As Long
    Dim lngMax2 As Long
    Dim lngMax As Long

    lngMax1 = DMax("SalesOrderNumber", "tblSalesOrder")
    lngMax2 = DMax("SOInvoiceNumber", "tblSalesOrderInvoice")
    If lngMax1 > lngMax2 Then
    lngMax = lngMax1
    Else
    lngMax = lngMax2
    End If
    ' do something with lngMax
    ...

    Note: wouldn't it be more efficient to use one table, with a Yes/No field to distinguish backorders from other orders?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing and picking the highest of the two (2000/2002)

    Yes Sir it would, and that the way we were doing it with. But the Accounts Payables departments for four of our customers have to have everything just so. I've take work-a-rounds, and some skull duggery to make the Invoice (form SalesOrder) just the way they need to (want) the Invoice to be, and there are times when they present a reverse Invoice, which has to be processed as an Invoice, not as a SalesOrder, this is an Accountant notion. If they come by to audit their account (they want to goto the table level, over somebuddys dead body with this) it had better be correct. As luck would have it the four of them do pretty much the same method of business.

    Thank you much for your time
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •