Find min from recordset

[Wahab]

Hi everybody
pls help me solve the simple thing:
I have record set which extract number of invoices for the given period,
from those invoice numbers I wnat to findout lowest and highest number of
invoce. My record set is like this:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Invoices WHERE Date
Between # " & Format(BeginningDate, "mm/dd/yy") & " # and # " &
Format(EndingDate, "mm/dd/yy") & " # and SalesCode = 1 ORDER BY InvoiceNo ")
I want to get result in StartNo and EndNo Field

Thanks in advance

 

[Paolo]

Hi Wahab,

if with StartNo you mean the lowest invoice number and with EndNo the
highest then

Set rst = CurrentDb.OpenRecordset("SELECT * FROM Invoices WHERE Date
Between # " & Format(BeginningDate, "mm/dd/yy") & " # and # " &
Format(EndingDate, "mm/dd/yy") & " # and SalesCode = 1 ORDER BY InvoiceNo ")
StartNo =rst!invoiceno
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Invoices WHERE Date
Between # " & Format(BeginningDate, "mm/dd/yy") & " # and # " &
Format(EndingDate, "mm/dd/yy") & " # and SalesCode = 1 ORDER BY InvoiceNo
desc")
EndNo =rst!invoiceno

or another way is
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Invoices WHERE Date
Between # " & Format(BeginningDate, "mm/dd/yy") & " # and # " &
Format(EndingDate, "mm/dd/yy") & " # and SalesCode = 1 ORDER BY InvoiceNo ")
StartNo =rst!invoiceno
rst.movelast
EndNo =rst!invoiceno

HTH Paolo

 

[Wahab]

Thanks Paolo
I'm not convience with the sort result, is there anyway to get max and min
of the
InvoiceNo from recordset? Please try for me.

 

[Paolo]

Why you aint convinced of the sort result? If you sort your recordset on
invoice number ascending the first record is the one with the lowest invoice
and if you sort it by invoice number descending the first one is the highest.
Using the min and max function require to group by the selected field and so
you'll have the lowest and the highest by day. If you wanna use the min and
max functions you have to create a temp table with the results of the
recordset with the conditions on the dates and then you can extract from this
table the min and the max with the internal function, but all this is trivial
'cause the trick with the sorting for me is correct.

BR Paolo