#Error with Instr NEED help!!!!

  • Thread starter Thread starter matthewwhaley
  • Start date Start date
M

matthewwhaley

Allright, this is a beast, but here is what I am trying to do

Create a seperate field for each Desc seperated by a "_". Ex (Main
Field([tblDrivers]![driverName]:

EQ_PRICE_VAR_USD_MFA_SPT ------ would have 4 fields
(EQ_PRICE,EQ_PRICE_USD, EQ_PRICE_USD_MFA)

EQ_VOL_VAR_USD_APOL.O_IMPM_SPTVOL_1.0_M03 would have 7 fields
(EQ_VOL,EQ_VOL_USD, EQ_VOL_USD_APOL.O., EQ_VOL_USD_APOL.O._IMPM, etc)


All fields have "_VAR_" at the same spot. I eliminated that in part of
the query below. My problem is that when I get to the 5th "_" say in
Ex 1, I begin to get #Error. IIF(Iserror) is not helping.

Any thoughts.

Here is the first instance I get an error and how I tried to do it in
the ZOOM box. I will post the sql if someone needs it, but I figure
this iserror has to work sometime/somehow


First 5 Desc: IIf(IsError(Left(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')
+9),'_')-1))),Left([tblDrivers]![driverName],InStr(InStr([tblDrivers]!
[driverName],'_')+1,[tblDrivers]![driverName],'_')) &
Right([tblDrivers]![driverName],Len([tblDrivers]![driverName])-
InStr([tblDrivers]![driverName],'_VAR_')-4),(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),'_')-1)))
 
Not sure I'm understanding --

You have a variable-length string that has some text to the left of "_VAR"
text fragment that you want to use independently and to concatenate to the
front of each text fragment after a _ character that occurs after the "_VAR"
text fragment? Is this correct?

Are you wanting to do this in a query? Or on a form or report? What will you
want to do with the parsed/built names from the original text string?
 
Not sure I'm understanding --

You have a variable-length string that has some text to the left of "_VAR"
text fragment that you want to use independently and to concatenate to the
front of each text fragment after a _ character that occurs after the "_VAR"
text fragment? Is this correct?

Are you wanting to do this in a query? Or on a form or report? What will you
want to do with the parsed/built names from the original text string?

--

Ken Snell
<MS ACCESS MVP>




Allright, this is a beast, but here is what I am trying to do
Click to expand...
Create a seperate field for each Desc seperated by a "_". Ex (Main
Field([tblDrivers]![driverName]:
Click to expand...
EQ_PRICE_VAR_USD_MFA_SPT ------ would have 4 fields
(EQ_PRICE,EQ_PRICE_USD, EQ_PRICE_USD_MFA)
Click to expand...
EQ_VOL_VAR_USD_APOL.O_IMPM_SPTVOL_1.0_M03 would have 7 fields
(EQ_VOL,EQ_VOL_USD, EQ_VOL_USD_APOL.O., EQ_VOL_USD_APOL.O._IMPM, etc)
Click to expand...
All fields have "_VAR_" at the same spot. I eliminated that in part of
the query below. My problem is that when I get to the 5th "_" say in
Ex 1, I begin to get #Error. IIF(Iserror) is not helping.
Click to expand...
Any thoughts.
Click to expand...
Here is the first instance I get an error and how I tried to do it in
the ZOOM box. I will post the sql if someone needs it, but I figure
this iserror has to work sometime/somehow
Click to expand...
First 5 Desc: IIf(IsError(Left(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')
+9),'_')-1))),Left([tblDrivers]![driverName],InStr(InStr([tblDrivers]!
[driverName],'_')+1,[tblDrivers]![driverName],'_')) &
Right([tblDrivers]![driverName],Len([tblDrivers]![driverName])-
InStr([tblDrivers]![driverName],'_VAR_')-4),(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),'_')-1)))- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Yes. I want to create new fields out of parsed and concatenated field
1. The problem is that every field is between the "_" EX:

{data}-->EQ_PRICE_VAR_USD_MFA_SPT --
([field1]=EQ_PRICE_VAR_USD_MFA_SPT, [field2]=EQ_PRICE_USD_MFA_SPT,
Click to expand...
[field3]=EQ_PRICE, [field4]=EQ_PRICE_USD, [field5]=EQ_PRICE_USD_MFA).

I parse on the "_", but each record doesnot have the same # of "_".
One record may have 5(above), others can have up to 8. The problem is
that the above returns an #error as I parse the 5th "_" instance, I
want it to return [field1](long name). Other records would yield new
data

Does that help? I guess that iseeror works nothing like Excel.
Correct??

Thanks again for your help
 
Sorry for another question, but when you say you want to create a new field
by parsing out the string from the current field's value, do you mean you
want to create a new value for a field from the parsing, or do you really
want to create a new field in a table that is named the same as the parsed
string value?

--

Ken Snell
<MS ACCESS MVP>

Not sure I'm understanding --

You have a variable-length string that has some text to the left of
"_VAR"
text fragment that you want to use independently and to concatenate to
the
front of each text fragment after a _ character that occurs after the
"_VAR"
text fragment? Is this correct?

Are you wanting to do this in a query? Or on a form or report? What will
you
want to do with the parsed/built names from the original text string?

--

Ken Snell
<MS ACCESS MVP>




Allright, this is a beast, but here is what I am trying to do
Click to expand...
Create a seperate field for each Desc seperated by a "_". Ex (Main
Field([tblDrivers]![driverName]:
Click to expand...
EQ_PRICE_VAR_USD_MFA_SPT ------ would have 4 fields
(EQ_PRICE,EQ_PRICE_USD, EQ_PRICE_USD_MFA)
Click to expand...
EQ_VOL_VAR_USD_APOL.O_IMPM_SPTVOL_1.0_M03 would have 7 fields
(EQ_VOL,EQ_VOL_USD, EQ_VOL_USD_APOL.O., EQ_VOL_USD_APOL.O._IMPM, etc)
Click to expand...
All fields have "_VAR_" at the same spot. I eliminated that in part of
the query below. My problem is that when I get to the 5th "_" say in
Ex 1, I begin to get #Error. IIF(Iserror) is not helping.
Click to expand...
Any thoughts.
Click to expand...
Here is the first instance I get an error and how I tried to do it in
the ZOOM box. I will post the sql if someone needs it, but I figure
this iserror has to work sometime/somehow
Click to expand...
First 5 Desc: IIf(IsError(Left(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')
+9),'_')-1))),Left([tblDrivers]![driverName],InStr(InStr([tblDrivers]!
[driverName],'_')+1,[tblDrivers]![driverName],'_')) &
Right([tblDrivers]![driverName],Len([tblDrivers]![driverName])-
InStr([tblDrivers]![driverName],'_VAR_')-4),(Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),
(InStr(InStr(Mid([tblDrivers]![driverName],InStr([tblDrivers]!
[driverName],'_VAR_')+9),'_')+1,Mid([tblDrivers]!
[driverName],InStr([tblDrivers]![driverName],'_VAR_')+9),'_')-1)))-
Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Yes. I want to create new fields out of parsed and concatenated field
1. The problem is that every field is between the "_" EX:

{data}-->EQ_PRICE_VAR_USD_MFA_SPT --
([field1]=EQ_PRICE_VAR_USD_MFA_SPT, [field2]=EQ_PRICE_USD_MFA_SPT,
Click to expand...
[field3]=EQ_PRICE, [field4]=EQ_PRICE_USD, [field5]=EQ_PRICE_USD_MFA).

I parse on the "_", but each record doesnot have the same # of "_".
One record may have 5(above), others can have up to 8. The problem is
that the above returns an #error as I parse the 5th "_" instance, I
want it to return [field1](long name). Other records would yield new
data

Does that help? I guess that iseeror works nothing like Excel.
Correct??

Thanks again for your help
Click to expand...
 
Back
Top