strings - How do I?

[Tcs]

I have field names with data I'm trying to pull apart. Ex:

1234 Horse Hockey Ln 678

I'm trying to generate a query to look for "Ln", so I can strip off the last 3
characters and plug them into a separate "apartment" field. Note that this
apartment value, if present, may vary in length, from as short as 1 character to
as long as 4 characters.

I'm trying to use:

iif(InStr(1,[SitusAddress],"Ln")<(len([SitusAddress])-1))

But I can't even get past Access (2k) trying to tell me:

....wrong number of arguments...

much less if I can even do what I'm trying to do.

Can anyone help?

Thanks in advance,

Tom

 

[Tom Lake]

I have field names with data I'm trying to pull apart. Ex:

1234 Horse Hockey Ln 678

I'm trying to generate a query to look for "Ln", so I can strip off the
last 3
characters and plug them into a separate "apartment" field. Note that
this
apartment value, if present, may vary in length, from as short as 1
character to
as long as 4 characters.

Try:

Mid([Address], Instr([Address], "Ln ") + 3)

This will get all the characters after "Ln "

Tom Lake

 

[Marshall Barton]

I have field names with data I'm trying to pull apart. Ex:

1234 Horse Hockey Ln 678

I'm trying to generate a query to look for "Ln", so I can strip off the last 3
characters and plug them into a separate "apartment" field. Note that this
apartment value, if present, may vary in length, from as short as 1 character to
as long as 4 characters.

I'm trying to use:

iif(InStr(1,[SitusAddress],"Ln")<(len([SitusAddress])-1))

IIf(InStr([SitusAddress],"Ln") > 0, Mid(SitusAddress,
InStr([SitusAddress],"Ln") + 1, Null)