Update query help with Instr()

  • Thread starter Thread starter Ed Peluffo
  • Start date Start date
E

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
 
Mid(Address, InStr(Address, "#") + 1) should give you everything that's
after the first # in the string.
 
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.
 
Thanks for your prompt response

David Whitson said:
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.
Click to expand...
 
Back
Top