InStr function

[Pete Provencher]

Using Access 2000:

I'm trying to convert some business owner names [owner] from one format to
another:

Smith, John A to John A Smith

I use the follwing to accomplish this task:

Owner1: Mid([owner],InStr([owner],",")+1) & " " &
Left([owner],InStr([owner],",")-1)


This works well except when I run into this problem:


CompUSA error

I need to write an iif statement that will use what's in the field if there
are no commas.

I tried this and it doesn't work:

Owner1: IIF(InStr([owner], ",")=0,[owner], Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)

Can anyone help with tis problem. It sure would be appreciated.


Pete Provencher

 

[Ken Snell [MVP]]

Define "doesn't work" -- what happens when you try your expression?

 

[Pete Provencher]

If give you a parameter box that shows a quotation mark as the paramenter. I
click on ok and it runs the query and puts error in the field.

Pete Provencher

Define "doesn't work" -- what happens when you try your expression?

--

Ken Snell
<MS ACCESS MVP>

Using Access 2000:

I'm trying to convert some business owner names [owner] from one format to
another:

Smith, John A to John A Smith

I use the follwing to accomplish this task:

Owner1: Mid([owner],InStr([owner],",")+1) & " " &
Left([owner],InStr([owner],",")-1)


This works well except when I run into this problem:


CompUSA error

I need to write an iif statement that will use what's in the field if
there
are no commas.

I tried this and it doesn't work:

Owner1: IIF(InStr([owner], ",")=0,[owner],
Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)

Can anyone help with tis problem. It sure would be appreciated.


Pete Provencher

 

[Guest]

Hi Pete,

The iif method still gave an error because Access evaluates all parts of the
expression, even if the logic doesn't reach that point, so the false part of
the expression still generated an error.

You may be able to use the switch function instead to provide the desired
starting point for the mid() function based on the instr() results, such as:

Owner1: Trim(Mid([owner],Switch(InStr([owner],",")>0,InStr([owner],",")+1,
InStr([owner],",")=0,1)) & " " &
Left([owner],Switch(InStr([owner],",")>0,InStr([owner],",")-1,
InStr([owner],",")=0,0)))

This isn't tested, so I'm sure there may be a typo or two, but hopefully
you'll get the idea.

-Ted Allen

 

[Guest]

Ken, correct me if I'm wrong, but I think that the error is the result of the
fact that the left funtion:

Left([owner],InStr([owner],",")-1), will reduce to:

Left([owner],-1)

in cases where the comma is not found because Instr() will return a 0.

-Ted Allen

If give you a parameter box that shows a quotation mark as the paramenter. I
click on ok and it runs the query and puts error in the field.

Pete Provencher

Define "doesn't work" -- what happens when you try your expression?

--

Ken Snell
<MS ACCESS MVP>

Using Access 2000:

I'm trying to convert some business owner names [owner] from one format to
another:

Smith, John A to John A Smith

I use the follwing to accomplish this task:

Owner1: Mid([owner],InStr([owner],",")+1) & " " &
Left([owner],InStr([owner],",")-1)


This works well except when I run into this problem:


CompUSA error

I need to write an iif statement that will use what's in the field if
there
are no commas.

I tried this and it doesn't work:

Owner1: IIF(InStr([owner], ",")=0,[owner],
Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)

Can anyone help with tis problem. It sure would be appreciated.


Pete Provencher

 

[Pete Provencher]

It worked perfectly. I'll have to read up on switch. Thanks to everyone.

Pete Provencher

Hi Pete,

The iif method still gave an error because Access evaluates all parts of the
expression, even if the logic doesn't reach that point, so the false part of
the expression still generated an error.

You may be able to use the switch function instead to provide the desired
starting point for the mid() function based on the instr() results, such as:

Owner1: Trim(Mid([owner],Switch(InStr([owner],",")>0,InStr([owner],",")+1,
InStr([owner],",")=0,1)) & " " &
Left([owner],Switch(InStr([owner],",")>0,InStr([owner],",")-1,
InStr([owner],",")=0,0)))

This isn't tested, so I'm sure there may be a typo or two, but hopefully
you'll get the idea.

-Ted Allen

Using Access 2000:

I'm trying to convert some business owner names [owner] from one format to
another:

Smith, John A to John A Smith

I use the follwing to accomplish this task:

Owner1: Mid([owner],InStr([owner],",")+1) & " " &
Left([owner],InStr([owner],",")-1)


This works well except when I run into this problem:


CompUSA error

I need to write an iif statement that will use what's in the field if there
are no commas.

I tried this and it doesn't work:

Owner1: IIF(InStr([owner], ",")=0,[owner], Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)

Can anyone help with tis problem. It sure would be appreciated.


Pete Provencher

 

[Ken Snell [MVP]]

Hi Pete,

The iif method still gave an error because Access evaluates all parts of
the
expression, even if the logic doesn't reach that point, so the false part
of
the expression still generated an error.

Ted - Jet does not do this. Jet (in a query, the control source of a form's
or report's control, etc.) evaluates the test, then only the "choice" based
on the results of that Test. This is different from VBA, which does exactly
as you note above.

 

[Ken Snell [MVP]]

Not in a query, Ted. Jet will not evaluate the "False" argument of the
expression if the IIf test results in a True value.

Is it possible for the owner field to contain a Null value? That may cause
an error. Try this expression in your query:

Owner1: IIF(InStr(Nz([owner],""), ",")=0,[owner],
Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)
--

Ken Snell
<MS ACCESS MVP>

Ken, correct me if I'm wrong, but I think that the error is the result of
the
fact that the left funtion:

Left([owner],InStr([owner],",")-1), will reduce to:

Left([owner],-1)

in cases where the comma is not found because Instr() will return a 0.

-Ted Allen

If give you a parameter box that shows a quotation mark as the
paramenter. I
click on ok and it runs the query and puts error in the field.

Pete Provencher

Define "doesn't work" -- what happens when you try your expression?

--

Ken Snell
<MS ACCESS MVP>

Using Access 2000:

I'm trying to convert some business owner names [owner] from one
format to
another:

Smith, John A to John A Smith

I use the follwing to accomplish this task:

Owner1: Mid([owner],InStr([owner],",")+1) & " " &
Left([owner],InStr([owner],",")-1)


This works well except when I run into this problem:


CompUSA error

I need to write an iif statement that will use what's in the field if
there
are no commas.

I tried this and it doesn't work:

Owner1: IIF(InStr([owner], ",")=0,[owner],
Mid([owner],InStr([owner],",")+1)
& " " & Left([owner],InStr([owner],",")-1)

Can anyone help with tis problem. It sure would be appreciated.


Pete Provencher

 

[Guest]

Good to know, thanks Ken. Sorry for the misinformation Pete. I'm not sure
what the source of the error was then, but at least the switch did work for
you.