Hell if I know what to do with it...

[2ippy]

Public Function ReplaceAbbreviatedNames(Address As String) As String

Dim PositionOfLastSpace As Integer

PositionOfLastSpace = InStrRev(Address, " ")
ReplaceAbbreviatedNames = Address

Select Case Mid(Address, PositionOfLastSpace + 1)
Case "st"
ReplaceAbbreviatedNames = Left(Address, PositionOfLastSpace) & "Street"
Case "rd"
ReplaceAbbreviatedNames = Left(Address, PositionOfLastSpace) & "Road"
Case "ave"
ReplaceAbbreviatedNames = Left(Address, PositionOfLastSpace) & "Avenue"
End Select

End Function

and...

Public Function fValidUKPostcode(strCodeToTest As String) As Boolean

Const c_REGEXP_UK_POSTCODE As String = _
"([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}" & _
"[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)"

If Len(strCodeToTest) Then
strCodeToTest = UCase(strCodeToTest)
Else
Exit Function
End If
With CreateObject("vbscript.RegExp")
.Pattern = c_REGEXP_UK_POSTCODE
fValidUKPostcode = .Test(strCodeToTest)
End With

End Function

I been shown 2 codes to help me with my forms for inputting addresses. I been playing with them but no sucess.

...help

 

[liddlem]

Hell if anybody knows what to answer????

Sorry 2ippy - I think you'll need to be a bit more specific.
What are you trying to achieve on the form?

I realize that you're trying to format an address, but I think that we would like to have more info in order to respond accordingly.

 

[2ippy]

i was given them 2 codes for changing text in my DB fields.

Now i believe they can be used on the lost focus part of a form textbox. I just can't implement it.

 

[OllyK]

You've been given 2 functions, you need to pass the test data in to them.

if fValidUKPostcode(me.<nameOfFieldWithPostCode>) = true then
'valid postcode
Else
'invalid - show a warning and clear what has been entered, for example
End if

 

[2ippy]

Well that went st8 over my head.

I'm only got basic knowledge atm of VBA. It's a work in progress

 

[Brianwarnock]

If you want to use them to update current fields in your database then use an update query. As you say you are a newbie do you know how to store the functions?
Alt+f11 to open the VBA editor, insert a module, rename it to addressfunctions for example, open it, copy and paste the 2 functions in.

In the Update query update your fields to

functionname([fieldname])

Brian

 

[OllyK]

They look like validation routines to me rather than database updates. The PostCode returns a Boolean to indicate whether the postcode is valid or not, the first would just update the text in a field prior to a db commit.

Select the field you want the checking to apply to
In the properties, chose the Event tab, go to On Lost Focus, choose the button with the 3 elipses (...) and choose Code Builder in the Dialog. Make sure the "Has Module" property of the form is set to yes.

In the code builder, right click and select Add Module, give it name then paste in the 2 bits of code you posted. Go back to the form code panel and in "Private sub <field_name>_LostFocus()" put

if fValidUKPOstCode(me.<fieldname.text>)=False then
msgbox("Invalid Postcode")
me.<fieldname>.selStart = 0
me.selLength = len(me.<fieldname>.text)
end if

Replace <fieldname> with the name of the field you're checking

 

[Brianwarnock]

You are correct about the fvalidoastcode, it validates the postcode format , not the values though.
The other function does update the data changing Any st to Any Street etc
and this can be used either in an update query or maybe in the After_Update event of the field on the entry form, perhaps both to bring the data upto scratch and then maintain it.

Brian

 

[2ippy]

having no luck with either i'm affraid - stuck as a dodo

 

[Brianwarnock]

Have a look at the attached it shows
1 how to use the functions with a form
2 how to use the replace function in an update query
3 how to run a validation query against your table to see the extent of invalid pc code formats

Have fun

Brian