text conversion to integer.

[hooi]

I have a text field which contains user entered ID. The id varies such as M0017, 0007, S006 and so on.

If a user enters a new ID say M0018, I'll need to pull the previously entered record (ie. M0017) for certain data processing.

I'd like to know what built-in function I should use so that based on the newly entered record, say M0018, I will pull the correct record via query to get M0017.

Any help rendered would be appreciated.

 

[Rich]

Does your table have a sequential key field

 

[hooi]

yes. There is an auto-generated key index field for the table.

 

[Rich]

Here's one way
Function PrevRecValc(KeyName As String, KeyValue, _
FieldNameToGet As String, Source As String)
Dim Rs As Recordset

On Error GoTo Err_PrevRecValc

' The default value is zero.
PrevRecValc = 0

' Get the form recordset.
Set Rs = CurrentDb().OpenRecordset((Source), 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.
PrevRecValc = Rs(FieldNameToGet)

Bye_PrevRecValc:
Exit Function
Err_PrevRecValc:
Resume Bye_PrevRecValc
End Function

 

[hooi]

Thanks...