Solved Split Fields

[Kayleigh]

Hi,
This may have a simple solution but can't think of a way to do it!
I have field house number and street in my database but the data imported had full address in one field - which was copied into street field. How do I move the house number (possibly different character size) into other field?
Thanks,
Krayna

 

[Uncle Gizmo]

You might find this thread useful:-

Extract House Number

I needed to extract the house number from the first line of an address for a HMRC GiftAid Claim form. HMRC requirements are that the field should contain either the house number or the house name, and no other information. The next field should contain the postcode. My assumption is that the...

www.access-programmers.co.uk

 

[Kayleigh]

Hi looked at it and adapted a similar function to do this.
However I am having trouble with updating current fields using function.
I created an update query but when running I encounter error - see enclosed.

UPDATE tblStaff SET tblStaff.fldHouseNumber = HouseNumber([tblStaff]![fldStreet])
WHERE (((tblStaff.fldHouseNumber) Is Null));

What can I do about this?

 

[MajP]

Is fldHouseNumber numeric? It should be a string even though it is a house number.

 

[Kayleigh]

Nope short text

 

[MajP]

Does the function return a delimited string '1234'? If not you need to wrap in single quotes.

 

[Kayleigh]

See code:

Function HouseNumber(strInString As String) As Long
Dim intLen  As Integer
Dim intCounter As Integer
Dim strNumber As String
Dim blnFoundNumber As Boolean
Dim fGetHouseNumberOrName As String


blnFoundNumber = False
strInString = Trim(strInString) 'Removes leading & trailing spaces
intLen = Len(strInString)       'Stores original length
intCounter = 1                  'Counter & position marker

    If strInString = "" Or IsNull(strInString) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    
            Do
                If IsNumeric(Mid(strInString, intCounter, 1)) Then              'Check if that single character is a number
                    blnFoundNumber = True
                    strNumber = strNumber & Mid(strInString, intCounter, 1)     'If it is add to existing ones (if any)
                    intCounter = intCounter + 1                                 'Add to counter so we know to go to next character on the next pass/loop
                Else
                    If intCounter = 1 Then Exit Do                              'The first Character "is Not Numeric" so don't Check any more, so Exit
                    If blnFoundNumber = True Then Exit Do                       'This Character is "is Not Numeric" so your House number is finished, so Exit
                    intCounter = intCounter + 1                                 'It "is Not Numeric", add to counter so we know to skip it
                End If
            Loop Until intLen = (intCounter - 1) 'Go until we processed all characters. The reason we have to do intCounter -1 is that Len starts at 0 & we told intCounter to start at 1

    If IsNumeric(strNumber) Then
        fGetHouseNumberOrName = strNumber
    Else
        fGetHouseNumberOrName = strInString
    End If
    
End Function      'fGetHouseNumberOrName

 

[Isaac]

See code:

Function HouseNumber(strInString As String) As Long
Dim intLen  As Integer
Dim intCounter As Integer
Dim strNumber As String
Dim blnFoundNumber As Boolean
Dim fGetHouseNumberOrName As String


blnFoundNumber = False
strInString = Trim(strInString) 'Removes leading & trailing spaces
intLen = Len(strInString)       'Stores original length
intCounter = 1                  'Counter & position marker

    If strInString = "" Or IsNull(strInString) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
  
            Do
                If IsNumeric(Mid(strInString, intCounter, 1)) Then              'Check if that single character is a number
                    blnFoundNumber = True
                    strNumber = strNumber & Mid(strInString, intCounter, 1)     'If it is add to existing ones (if any)
                    intCounter = intCounter + 1                                 'Add to counter so we know to go to next character on the next pass/loop
                Else
                    If intCounter = 1 Then Exit Do                              'The first Character "is Not Numeric" so don't Check any more, so Exit
                    If blnFoundNumber = True Then Exit Do                       'This Character is "is Not Numeric" so your House number is finished, so Exit
                    intCounter = intCounter + 1                                 'It "is Not Numeric", add to counter so we know to skip it
                End If
            Loop Until intLen = (intCounter - 1) 'Go until we processed all characters. The reason we have to do intCounter -1 is that Len starts at 0 & we told intCounter to start at 1

    If IsNumeric(strNumber) Then
        fGetHouseNumberOrName = strNumber
    Else
        fGetHouseNumberOrName = strInString
    End If
  
End Function      'fGetHouseNumberOrName

You might be able to use VAL

?val(left("123 168th st",instr(1,"123 168th st"," "))) returns 123

 

[Kayleigh]

Sorry where do I include that?

 

[Isaac]

Sorry where do I include that?

Replace everything with it - nothing else needed.

For example, if you were using a query, you could update the House Number column to

val(left([Address],instr(1,[Address]," ")))

(I just made up those field names replace with yours)

 

[Kayleigh]

Sorry tried that but still getting error!

 

[MajP]

STOP. The problem is you are trying to insert a long into a string field. The above solution does no better because the val function returns a numeric.
You need to change the function to return a string or use CSTR.

 

[MajP]

If you read the error message you are getting a type mismatch.

 

[Kayleigh]

Have changed type but still getting same error??

 

[MajP]

are you converting the value to a string? If so show the query.

 

[Kayleigh]

Query:

UPDATE tblStaff SET tblStaff.fldHouseNumber = HouseNumber([tblStaff]![fldStreet])
WHERE (((tblStaff.fldHouseNumber) Is Null));

 

[Isaac]

As MajP mentioned, if your destination column for the Update or Insert is not numeric, you may need to use CSTR() wrapped around my suggestion.
4 more letters......Still probably better than 34 lines

 

[MajP]

As I have said
HouseNumber([tblStaff]![fldStreet])
That returns a long. Look at the function signature to see the return type.
Try this
cstr((HouseNumber([tblStaff]![fldStreet]))

 

[Kayleigh]

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));

 

[Gasman]

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 really should not change code unless you know what you are doing.?

The code linked has a function called fGetHouseNumberOrName and returns either a numeric or string value from a variable of the same name.
You have changed the function to be named HouseNumber without changing the variable name for the return value.?