Solved Code to Get Required Part of String after Truncating Unnecssary Part (1 Viewer)

Pac-Man

Active member
Local time
Today, 14:50
Joined
Apr 14, 2020
Messages
408
Hello,

I want to perform following objectives using the code:

1. Initial string (Report No) is ABCD-EF/1122 (Rev.1)
2. First I want to truncate it to ABCD-EF/1122
3. Then I want to get ReportID in tblReports of the truncated Report No.

I am using following code:

SQL:
Dim sReportNo as String
Dim lngReportNo as Long

sReportNo = CStr(me.txtNo) 'txtNo is equal to ABCD-EF/1122 (Rev.1)'
sReportNo = Left(sReportNo, Len(sReportNo) - InStrRev(sReportNo, " ")) 'ReportNumber is the field name in tblReports where report no are stored in either ABCD-EF/1122 or ABCD-EF/1122 (Rev.1) format'
lngReportNo = DLookup("ReportID", "tblReports", "ReportNumber =" & sReportNo)

It is not giving me required result. Where am I doing wrong.

Best Regards
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,175
Code:
Dim sReportNo As String
Dim lngReportNo As Long
Dim iStart As Integer
Dim sRev As String
sReportNo = CStr(Me.txtNo) 'txtNo is equal to ABCD-EF/1122 (Rev.1)'
iStart = InStrRev(sReportNo, "(")
If iStart > 0 Then
    sRev = Mid(sReportNo, iStart)
    sReportNo = Replace(sReportNo, sRev, "")
End If
sReportNo = Trim(sReportNo)
'sReportNo = Left(sReportNo, Len(sReportNo) - InStrRev(sReportNo, " ")) 'ReportNumber is the field name in tblReports where report no are stored in either ABCD-EF/1122 or ABCD-EF/1122 (Rev.1) format'
lngReportNo = DLookup("ReportID", "tblReports", "ReportNumber ='" & sReportNo & "'")
 

Pac-Man

Active member
Local time
Today, 14:50
Joined
Apr 14, 2020
Messages
408
Code:
Dim sReportNo As String
Dim lngReportNo As Long
Dim iStart As Integer
Dim sRev As String
sReportNo = CStr(Me.txtNo) 'txtNo is equal to ABCD-EF/1122 (Rev.1)'
iStart = InStrRev(sReportNo, "(")
If iStart > 0 Then
    sRev = Mid(sReportNo, iStart)
    sReportNo = Replace(sReportNo, sRev, "")
End If
sReportNo = Trim(sReportNo)
'sReportNo = Left(sReportNo, Len(sReportNo) - InStrRev(sReportNo, " ")) 'ReportNumber is the field name in tblReports where report no are stored in either ABCD-EF/1122 or ABCD-EF/1122 (Rev.1) format'
lngReportNo = DLookup("ReportID", "tblReports", "ReportNumber ='" & sReportNo & "'")
Worked like charm. Thanks you so much.
 

Users who are viewing this thread

Top Bottom