troubleshooting Question

I need to loop through the returned recordset and look at just the NUMBERS past the last R

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft AccessVBA
16 Comments1 Solution30 ViewsLast Modified:
Setup:
I have a variable that houses a precalculated value like:
glb_cboRebate_Lookup = SLS-000118-MAIN-A1-5-R5
also a precalcuated string:
glb_string =  SLS-000118-MAIN-A1-5-R

I have an sql i'm running.
' checking to see if new glb_cboRebate_Lookup  exists in table already , so we do create an error with a duplicate.
Set R = CurrentDb.OpenRecordset("SELECT * FROM [Program_Header] WHERE [Rebate_ID] LIKE '" & glb_cboRebate_Lookup & "' ", dbOpenDynaset, dbSeeChanges)
' WHAT HAPPENS IF IT CANT FIND ID
record_count = R.RecordCount

If record_count > 0 Then
   MsgBox "ID Already Exists!", vbCritical, "Search ID"
   Exit Sub
End If

Open in new window



glb_cboRebate_Lookup -  looks like  SLS-000118-MAIN-A1-5-R5



So now i have to run a second sql that does a wilcard for just the following: variable
glb_cboRebate_LookupWild =  SLS-000118-MAIN-A1-5-R


' checking for wildcard on string
Set R = CurrentDb.OpenRecordset("SELECT * FROM [Program_Header] WHERE [Rebate_ID] LIKE '" & glb_cboRebate_LookupWild & "*' ", dbOpenDynaset, dbSeeChanges)

Open in new window


The return will look like the following:
Rebate_ID
SLS-000118-MAIN-A1-5-R1
SLS-000118-MAIN-A1-5-R10
SLS-000118-MAIN-A1-5-R4
SLS-000118-MAIN-A1-5-R5
SLS-000118-MAIN-A1-5-R6
SLS-000118-MAIN-A1-5-R7
SLS-000118-MAIN-A1-5-R8
SLS-000118-MAIN-A1-5-R9

What I need:
I need to loop through the returned recordset and look at just the NUMBERS past the last  R  -  "SLS-000118-MAIN-A1-5-R"
CREATE AN ARRAY of the numbers:
in this case  (1,10,4,5,6,7,8,9)
Get the max value in that array , in this case  10


jj = array(max_Numeric)
and then

take that numeric and add it to
glb_string =   glb_string & max_Numeric)

So the new Number will be
 SLS-000118-MAIN-A1-5-R11



I hope this makes sense:
Sorry for the long explanation
fordraiders
ASKER CERTIFIED SOLUTION
gowflow
Partner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros