extract values from a string, urgent...

[Guest_imported]

Hi,


I have a large string(a field with string length250), where we must "cut" the position. I think with the mid function but how??. I shall explain the situation. I have three fields. In the first field we can find the ID numbers of the tables, the second field is the name of the table, the third field is a large string with the numbers 0,1,2,3 and 4. In this string is the information about right(permissions) on tables the 0 = nothing, 1=create, 2=read, 3=update 4=delete. The position of this values are the table ID's. For example 43243332, the first 4 is table ID=1(Customer), the second 3 is table ID=2 (Supplier), the third 2 is table ID = 3(Articles). I must only extract(From this string) the 1,2,3 and 4. The result must customer = 4(delete), supplier = 3(update) and articles 2(read).

Example:
{Field1} {Field2} {Field3}
1 Customer 44444443323444444444433332222111
2 Supplier 43444444444444444444333333332222
3 Articles 41222222222222244444444221222334
... .... ...
... .... ...

must be

{Field1} {Field2} {Field3}
1 Customer Delete
2 Supplier Update
3 Articles Read
... .... ...
... .... ...

A short explain is that i must check to the field1(table ID) and then take the value of the string from the position of Table ID, Thus when field1 is 5 i must take the 5th position of the string (values 1,2,3 or 4).
How can i do this, could somebody help me plss, I hope someone could find an answer this problem.

Sincerely,

Thomas

 

[Naith]

Just to be sure what it is that you're trying to achieve;

If {field1} = N you want to take the Nth character in the string of {field3}. Please put me back on track if I've misunderstood you.

You mentioned that you only want to take values 1 through 4, but that the string can contain numbers 0 through 4. What do you expect to happen if the Nth character in the string is 0?

Naith

 

[kenhamady]

Try:

if {Field3} [ {Field1} ] = 0 then "Nothing" else
if {Field3} [ {Field1} ] = 1 then "Create" else
if {Field3} [ {Field1} ] = 2 then ...

What doesn't make sense to me is why each record's premission string would be different, if it gives the permissions of all tables. Is it the same in real life? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

Yes you have understand me very well. But sorry sorry sorry i have made a mistake. The large string (Field3) must start with position 0. 44332 = The first 4 is position 0 the second 4 is position 1 the 3 is position 3 etc. It can be possible that field 1 contains a value 0(table ID = 0).

I hope u can help...

 

[Guest_imported]

Yes you have understand me very well. But sorry sorry sorry i have made a mistake. The large string (Field3) must start with position 0. 44332 = The first 4 is position 0 the second 4 is position 1 the 3 is position 3 etc. It can be possible that field 1 contains a value 0(table ID = 0).

I hope u can help...

 

[Naith]

Okay, so I think I now understand you to mean that if {field1} = N then you want the Nth+1 character from {field3} returned - bearing in mind that the first character in the string is position 0.

You can use this formula, assuming that {field3} is a true string, and not a numerical field.

if mid({field3},{field1}+1,1) = '4' then "Delete"
else
if mid({field3},{field1}+1,1) = '3' then "Update"
else
etc

Naith

 

[Guest_imported]

Thanks a lot. Ken, i know you are one of the masters of this forum but i think you have misunderstood me, i think i couldn't explain it very well. The formule you give, gives a the following error: A string is required here, field1 is a number field and field3 is a string. Naith has understand me very well just like he says: If {field1} = N you want to take the Nth character in the string of {field3}.

I have tried this function but i get an error message as following: Mid({Field3}, {Field1})

start position is less than 1 or not an integer(because table ID is beginning with a 0)

I hope you can understand me

 

[Naith]

Put in a condition to catch 0 table ids.

i.e.

If {field1} = 0 then 'whatever'
else
<insert the formula I just gave you here>

 

[kenhamady]

Sorry, I forgot the quotes on the strings. I will now assume that you want &quot;nothing&quot; for table 0 - since there is no position for zero.

Try this:

if {Field3} = 0 then &quot;Nothing&quot; else
if {Field3} [ {Field1} ] = '0' then &quot;Nothing&quot; else
if {Field3} [ {Field1} ] = '1' then &quot;Create&quot; else
if {Field3} [ {Field1} ] = '2' then ...
However, if you mean that tableID 0 is really position 1 in the string (ie everything is off by one) then use:

if {Field3} [ {Field1}+1 ] = '0' then &quot;Nothing&quot; else
if {Field3} [ {Field1}+1 ] = '1' then &quot;Create&quot; else
if {Field3} [ {Field1}+1 ] = '2' then ... Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

I think i'm on the good way only the tableID=0 will give problems. I get the value before that. I have used naiths formula as follow:

if mid({field3},{field1}+1,1) = '4' then &quot;Delete&quot;
else
if mid({field3},{field1}+1,1) = '3' then &quot;Update&quot;
else
etc

Only when field3 is as follow 4444144, i will get next results

tableID=0 = nothing
tableID=1 = nothing
tableID=2 = nothing
tableID=3 = create
tableID=4 = nothing
tableID=5 = nothing
tableID=6 = nothing

The create is by tableID=3 but i must have it on tableID=4, because this is the fifth position...

Any ideas...

 

[Guest_imported]

I think i'm on the good way only the tableID=0 will give problems. I get the value before that. I have used naiths formula as follow:

if mid({field3},{field1}+1,1) = '4' then &quot;Delete&quot;
else
if mid({field3},{field1}+1,1) = '3' then &quot;Update&quot;
else
etc

Only when field3 is as follow 4444144, i will get next results

tableID=0 = nothing
tableID=1 = nothing
tableID=2 = nothing
tableID=3 = create
tableID=4 = nothing
tableID=5 = nothing
tableID=6 = nothing

The create is by tableID=3 but i must have it on tableID=4, because this is the fifth position...

Any ideas...

 

[Naith]

I'm assuming the value for {field1} in this instance was '4'. (Or was it 0?)

The example you've given seems like it's based on a context of Table Id being 4, but you say that it's when the Table Id = 0 that you get the preceding value.

Is this the only occurence where you are getting this type of behaviour where the Table Id equals whatever it's equalling in this case? Or is it the case that all instances of this Table Id return the preceding value?

I think it would be helpful for me if you copied and pasted both the formulas you are using to derive and display your &quot;tableID=0=...&quot; data.

 

[Guest_imported]

Hi Ken,

Sorry that i disturb you but i have a little question. In the formula you says

if {Field3} [ {Field1}+1 ] = '0' then &quot;Nothing&quot; else
if {Field3} [ {Field1}+1 ] = '1' then &quot;Create&quot; else
if {Field3} [ {Field1}+1 ] = '2' then ...

You didn't use the TRIM function is the [] equal to trim or something could you pls explain it. At the moment i'm not on work, but tomorrow i shall check it...

 

[kenhamady]

That is a subtring. The following returns the 4th position of the field:

{Field} [4]

The number can be calculated or taken from another field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com