extracting data from strings (1 Viewer)

[dolano]

I would like to extract a particular part of a string and do a calculation on it. Example would be as follows.

Field = Code
Value = LV-3P004sq
So if there is a P in the value it will = 3 and this shall be multiplied by the preceeding number (in this case 3*P = 9) the result is then placed in another field(s) called TTR and TFR. I was looking into attempting this using DAO

Thank you for your time in advance

Pauli

 

[Steve R.]

I am not an expert, but this appears to be more of a VBA question. I would suggest looking up the "Functions Reference" under Accesses VBA help. Not the Access, Access help.

Lookup the following functions: LEN, MID, VAL
what you would be doing is counting the total number of characters in the string, then parsing them one by one to evaluate what you want to do. I do not know the details of what you are trying to do, so I am just providing a skeleton.

dim i as integer
dim j as integer
dim str1 as string
j=0
i=1
j=len([textbox])
do while i < j
str1=mid([textbox],1,i)

********Enter Decsion Code Here ***********
if str1 = "P" then somevalue =3
[textbox2] =somevalue * anothervalue

i=i+1
loop

 

[dolano]

shudda replyed sooner

Thanks for the advice, I overcame the problem using a combination of DAO, InStr and Mid functions. You can see it in the code repository forum above if you wish.