Retrieve previuos record

[sargon]

Hello,
Please tell me if there is a way to retrieve a value from a field in the previous form record in a query that don't have ID (autonumber), using data/time ascending.The function PrevRecVal() from QrySampl can only be used for query that include ID.
Thanks

 

[gromit]

You might be able to do it with a set of related queries -

1. Find the Date/Time of the current record.
2. Do a query to find the Max of the Date/Time field WHERE Date/Time is less than the current date.
3. Do a query to retrieve the specific record that matches the Max value found in Step 2.

hth,

-g

 

[sargon]

Thanks for replay this question and for solution...I have try it but I do not think is what I need ..Please look at the attached file..there are one Access(query FisaMag Query) and one Word files.Initials must be equal with previous Finals.


Thanks

 

[gromit]

I've tried to create some queries to do this, but I'm not having much success.

You might be better off generating the values through some Visual Basic code. You would create a query in VBA to determine the correct date and then populate the fields with the appropriate values.

- g

 

[sargon]

In QrySampl I was found this code:

Function PrevRecVal(KeyName As String, KeyValue, FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("tblMileage", dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

It is working only for ID(autonumbers) if somebody
can modify this code to work for ascending date/time(without ID) ..please don't
hesitate.
Thanks

 

[gromit]

Actually, the code does not require AutoNumbers. The example of the function call is shown assuming a primary key called "ID".

See the attached db. The form displays different records in Table1. If you press the command button, it will fill the fields at the bottom of the form with the appropriate values from the previous record.

This *does* require that the records in the database are sorted in the appropriate order. If the existing table is not properly sorted, you can create a query to sort in the correct fashion and then base the form off of that.

- g

 

[sargon]

Thanks for replay, I have download few times the attachment db2, but it grerate 0 for each record:
3 C 01.03.2006 30
Find Prev
0 0 0 0

I have looked at the code but I did not succeded to modify it corectly.
If you can look at it or send it again ..please do
Thanks

 

[sargon]

The db2 generate only 0(zero) for each record...PLS can somebody look at the code?

 

[sargon]

DB2 does not work in my Access2000 but it Work in Access 2003!