Selecting part of a field up until an # character

[Guest]

Address field contains "1234 Hickory Drive #45".
Mid([Address], InStr([Address], " ") + 1)
Works wonderfully to select "Hickory Drive #45". (excludes the "1234")
Now I want to include the option to exclude anything past an "#" character.

Example "1234 Hickory Drive #45" . Only extract the "Hickory Drive" not the
#45.

Thanks!

 

[Guest]

Try this

Left(Mid([Address], InStr([Address], " ") + 1),Instr(Mid([Address],
InStr([Address], " ") + 1),"#")-1)

 

[Michel Walsh]

Hi,



in a query?

------------------------------
SELECT Mid(Address & " ", InStr(Address & " ", " ") + 1) As intermediate,
Left( intermediate & "#" , InStr( intermediate & "#" , "#") ) As
finalResult

FROM myTable
------------------------------


I appended the character I am looking for, with InStr, to be sure to find it
(otherwise, when it is not found, InStr returns 0 which further complexifies
the logic).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks! Excellent. I tried this and had to modify it a bit, but would never
have been able to do it without this direction.

This is the final code;

SELECT [mytable].[ID Number], Mid(Address & " ",InStr(Address & " "," ")+1)
AS intermediate,
Left(intermediate , InStr(intermediate & "#"," "))
AS finalResult, [Anac sch].Address
FROM [mytable];

Thanks Again!

Hi,



in a query?

------------------------------
SELECT Mid(Address & " ", InStr(Address & " ", " ") + 1) As intermediate,
Left( intermediate & "#" , InStr( intermediate & "#" , "#") ) As
finalResult

FROM myTable
------------------------------


I appended the character I am looking for, with InStr, to be sure to find it
(otherwise, when it is not found, InStr returns 0 which further complexifies
the logic).



Hoping it may help,
Vanderghast, Access MVP

Address field contains "1234 Hickory Drive #45".
Mid([Address], InStr([Address], " ") + 1)
Works wonderfully to select "Hickory Drive #45". (excludes the "1234")
Now I want to include the option to exclude anything past an "#"
character.

Example "1234 Hickory Drive #45" . Only extract the "Hickory Drive" not
the
#45.

Thanks!

 

[Guest]

This didn't seem to work for me but thanks for responding!

Try this

Left(Mid([Address], InStr([Address], " ") + 1),Instr(Mid([Address],
InStr([Address], " ") + 1),"#")-1)

--
\\// Live Long and Prosper \\//

Address field contains "1234 Hickory Drive #45".
Mid([Address], InStr([Address], " ") + 1)
Works wonderfully to select "Hickory Drive #45". (excludes the "1234")
Now I want to include the option to exclude anything past an "#" character.

Example "1234 Hickory Drive #45" . Only extract the "Hickory Drive" not the
#45.

Thanks!