Seperating a variable length string

[Brian]

I'm attempting to seperate out 2 sides of a text string, representing an
exact foot and frame for a roll of film. The size of the string is variable,
but most of the time seperated by a + sign. All of the data except for the +
are numbers and I need to pull them out to multiply the left of the string by
either 16 or 40, depending on the film type, and then add the right side of
the string to that result.

The strings can be up to 4 characters on the left and 2 characters on the
right. 2356+28 is a typical number in the field. 94268 would be the total
frame count which is what I'm trying to find.

Any help would be greatly appriciated.

 

[Rob Oldfield]

Something similar but with a different calculation as you don't say how you
decide between multiplying by 16 or 40. This just grabs the two numbers and
multiplies them together. You'd need to add something to check where the
input string is not in the correct format e.g. where there's no + in there
and the instr function returns 0


function framecount(str as string) as long
dim pos as integer
pos=instr(str,"+")
framecount=left(str,pos-1)*mid(str,pos+1)
end function

 

[Brian]

Here is the statement that I've got that determines if something is
multiplied by 16 or 40
IIf(Mid([element],1,2)="35",16*[Footage Count In],40*[Footage Count In]))

How would I intergrate what you wrote into this statement.

Thanks for the help

 

[Rob Oldfield]

Assuming that you have a query with the field 'element' with the 35s, and
another field called 'whatever' that contains the string containing the +,
then you could just add a calculated field onto that query:
framecount([element],[whatever]) where the function would be...

function framecount(el, as string, str as string) as long
dim pos as integer
pos=instr(str,"+")
if mid(el,1,2)="35" then
framecount=16*left(str,pos-1)+mid(str,pos+1)
else
framecount=40*left(str,pos-1)+mid(str,pos+1)
endif
end function

Here is the statement that I've got that determines if something is
multiplied by 16 or 40
IIf(Mid([element],1,2)="35",16*[Footage Count In],40*[Footage Count In]))

How would I intergrate what you wrote into this statement.

Thanks for the help

Something similar but with a different calculation as you don't say how you
decide between multiplying by 16 or 40. This just grabs the two numbers and
multiplies them together. You'd need to add something to check where the
input string is not in the correct format e.g. where there's no + in there
and the instr function returns 0


function framecount(str as string) as long
dim pos as integer
pos=instr(str,"+")
framecount=left(str,pos-1)*mid(str,pos+1)
end function


for
the + string
by side
of

 

[Brian]

Thanks for all your help so far and I think I've got just one more question.
All of the code works great, but I'm having trouble with the calculated field.
What I want it to do is run "framecount" when [footage count in] has the +
present in the expression and to run [framecountnormal] when it doesn't.
What is happening is only the false result is coming up. I'm not sure why
that is, but this is the last thing I think I need to make this work.

=IIf(IsNull([Footage Count In]),"<---",IIf(Mid([footage count
in],4,1)="+",framecount([from element],[footage count
in]),framecountnormal([from element],[footage count in])))

Thanks again for any help.

Assuming that you have a query with the field 'element' with the 35s, and
another field called 'whatever' that contains the string containing the +,
then you could just add a calculated field onto that query:
framecount([element],[whatever]) where the function would be...

function framecount(el, as string, str as string) as long
dim pos as integer
pos=instr(str,"+")
if mid(el,1,2)="35" then
framecount=16*left(str,pos-1)+mid(str,pos+1)
else
framecount=40*left(str,pos-1)+mid(str,pos+1)
endif
end function

Here is the statement that I've got that determines if something is
multiplied by 16 or 40
IIf(Mid([element],1,2)="35",16*[Footage Count In],40*[Footage Count In]))

How would I intergrate what you wrote into this statement.

Thanks for the help

Something similar but with a different calculation as you don't say how you
decide between multiplying by 16 or 40. This just grabs the two numbers and
multiplies them together. You'd need to add something to check where the
input string is not in the correct format e.g. where there's no + in there
and the instr function returns 0


function framecount(str as string) as long
dim pos as integer
pos=instr(str,"+")
framecount=left(str,pos-1)*mid(str,pos+1)
end function


I'm attempting to seperate out 2 sides of a text string, representing an
exact foot and frame for a roll of film. The size of the string is
variable,
but most of the time seperated by a + sign. All of the data except for
the +
are numbers and I need to pull them out to multiply the left of the string
by
either 16 or 40, depending on the film type, and then add the right side
of
the string to that result.

The strings can be up to 4 characters on the left and 2 characters on the
right. 2356+28 is a typical number in the field. 94268 would be the
total
frame count which is what I'm trying to find.

Any help would be greatly appriciated.

 

[Rob Oldfield]

I can't say exactly why your method isn't working without seeing your
data... but...using IIfs in query expressions is, generally speaking, to be
avoided. You'd be better off rolling it all in to a single function along
the lines of...

'using fci as a shortening of footage count in
function framecount(el, as string, fci as string) as long
dim pos as integer
if isnull(fci) then
framecount="<---"
else
if mid(fci,4,1)="+" then
'do calculations for framecountnormal
else
pos=instr(str,"+")
if mid(el,1,2)="35" then
framecount=16*left(str,pos-1)+mid(str,pos+1)
else
framecount=40*left(str,pos-1)+mid(str,pos+1)
endif
endif
endif
end function

One of the benefits of doing it this way is that you can drop a breakpoint
in at the start of the routine, go the immediate window, type
?framecount("whatever","whichever")
and then follow the logic through to see where it goes wrong.

Thanks for all your help so far and I think I've got just one more question.
All of the code works great, but I'm having trouble with the calculated field.
What I want it to do is run "framecount" when [footage count in] has the +
present in the expression and to run [framecountnormal] when it doesn't.
What is happening is only the false result is coming up. I'm not sure why
that is, but this is the last thing I think I need to make this work.

=IIf(IsNull([Footage Count In]),"<---",IIf(Mid([footage count
in],4,1)="+",framecount([from element],[footage count
in]),framecountnormal([from element],[footage count in])))

Thanks again for any help.

Assuming that you have a query with the field 'element' with the 35s, and
another field called 'whatever' that contains the string containing the +,
then you could just add a calculated field onto that query:
framecount([element],[whatever]) where the function would be...

function framecount(el, as string, str as string) as long
dim pos as integer
pos=instr(str,"+")
if mid(el,1,2)="35" then
framecount=16*left(str,pos-1)+mid(str,pos+1)
else
framecount=40*left(str,pos-1)+mid(str,pos+1)
endif
end function

Here is the statement that I've got that determines if something is
multiplied by 16 or 40
IIf(Mid([element],1,2)="35",16*[Footage Count In],40*[Footage Count In]))

How would I intergrate what you wrote into this statement.

Thanks for the help
:


Something similar but with a different calculation as you don't say

how
you

decide between multiplying by 16 or 40. This just grabs the two

numbers
and

multiplies them together. You'd need to add something to check

where
the

input string is not in the correct format e.g. where there's no + in there
and the instr function returns 0


function framecount(str as string) as long
dim pos as integer
pos=instr(str,"+")
framecount=left(str,pos-1)*mid(str,pos+1)
end function


I'm attempting to seperate out 2 sides of a text string,

representing
an

exact foot and frame for a roll of film. The size of the string is
variable,
but most of the time seperated by a + sign. All of the data

except
for

the +
are numbers and I need to pull them out to multiply the left of

the
string

by
either 16 or 40, depending on the film type, and then add the

right
side

of
the string to that result.

The strings can be up to 4 characters on the left and 2 characters

on
the

right. 2356+28 is a typical number in the field. 94268 would be the
total
frame count which is what I'm trying to find.

Any help would be greatly appriciated.