By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Small syntactical error

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...
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

Nov 12 '05 #2

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in message
news:t3****************@nwrddc03.gnilink.net...
"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...
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...
Are address4 and address5 both fields in the table "addresses", or is one of them a control on a form?


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

Cheers,
Chris
Nov 12 '05 #3

P: n/a

"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...
"Larry Linson" <bo*****@localhost.not> wrote in message
news:t3****************@nwrddc03.gnilink.net...
"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...
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...
Are address4 and address5 both fields in the table "addresses", or is

one of
them a control on a form?


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

Cheers,
Chris

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

Nov 12 '05 #4

P: n/a
"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...
"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bn**********@ucsnew1.ncl.ac.uk...
> The following gives an invalid procedure call error:
>
> select *
> from addresses
> where
> address4 = left(address5,instr(address5,",")-1)
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). You need to check if there is a comma first, e.g.
IIf(InStr([address5],",")>0,Left([address5],InStr([address5],",")-1),[addres s5])


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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.