Converting data of a field into different fields in the same record.

[creativeimages]

Converting data of a field into different fields in the same record.
Can I use an "Expression" or the "Expression Builder" to use a string function to select part of a field in a record as the data for another field in that record? Must "Expression" or "Expression Builder" be used only in Queries? If a data string includes the street address, as "123-B Main Street" or "456 Central Boulevard", where I need to have fields named StreetNumber, StreetNumberAlpha, StreetName and StreetType, how can I convert the data into different fields in the same record?

 

[willir]

creativeimages

I find the "tool" that offers the must flexibility and power is VBA...

Parsing a street address is always hard, but here is a simple example...

Assumptions...
txtFullAddress - Text box with "full" address - format is number + street name "," city - like I said, simple
txtStreetNo - Text box with street number
txtStreetName - Text box with street name
txtCity - Text box with city

Private Sub txtFullAddress_AfterUpdate()
Dim strAddr as String
Dim intStreetNo as Interger, intCity as Interger, intLen as Interger

If Len(Nz(Me.txtFullAddress)) > 0 Then
   strAddr = Me.txtFullAddress 
   intStreetNo = InStr(1, strAddr, " ")
   intCity = InStr(1, strAddr, ",")
   intLen = Len(strAddr)
   Me.txtStreetNo = Left(strAddr, intStreetNo - 1)
   Me.txtStreeName = mid(strAddr, intStreetno + 1, intLen-intCity)
   Me.txtCity = Right(strAddr, intLen-intCity-1)
End If

Here, the full street address is parsed and assigned to three text boxes on the form.

Richard