Parsing a Field...

[imtheodore]

If I have a value in a table of:

Smith - 8 - 2323 - b3234

Is it possible for me to display the "8" in a separate part of the form
I cannot use the "Right" or "Mid" function since the "8" could be "10" or "7-5" etc.. It is not a fixed length. Is it possible to look for a delimiter to display a value on a form? (I can use the "-" or add any delimiter I need)

I will be listing the entire value in one field "fielda"
But I want to list the "8" separately in another field "Fieldb"

And again I cannot use =mid([fielda],15,1) since the value is not a fixed length.

Thanks,
Dave

 

[gemma-the-husky]

can you explain how the value you want extracting is to be found

eg is it the number after the first hyphen

unless you can specifiy where it is located you wont be able to do this

 

[Mike375]

So Smith - 8 - 2323 - b3234 can be

Smith - 8 - 2323 - b3234
Smith - 10 - 2323 - b3234
Smith - 158 - 2323 - b3234

Smith - 8-19 - 2323 - b3234
Smith - 8-1 - 2323 - b3234

and either side of the bold is the same except of course for the Smith and it is the bold section that needs to be extracted?

Is the section 2323 - b3234 always the same or can it be 27323 - b32934 or 27323 - z32934 etc.

 

[gemma-the-husky]

never mind - this function will extract the number after the first hyphen

function extract(thestrg As String) as double

Dim pos As Long

    pos = InStr(thestrg, "-")

    extract = Val(Mid(thestrg, pos + 1))

End function

 

[MStef]

Hello Dave!

Look at "DemoExtract8A2000.mdb"
I think it is what you need.
Look at Query1, form1,
Open form1.

 

[Mike375]

Look at "DemoExtract8A2000.mdb"
I think it is what you need.
Look at Query1, form1,
Open form1.

That would need to be changed for

Smith - 158 - 2323 - b3234

and

Smith - 15-8 - 2323 - b3234

 

[Mike375]

Gemma

Just tried your and it gets

Smith - 8 - 2323 - b3234 = 8
Smith - 78 - 2323 - b3234 = 78

But I think he also wants

Smith - 8-9 - 2323 - b3234

 

[imtheodore]

Ok, sorry for the confusion. Lot's of good ideaas posted though.

The field consists of a name - Hours - Pager - userid

I need the hours. The length of the name or the hours length will change. If I can use a delimiter to get what I want, I can set up my file to be:

name * Hours * Pager * userid

Then my -'s in the hours would not mess up the delimiter.

Example:
My new field will look like this:
smith * 7-5 * 2323 * b3234

I suppose I can use this code then:
function extract(thestrg As String) as double

Dim pos As Long

pos = InStr(thestrg, "*")

extract = Val(Mid(thestrg, pos + 1))

End function

But will this still give me 7-5 for the example posted above?

 

[gemma-the-husky]

i thought he said he wanted to extract the 8 - my code takes the first numerical value after the first hyphen. but reading it again its a bit less clear

but if there is no rigid structure involved he is going to struggle with this. particularly since 8-9 is not a number.

can we hear from the poster please?

perhaps he wants to get the string between the first and second separtors

in which case

function extractstring(thestrg As String) as string

Dim pos1 As Long
Dim pos2 As Long

    pos1=0
    pos2=0

    pos1 = InStr(thestrg, "-")
    if pos1>0 then
        pos2 = InStr(pos1+1,thestrg, "-")
    end if

    if pos2>0 then
        extract = trim(Mid(thestrg, pos1 + 1, pos2-pos1-1)
    end if
End function

 

[imtheodore]

i thought he said he wanted to extract the 8 - my code takes the first numerical value after the first hyphen. but reading it again its a bit less clear

but if there is no rigid structure involved he is going to struggle with this. particularly since 8-9 is not a number.

can we hear from the poster please?

perhaps he wants to get the string between the first and second separtors

in which case

function extractstring(thestrg As String) as string

Dim pos1 As Long
Dim pos2 As Long

    pos1=0
    pos2=0

    pos1 = InStr(thestrg, "-")
    if pos1>0 then
        pos2 = InStr(pos1+1,thestrg, "-")
    end if

    if pos2>1 then
        extract = trim(Mid(thestrg, pos1 + 1, pos2-pos1-1)
    end if
End function

I believe if I change my delimiters this code may work??? Am I correct?

 

[KenHigg]

If there is this much trouble at this point chances are good there will be more issues... I'd say the best bet is for the user to post a sizable portion of sample data to look at...

 

[Brianwarnock]

Gemma's solution should work with the changed delimiters.

Brian

Edit I think the length should be pos2-pos1-3

 

[KenHigg]

Provided the user is not allowed to put and asterisk in any of the fields

 

[gemma-the-husky]

brian,

it was off the top of my head, but i think its pos2-pos-1

the length of the extracted string

ie between markers at 6 and 10, you want 3chars

so 10-6-1

.... and then trim leading and trailing spaces

 

[imtheodore]

Provided the user is not allowed to put and asterisk in any of the fields

The user must choose from a list of values, so I will be able to control that scenerio. I could probably just change the "-" to a "to" and resolve it that way.

 

[Brianwarnock]

.... and then trim leading and trailing spaces

It wasn't a criticism, he can tweak it himself,and i wasn't quite right as it should be pos1+2,pos2-pos1-3 to remove leading and trailing spaces.

brian

 

[KenHigg]

The user must choose from a list of values, so I will be able to control that scenerio. I could probably just change the "-" to a "to" and resolve it that way.

Oh. Then husky's solution worked?

 

[gemma-the-husky]

the trim function removes leading and trailing spaces, without having to explicitly assume a certain number

THEODORE

if you change the delimiter from - to "to", the code will not work, as it assumes the delimiter is a single character. Use a tilde, or hash character perhaps

 

[KenHigg]

I'm still a bit confused... If you have the abilty to control the delimeter seems your situation is pretty flexible. Why not just attach to the source (the .csv) and have it all in seperate fields to start with?

 

[imtheodore]

I'm still a bit confused... If you have the abilty to control the delimeter seems your situation is pretty flexible. Why not just attach to the source (the .csv) and have it all in seperate fields to start with?

Unfortunately I am pulling the data from a horrible SQL db where the data is stored by employee, I need it by day. I had to run an obnoxious stored procedure just to make teh dat almost useable, which is where I am stuck. Sort of confusing, but if anyone wants to take a stab at it I'll post an example...

Give me a few minutes to put it together