Small syntactical error

Hi, I can't work this out, as it seems to work in the vba immediate window
but not in a query.

The following gives an invalid procedure call error:

select *
from addresses
where
address4 = left(address5,instr(address5,",")-1)

but if I change the last line to just

address4 = left(address5,5)

it works as I'd expect, but I of course want the functionality of using the
instr method (which I've used before and got working...)

Any ideas?
Cheers,
Chris

re: Small syntactical error

Are address4 and address5 both fields in the table "addresses", or is one of
them a control on a form?

Larry Linson
Microsoft Access MVP

"C L Humphreys" <clhumphreys@toofgib.moc> wrote in message
news:bn11gp$rdu$1@ucsnew1.ncl.ac.uk...[color=blue]
> Hi, I can't work this out, as it seems to work in the vba immediate window
> but not in a query.
>
> The following gives an invalid procedure call error:
>
> select *
> from addresses
> where
> address4 = left(address5,instr(address5,",")-1)
>
> but if I change the last line to just
>
> address4 = left(address5,5)
>
> it works as I'd expect, but I of course want the functionality of using[/color]
the[color=blue]
> instr method (which I've used before and got working...)
>
> Any ideas?
> Cheers,
> Chris
>
>
>[/color]

re: Small syntactical error

"Larry Linson" <bouncer@localhost.not> wrote in message
news:t3Zkb.10586$Y1.107@nwrddc03.gnilink.net...[color=blue]
> "C L Humphreys" <clhumphreys@toofgib.moc> wrote in message
> news:bn11gp$rdu$1@ucsnew1.ncl.ac.uk...[color=green]
> > The following gives an invalid procedure call error:
> >
> > select *
> > from addresses
> > where
> > address4 = left(address5,instr(address5,",")-1)
> >
> > but if I change the last line to just
> >
> > address4 = left(address5,5)
> >
> > it works as I'd expect...[/color][/color]
[color=blue]
> Are address4 and address5 both fields in the table "addresses", or is one[/color]
of[color=blue]
> them a control on a form?[/color]

They're both fields of the table. I'm not using any forms here.

Cheers,
Chris

re: Small syntactical error

"C L Humphreys" <clhumphreys@toofgib.moc> wrote in message
news:bn2prt$jqb$1@ucsnew1.ncl.ac.uk...[color=blue]
> "Larry Linson" <bouncer@localhost.not> wrote in message
> news:t3Zkb.10586$Y1.107@nwrddc03.gnilink.net...[color=green]
> > "C L Humphreys" <clhumphreys@toofgib.moc> wrote in message
> > news:bn11gp$rdu$1@ucsnew1.ncl.ac.uk...[color=darkred]
> > > The following gives an invalid procedure call error:
> > >
> > > select *
> > > from addresses
> > > where
> > > address4 = left(address5,instr(address5,",")-1)
> > >
> > > but if I change the last line to just
> > >
> > > address4 = left(address5,5)
> > >
> > > it works as I'd expect...[/color][/color]
>[color=green]
> > Are address4 and address5 both fields in the table "addresses", or is[/color][/color]
one[color=blue]
> of[color=green]
> > them a control on a form?[/color]
>
> They're both fields of the table. I'm not using any forms here.
>
> Cheers,
> Chris[/color]


Chris
If you look at the help file under the Instr function, you will see it can
return a number of values depending on the values of the two values of
SearchFor and SearchIn. Obviously, since you know that SearchFor is a
comma, this value cannot be null and it is also not zero length. However,
depending on the design of the field, address5 might contain zero-length or
null values - and that's even before you consider whether it has commas in
it.
Your problem comes with subtracting 1 form the value of instr(address5,",").
If address5 contains a comma, all is well but if there is no comma, you may
get a null or -1 (depending on database design).
Asking for Left("Hello", -1) or Left("Hello", Null) will cause you trouble.

You need to check if there is a comma first, e.g.

IIf(InStr([address5],",")>0,Left([address5],InStr([address5],",")-1),"No
Comma")
or

IIf(InStr([address5],",")>0,Left([address5],InStr([address5],",")-1),[addres
s5])

or whatever.


HTH

Fletcher

re: Small syntactical error

"Fletcher Arnold" <fletch@home.com> wrote in message
news:bn2tb9$atu$1@sparta.btinternet.com...[color=blue][color=green][color=darkred]
> > > "C L Humphreys" <clhumphreys@toofgib.moc> wrote in message
> > > news:bn11gp$rdu$1@ucsnew1.ncl.ac.uk...
> > > > The following gives an invalid procedure call error:
> > > >
> > > > select *
> > > > from addresses
> > > > where
> > > > address4 = left(address5,instr(address5,",")-1)[/color][/color][/color]
[color=blue]
> Your problem comes with subtracting 1 form the value of[/color]
instr(address5,",").[color=blue]
> If address5 contains a comma, all is well but if there is no comma, you[/color]
may[color=blue]
> get a null or -1 (depending on database design).[/color]
[color=blue]
> You need to check if there is a comma first, e.g.
>[/color]
IIf(InStr([address5],",")>0,Left([address5],InStr([address5],",")-1),[addres[color=blue]
> s5])[/color]

You know what? You're right :o)

Thanks a lot, I just assumed it would allow me to do it but throw up errors
as it made it's way through the table (which seems to happen on some
occasions).

Cheers,
Chris