String Manipulation !!!

[amal1973]

Hello
I have this string “ACCR 9/30 RECR BNS 03058864”
I need to build the where statement in a query that will look at the first 4 characters and the characters after the date. So in this example it should look at “ ACCR” and “RECR BNS “

This is an example of what am trying to do …

strSql = " UPDATE Tbl_Report_Download SET Tbl_Report_Download.[Line Item] = 'Recruiting Bonus Accrual'" & _
" WHERE (((Left([DESC 1],3))='REV')) And Tbl_Report_Download.[CORP ORG SECOND LEVEL] ='46A' And (Right([DESC 1],3)='BNS') ;"
DB.Execute strSql

Thanks

 

[JunglesMcBeef]

Is that last number's field size always 8?

 

[JunglesMcBeef]

Anyway, if so try this:

intSpaceChar = 0
strField1 = ""
strField2 = ""
strNewChar = ""

For i = 1 to (Len(YourString) – 8)
strNewChar = Mid(YourString, i, 1)

if strNewChar = " " then
intSpaceChar = intSpaceChar + 1
elseif intSpaceChar = 0 then
strField1 = strField1 & Mid(YourString, i, 1)
end if

if intSpaceChar > 1 then
strField2 = strField2 & Mid(YourString, i, 1)
end if
next i

Use the Field strings where you are using the left and right functions, just yell out if you don't get it. Good Luck

 

[TonyJollans]

Hi amal1973,

Assuming you have space separated elements in your string this should work ..

WHERE [DESC1] Like "ACCR*" AND Mid$([DESC1],InStr(6,[DESC1]," &quot+1) Like "RECR BNS*"

Enjoy,
Tony

 

[amal1973]

JunglesMcBeef..

Thanks for your reply, Two thing I need to understand cause I started to have all kind of errors J .
My String is in a record set, and about yelling out if you don't get it I am yelling now. I am missing how to put the string in the where clause.

And yes the last number's field size is always 8

This what I did so far, but errors as type mismatch

Set rstLoad = DB.OpenRecordset("Select * from Tbl_Report_Download&quot

While Not rstLoad.EOF
intSpaceChar = 0
strField1 = ""
strField2 = ""
strNewChar = ""
Dim intLen As Variant

intLen = Len(rstLoad("Desc 1&quot - 8) ------------Error
For i = 1 To inlen
strNewChar = Mid(rstLoad("Desc 1&quot, i, 1)

If strNewChar = " " Then
intSpaceChar = intSpaceChar + 1
ElseIf intSpaceChar = 0 Then
strField1 = strField1 & Mid(rstLoad("Desc 1&quot, i, 1)
End If

If intSpaceChar > 1 Then
strField2 = strField2 & Mid(rstLoad("Desc 1&quot, i, 1)
End If
Next i



TonyJollans..
Having problems putting it inside a strsql in vba?

strSql = "UPDATE Tbl_Report_Download SET Tbl_Report_Download.[Line Item] = 'Recruiting Bonus Accrual'" & _
' " WHERE (((Tbl_Report_Download.[DESC 1]) Like 'ACCR*') AND ((Mid$([DESC 1],InStr(6,[DESC 1], " &quot+ 1 ))Like 'RECR BNS*'));"

can you help with that ..

thank you all

 

[TonyJollans]

Hi amal1973,

You appear to have an extra apostrophe (') at the start of the second line, but other than that what is causing you a problem is the " " in the Instr; change it to ' ', giving ..

strSql = "UPDATE Tbl_Report_Download SET Tbl_Report_Download.[Line Item] = 'Recruiting Bonus Accrual'" & _
" WHERE (((Tbl_Report_Download.[DESC 1]) Like 'ACCR*') AND ((Mid$([DESC 1],InStr(6,[DESC 1], ' ')+ 1 ))Like 'RECR BNS*'));"

Enjoy,
Tony

 

[CajunCenturion]

This certainly looks like a good opportunity to use Regular Expressions, however there is insufficient information provide to insure that all cases can be covered. Part of the confusion is what appears to be two different data strings used in your original post.

If you could, please define the various parts of this string, and what you know about each part.
ACCR 9/30 RECR BNS 03058864
So far, it seems like the string has four parts
1 - ACCR: Tran Type? - what are the possibilities, ie can this be two words, is the length fixed, or what is the maxlen, can ther be a space embedded in the this field.
2 - 9/30: Date? - Either m/yy or mm/yy, or are their other options as well?
3 - RECR BNS: What does this mean? Will it always be two words? How can you know were it ends?
Can you describe in these type terms the format of your data string?
4 - 03058864: Always an 8 digit string? Is this always the last field in the string?

Can you provide this type of defintion for the data string that you wish to parse and manipulate?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[amal1973]

TonyJollans --It worked perfectly – thanks a million


CajunCenturion—

This description comes from a mainframe system like that, it does not change., what might change is the Characters and numbers Ex.. ACCR could be ABBT or ACT.
The date would come 9/30 and RECR BNS are two words with a space and the numbers at the end are always 8 characters