Update query help with Instr()

[Ed Peluffo]

I have an Address and Apt Field, Address Filed currently holds the Apt info.
Need to move it to Apt field. most of apt info starts with a # then the
number. How do I create a query to take anything on or after the # sign and
update the apt field with it.

Thanks in advance as my help files don't seem to be working correctly

 

[Douglas J. Steele]

Mid(Address, InStr(Address, "#") + 1) should give you everything that's
after the first # in the string.

 

[David Whitson]

UPDATE Apartment SET Apt = mid(Address,instr
(Address,'#')), Address = mid(Address,1,instr
(Address,'#')-1)

This will MOVE the # and everything after it to the Apt.
field and the second part will REMOVE the # and
everything after it from the Address Field.

David W.

 

[Ed Peluffo]

Thanks, it works

Mid(Address, InStr(Address, "#") + 1) should give you everything that's
after the first # in the string.

 

[Ed Peluffo]

Thanks for your prompt response

UPDATE Apartment SET Apt = mid(Address,instr
(Address,'#')), Address = mid(Address,1,instr
(Address,'#')-1)

This will MOVE the # and everything after it to the Apt.
field and the second part will REMOVE the # and
everything after it from the Address Field.

David W.