Help with IIf statement

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi,

I have a field that can contain a variation of the
following.

M81609TTMVUSP00222
:Z.AC1656AMVP00222

Currently, I'm running 2 seperate queries to capture the
M8 out of the 1st entry and the AC out of the next. Is
there anyway to combine them into one? I attempted with
a IIf statement, but that didn't work so well.

Thanks.

Jason
 
Hi Jason,
I read your post, but not sure what you're looking to
do. Do you want to select rows of data where the given
field has M8 or AC occurring somewhere within it?
Does M8 and AC always show up in the same positions of
that field?
 
Hi,

Sorry if I was not clear.

I'd like to take the M8 and AC and place those into
another field using an update query. The M8 of course
will be different in each row, but the location (1st 2
fields) will remain the same. Same rule will apply with
the AC, those will always be in location 4 and 5.

Hope that helps.

Jason
 
I'm still not exactly sure of all the details, but it
sounds to me like you need to be using an IIF and the
Instr function. Something like the following:

IIF(Instr("M81609TTMVUSP00222","M8"),"M8","AC")


Again, I'm not clear on what you're after, but I have a
general idea. One way other other, I imagine you need a
variation of the above.

Hope that helps some.


Rick


P.S. I could help with the checking for the position of
the characters as well, if needed, but again... just not
sure of all the details.
 
Thanks Rick for the reply.

Here is a better explanation.

On the left is the current data, on the right is what I
need extracted from it. Is there a way to do it in one
shot?

M81609TTMVUSP00222 M8
:Z.AC1656AMVP00222 AC
M71603TTMVUSP00222 M7
:Z.ZC1507AMVP00222 ZC
:Z.MP1654TMVP00222 MP
M71696TTMVUSP00222 M7

Thanks.

Jason
 
So you need the first two characters in the string, unless there is a period in
the string and then you need the two characters after the period? One way is as follows.

IIF(Instr(YourString,".") = 0,
Left(YourString,2),
Mid(YourString,Instr(YourString,".")+1,2)

All on one line.

If your period when present is ALWAYS the third character, then you could test
the third character and make this a bit more efficient.

IIF(MID(YourString,3,1) = ".",
Mid(YourString,4,2),
Left(YourString,2)
 
Thanks Rick for the reply.

Here is a better explanation.

On the left is the current data, on the right is what I
need extracted from it. Is there a way to do it in one
shot?

M81609TTMVUSP00222 M8
:Z.AC1656AMVP00222 AC
M71603TTMVUSP00222 M7
:Z.ZC1507AMVP00222 ZC
:Z.MP1654TMVP00222 MP
M71696TTMVUSP00222 M7

Thanks.

Jason
Click to expand...
** snipped **
The 1st, 3rd, and 6th lines seem all to contain the characters you
want as the very first 2 characters.
Assuming these characters are from a field in your table, then:

NewColumn1:Left([FieldName],2)

The other lines all have the characters after the ":Z." combination
(i.e. the 4th and 5th characters):

NewColumn2:Mid([FieldName],4,2)

Now I have no idea from any of your previous posts whether we're
supposed to know in advance which expression is needed at any
particular time, so adapt the above, using an IIf statement, to return
the correct value into one column:

NewColumn:IIf(Left([FieldName],2) = ":Z."),
Mid([FieldName],4,2),Left([FieldName],2)
 
Thanks Rick for the reply.

Here is a better explanation.

On the left is the current data, on the right is what I
need extracted from it. Is there a way to do it in one
shot?

M81609TTMVUSP00222 M8
:Z.AC1656AMVP00222 AC
M71603TTMVUSP00222 M7
:Z.ZC1507AMVP00222 ZC
:Z.MP1654TMVP00222 MP
M71696TTMVUSP00222 M7

Thanks.

Jason
Click to expand...
** snipped **
The 1st, 3rd, and 6th lines seem all to contain the characters you
want as the very first 2 characters.
Assuming these characters are from a field in your table, then:

NewColumn1:Left([FieldName],2)

The other lines all have the characters after the ":Z." combination
(i.e. the 4th and 5th characters):

NewColumn2:Mid([FieldName],4,2)

Now I have no idea from any of your previous posts whether we're
supposed to know in advance which expression is needed at any
particular time, so adapt the above, using an IIf statement, to return
the correct value into one column:

NewColumn:IIf(Left([FieldName],2) = ":Z."),
Mid([FieldName],4,2),Left([FieldName],2)
Click to expand...

Whoops, I made a small mistook.
It should have read:

NewColumn:IIf(Left([FieldName],3) = ":Z."),
Mid([FieldName],4,2),Left([FieldName],2)
 
Fred,

Thanks for the reply. I needed to make one small
adjustment to your corrected IIf statement. I believe it
should read as follows.

NewColumn:IIf(Left([FieldName],3) = ":Z.",
Mid([FieldName],4,2),Left([FieldName],2))

The ) after the ":Z." was giving me an error message. I
put it and the end of the expression and it returned the
proper result.

Thanks a lot for your assistance.

Jason
-----Original Message-----
Thanks Rick for the reply.

Here is a better explanation.

On the left is the current data, on the right is what I
need extracted from it. Is there a way to do it in one
shot?

M81609TTMVUSP00222 M8
:Z.AC1656AMVP00222 AC
M71603TTMVUSP00222 M7
:Z.ZC1507AMVP00222 ZC
:Z.MP1654TMVP00222 MP
M71696TTMVUSP00222 M7

Thanks.

Jason
Click to expand...
** snipped **
The 1st, 3rd, and 6th lines seem all to contain the characters you
want as the very first 2 characters.
Assuming these characters are from a field in your table, then:

NewColumn1:Left([FieldName],2)

The other lines all have the characters after the ":Z." combination
(i.e. the 4th and 5th characters):

NewColumn2:Mid([FieldName],4,2)

Now I have no idea from any of your previous posts whether we're
supposed to know in advance which expression is needed at any
particular time, so adapt the above, using an IIf statement, to return
the correct value into one column:

NewColumn:IIf(Left([FieldName],2) = ":Z."),
Mid([FieldName],4,2),Left([FieldName],2)
Click to expand...

Whoops, I made a small mistook.
It should have read:

NewColumn:IIf(Left([FieldName],3) = ":Z."),
Mid([FieldName],4,2),Left([FieldName],2)

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
John,

Both of these expressions worked fine. I just needed to
add a closing ) to the 1st one.

I actually like the 1st one better since the period may
not be present in the 3rd character.

Thanks a lot for your help.

Jason

-----Original Message-----
So you need the first two characters in the string, unless there is a period in
the string and then you need the two characters after
Click to expand...
the period? One way is as follows.
 
Back
Top