Creating a unique property code (1 Viewer)

timjfletcher · Apr 5, 2011

Hi everyone,

I am currently developing a unique property reference (flatnumber+housenumber+postcode) for use across various databases which may refer to addresses in different formats.

*running the code through Access 2007.

I have used the following code to extract just the numbers in a string:

Code:

Public Function fExtractNumeric(strInput) As String
    ' Returns the numeric characters within a string in
    ' sequence in which they are found within the string
    Dim strResult As String, strCh As String
    Dim intI As Integer
    If Not IsNull(strInput) Then
        For intI = 1 To Len(strInput)
            strCh = Mid(strInput, intI, 1)
            Select Case strCh
                Case "0" To "9"
                    strResult = strResult & strCh
                Case Else
            End Select
        Next intI
    End If
    fExtractNumeric = strResult
End Function

This works really well, however I would to develop this to encorporate house/flat numbers that include a letter (ie 12a, 4b etc).

To add a complication to this I am currently using a field entitled 'address_line_1' which may contain the flat number and the house number (ie flat 4b 12 something street). At present the code produces the number 412, however I would like it to produce 4b12.

Other examples (problems):

Room 12c The Grange 33 acarcia avenue = 12c33

Flat 4 23b main road = 423b

Flat 6, 54a bridge road = 654a

Apartment 2a, 74 oak road = 2a74

Sorry if the next bit gets overly complex but I also need to takle alternative flat names, such as 'First Floor Flat' so that they become fff, 'Second Floor Flat' becomes sff, 'Ground Floor Flat' becomes gff and 'Basement Floor Flat' becomes bff.
*(I will probably need to build a lookup table for variations of these ie gnd = ground, 1st = first etc)

Therefore:

First floor flat 19 broad street = fff19

Ground floor flat 33 drive street = gff33

1st floor flat 19 broad street = fff19 (trickiest)

Is this possible?

Thanks in advance.

ted.martin · Apr 5, 2011

It seems to be that what you are asking for requires the values of various fields to be concatenated together to provide the somewhat complex code to which you refer.

My advice, for what its worth, would be to use a standard incremental (autonaumber) PropertyID field for each record. By all means present this at the user intercace not only as a PropertyID but also your concatenated method. BUT bear in mind, at some stage you will probably want to do a search and it will be far easier to search for a particular field in your Property table and then having found it, maybe refer to it's PropertyID rather than trying to search for something in a lengthy concatenated string.

CBrighton · Apr 5, 2011

I'd agree with Ted that an autonumber field would be better.

If you still don't want that the first set of examples can be done by finding the spaces. The letters which you want to capture are all directly next to the numbers with no spaces. Therefore it could capture from the first number to the character before the next space.

However, you then go on and make it much, much more complex.

Functions are systematic. They do exactly what they are told. However, they don't understand what the data itself means. You may be able to edit the function to do all you want, but I doubt it. And even if you did, you would have to program in every possible variation of flat numbering (1st fl, 1st flr, 1st floor, 1F, first floor flat, etc) so that the function knew how to treat them.

Realistically, it sounds like you are aiming too high and expecting too much reasoning from a function.

timjfletcher · Apr 5, 2011

Many thanks for your quick replies.

I would like to use an autonumber, however this would not allow linking/matching to other databases, as the autonumber is only unique to that database.

I like the idea of identifying numbers followed by a single letter followed by a space. This would solve alot of the occurances but I'm not sure if it is possible to adapt the existing code, or whether I need to start from scratch.

Any ideas?

Thanks again.

CBrighton · Apr 5, 2011

What I meant was that your first examples are in that format:

Room 12c The Grange 33 acarcia avenue = 12c33

Flat 4 23b main road = 423b

Flat 6, 54a bridge road = 654a

Apartment 2a, 74 oak road = 2a74

As long as there is a set format (i.e. capture all numbers and all letters which are directly next to numbers) the function can be adapted. However the second set of examples make this a moot point as that involves the function having to read and understand the data it is being sent.

A function doesn't understand, it performs standardised actions. It can check criteria, but not work out if the first line of the address is an odd way of writing a flat number or just a house name. And a mix of both is even worse. How would the function know if "First Floor Seaview House" should be shortened to FF or FFSH? The function wouldn't know that only the first 2 words are relating to the floor.

timjfletcher · Apr 6, 2011

Thanks again for the update.

I think I will ignore the 2nd stage for the time being which involves identifying first floor flats etc.

However, it would be really useful if you could let me know the amended code so that if a number is followed by a letter (a-z), and then by a space, then include it in the final code?

Sorry, I really dont have a clue how to adjust my existing code below:

Code:

Public Function fExtractNumeric(strInput) As String
    ' Returns the numeric characters within a string in
    ' sequence in which they are found within the string
    Dim strResult As String, strCh As String
    Dim intI As Integer
    If Not IsNull(strInput) Then
        For intI = 1 To Len(strInput)
            strCh = Mid(strInput, intI, 1)
            Select Case strCh
                Case "0" To "9"
                    strResult = strResult & strCh
                Case Else
            End Select
        Next intI
    End If
    fExtractNumeric = strResult
End Function

Thanks

Creating a unique property code (1 Viewer)

timjfletcher

New member

ted.martin

Registered User.

CBrighton

Surfing while working...

timjfletcher

New member

CBrighton

Surfing while working...

timjfletcher

New member

Users who are viewing this thread