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

Topic

New Reply

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

IIf(DMax([tblSalesOrder].[SalesOrderNumber]<[tblSalesOrderInvoice].
[SOInvoviceNumber]),[tblSalesOrder].[SalesOrderNumber]+1,[tblSalesOrderInvoice].
[SOInvoiceNumber]+1)

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.

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote