Split apart string at "#" [apartment number in address field]

[sarahs]

I am trying to write an update query that will split apart an address field into two or three parts: StreetNumber, Street, and ApartmentNumber [if there is an apartment number].

The following query works fine to split out the Street Number:

UPDATE Workers SET Workers.StreetNumber = Mid([Full Address],1,InStr([Full Address]," ")-1)
WHERE (((Workers.StreetNumber) Is Null) AND ((Mid([Full Address],1,1)) In ("1","2","3","4","5","6","7","8","9")));

And the following query gives everything after the street number [ie, street name and apartment number, if there is one]

UPDATE Workers SET Workers.Street = Mid([Full Address],InStr([Full Address]," ")+1)
WHERE (((Workers.Street) Is Null) AND ((Mid([Full Address],1,1)) In ("1","2","3","4","5","6","7","8","9")));

However, I can't figure out how to get the second query to return ONLY the street information [excluding the apartment number if there is one], and I haven't been able to write a third query to split off the apartment number. Because the apartment number is almost always preceded by the # sign, [and I am willing to hunt for the ones that aren't and change them by hand], I thought I could use the # as criteria to identify which records to update using the third query, and to mark the place where the string should be split. But I can't get it to work.

Any suggestions?

 

[cpod]

The # sign is a special "wildcard" character. To search for it you have to enclose it in brackets:

*[#]*

will find call entries containing the # sign.

 

[sarahs]

yes, this is how I am searching for the '#' character (I am enclosing it in square brackets: [#]). Setting the criteria is not the problem.

I am having problems figuring out how to write the query so it selects only the middle part of the string [to the right of the first space -- if the first space is preceded by numbers, and to the left of the #, if there is a #] for the street field, and only the part of the string to the right of the # if there is a # for the AptNumber field.

 

[cpod]

Well, you already have the street name and apartment number seperated for the field "full address" into the field "street":

Streetname: Left([street],InStr([street],"#")-1)

ApartmentNumber: Right([street],Len([street])-InStr([street],"#"))

Unless I am still missing the point.

 

[sarahs]

Yes, this would work, but it would require me to make some intermediate steps: first I would have to run a query to separate out the StreetNumber and put that in its own field, then the second query to put the rest of the Full Address field [The Street name and Apartment number] in 'Street'. Then I would run two additional queries on 'Street' to update the StreetName fields and Apartment Number fields, and it would require that I create a new field called 'StreetName'. If this is the only way to do it, then I will do it this way; I am just wondering if there isn't a better way, since this requires storing information in 'Street', which I actually do not want to store [it is duplicated in the Full Address field and in the StreetName and ApartmentNumber fields].

I am trying to perform this operation in three queries, without storing extra information in a field that I don't actually want to use:

Query 1 would pull out the Street Number from the Full Address field and update the StreetNumber field

Query 2 would pull out just the Street Name from the Full Address field [everything after the space if there is no apartment number, or between the first space and the # symbol if there is an apartment number] and update the Street field

and Query 3 would pull out just the Apartment Number [everything after the # symbol, if there is one] from the Full Address field and update the AptNumber field

I have the first query, but can't figure out how to do Queries #2 and 3.

 

[cpod]

OK. If you run the first query and you have the "StreeNumber" then:

SteetName: Mid([fulladdress],Len([streetnumber])+2,Len([fulladdress])-(Len([streetnumber])+2+(Len([fulladdress])-InStr([fulladdress],"#")+1)))

ApartmentNumber: ApartmentNumber: Mid([fulladdress],InStr([fulladdress],"#")+1)

 

[sarahs]

Thank you very much for your help! I will try this.