Solved Split Fields (1 Viewer)

[Isaac]

Tried both of these queries but both returned errors:

UPDATE tblStaff SET tblStaff.fldHouseNumber = CStr((HouseNumber([tblStaff]![fldStreet])));

UPDATE tblStaff SET tblStaff.fldHouseNumber = CStr(Val(Left([fldStreet],InStr(1,[fldStreet]))))
WHERE (((tblStaff.fldHouseNumber) Is Null));

You missed a space
Try this

UPDATE tblStaff SET tblStaff.fldHouseNumber = CStr(Val(Left([fldstreet],InStr(1,[fldstreet]," "))))

WHERE tblStaff.fldHouseNumber Is Null and instr(1,tblStaff.fldHouseNumber," ")>0

This assumes that all of your [fldstreet] values at least have a space in them somewhere.

 

[MajP]

This works for me fine. Assuming the first thing is street number.

Public Function GetHouseNumber(strAddress As Variant) As Variant
  If Not IsNull(strAddress) Then
     GetHouseNumber = Split(strAddress, " ")(0)
  Else
    GetHouseNumber = Null
  End If
End Function

Sql

UPDATE tblAddress SET tblAddress.HouseNumber = GetHouseNumber([address]);

 

[Kayleigh]

That is a great solution!
Now how do I delete the house numbers in the original field - so it displays the street name only?

 

[MajP]

Once you get the number into a seperate field you can do an update query using the replace function to replace the number with nothing.

 

[Kayleigh]

Yes actually just found the solution here

Thanks!

 

[MajP]

I do not see that as the solution. You already had the solution to find the house number. You need a query like

UPDATE tblAddress SET tblAddress.AddressNoNumber = Replace([addressWithNumber],[HouseNumber],"");

Look at the table and run the query. I updated a new field but you can update the existing. Safer the way I did it in case you make a mistake.