Finding the last similar record... [Resolved]

[DKasler]

I have an app that deals with ording supplies..

Now when someone chooses the item the want to order I want to display the date this item was last orderd..

I've looked up records before with no problem, but usually a record using a specific item of data specified by the user...

this time i need to find the last entered record with similar criteria.

[Hack]

How about something like

strSQL = "SELECT MAX(datefield) FROM table WHERE field = '" & item_that_was_just_ordered & "'"

[DKasler]

i think that could work... Thanks much.

[DKasler]

How would that be inputed to a label once its called... I tried

Label17.Caption = datPrimaryRs![Max(SupDate)]

but got this error "Item could not be found in the collection corresponding to the requested name or ordinal"

I also tried the about code with out the MAX()

Here is the complete code I used.

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
    Dim datPrimaryRs
    
    ' set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\supplies\supplies.mdb;Persist Security Info=False"
    sSQL = "select MAX(SupDate) from SupplyMaster where SupType = '" & DBCombo2.Text & "'"
    
    ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
 
    ' create a recordset using the provided collection
    Set datPrimaryRs = New ADODB.Recordset
    datPrimaryRs.CursorLocation = adUseClient
    datPrimaryRs.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
    
    If IsNull(datPrimaryRs![MAX(SupDate)]) Then
        Label17.Caption = "Never Ordered"
    Else
        Label17.Caption = datPrimaryRs![MAX(SupDate)]
    End If

[dglienna]

You don't need the MAX in the if statement.

If IsNull(datPrimaryRs![SupDate]) Then
        Label17.Caption = "Never Ordered"
Else
        Label17.Caption = datPrimaryRs![SupDate]
End If

[DKasler]

I removed the Max from the if statement and Im still getting the same error.

[dglienna]

Try

datPrimaryRs.Fields(0)

' where 0 is the position in your recordset

[DKasler]

Im not 100% sure where the

datPrimaryRs.Fields(0)

needs to go. Though I did try it to the best of my ability and i got the same error as before.

[DKasler]

like... *bump* - for suuurrre.

[westconn1]

Label17.Caption = sSQL(supdate)

try it, i'm not sure

pete

[DKasler]

Label17.Caption = sSQL(supdate)

try it, i'm not sure

pete

No, that didnt do it either. It just told me that the variable "supdate" was not defined.


On a side note, I even tried changing the field in the db to a date/time field and that made no difference.

[westconn1]

i would think that sSQL should return only one record which must have must have the date in it you want, so it is just a matter off getting it out.

put a breakpoint in your code just after you get sSQL, then have a look in the locals window at how it is made up, i presume it should be an array of fields

pete

[space_monkey]

How would that be inputed to a label once its called... I tried

Label17.Caption = datPrimaryRs![Max(SupDate)]

but got this error "Item could not be found in the collection corresponding to the requested name or ordinal"

I also tried the about code with out the MAX()

Here is the complete code I used.

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
    Dim datPrimaryRs
    
    ' set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\supplies\supplies.mdb;Persist Security Info=False"
    sSQL = "select MAX(SupDate) from SupplyMaster where SupType = '" & DBCombo2.Text & "'"
    
    ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
 
    ' create a recordset using the provided collection
    Set datPrimaryRs = New ADODB.Recordset
    datPrimaryRs.CursorLocation = adUseClient
    datPrimaryRs.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
    
    If IsNull(datPrimaryRs![MAX(SupDate)]) Then
        Label17.Caption = "Never Ordered"
    Else
        Label17.Caption = datPrimaryRs![MAX(SupDate)]
    End If

This has worked for me in the previous programs

If IsNull(datPrimaryRs(0)) then
   Label17.caption = "Never Ordered"
Else
   Label17.Caption = datPrimaryRs(0)
end if

I'm not sure why you have the "!" in there

HTH

[DKasler]

This has worked for me in the previous programs

If IsNull(datPrimaryRs(0)) then
   Label17.caption = "Never Ordered"
Else
   Label17.Caption = datPrimaryRs(0)
end if

I'm not sure why you have the "!" in there

HTH

Thank you so much space monkey. That worked just the way I had hoped.

As for the "!" if you dont have it the system expects the end of statement to be "datprimaryRS" so you need the "!" to add the field after