arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 06:25
- Joined
- May 7, 2009
- Messages
- 19,237
if still not working add a function to a Module:
You're query will look like this:
Code:
Public Function genSN(ByVal HealthCardNo As Variant, id As Long) As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
HealthCardNo = HealthCardNo & vbNullString
If Len(HealthCardNo) = 0 Then
Exit Function
End If
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset( _
"Select HealthInsID " & _
"From tblHealthInsurance " & _
"Where HealthCardNo = '" & HealthCardNo & "' " & _
"Order By IssueDate, HealthInsID;", _
dbOpenSnapshot, _
dbReadOnly)
With rs
If Not (.BOF And .EOF) Then
.FindFirst "HealthInsID = " & id
Do Until .BOF
i = i + 1
.MovePrevious
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
genSN = i
End Function
You're query will look like this:
Code:
SELECT genSN(HealthcardNo, HealthInsID) AS SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class],
ExpiredDate, TerminationReasons, Note
FROM tblHealthInsurance
ORDER BY IssueDate, HealthInsID;