How to count

[frade]

Hi :hi:

I'm using MS access. I would like to create a query to calculate
the number of one specified character in a string

What could I do?

Example in my field

Mainstreet 4, 75000, PARIS

in this case, the query returns 3 (the number of commas)

Thanks a lot!

Fran?ois

[Norie]

Fran?ois

I don't think this is possibly with a simple query.

You could use VBA to create a user defined function, but that might impact on performance.

What are you actually trying to do?

[frade]

Thanks Norie for your reply.

I would like to isolate the zip code from a complete address
If possible by creating a new query in MS Access

But in some cases, there are 3 commas in the string
something like this : Street of Washington,78, 1000 Brussels

A third comma can appears between the street's name and the number of the street...

In other records, only 2 commas are present in the field's address
Something like this:Mainstreet 4, 75000, PARIS
without a comma between the street's name and the number of the street...

What could I do?

Best regards,

Fran?ois

[OBP]

Fran?ois, you can do this in VBA on a form as you can search from the right hand side and it will be the part of the string after the first comma,
Ideally the table should have seperate fields for each part of the address to prevent this kind of problem.
If you need help I can show you how to write the VBA to extract the Post code in to a seperate field on the form.

[Norie]

Fran?ois

What do you want to extract in your examples?

I know it's the zip code, and think I know where it is, but I'm not 100% sure.

[frade]

Hi everybody and many thanks!
I understand you very well when you say that the table should have seperate fields for each part of the address..but it's not a database created by myself
Unfortunately, I have a field with a complete address

Example 1 In this case:
Washington's Avenue,78,1000 Brussels
I would like to extract '1000' (the four first caracters after the second comma)

However, in the database received, in some other records ..it's something
like this
Example 2
London's Avenue 45,4000 Liege
I would like to extract '4000'
(the four first caracters after the first comma)

the rule is different in my 2 examples and depends of the number of commas...I would like to find a solution that works for the both

What could I do to extract the Post code with VBA or a query?

Regards,

Fran?ois

[xCav8r]

VBA is the easiest way imho.

If those are your two rules, just give me your table name and the names of the fields in that table, and I'll give you the VBA you need to extract the zip. I'll do it based on location of commas and spaces, so if there are any other rules that might apply, let me know.

Current Rules:
1. Two commas: Street Name + (Comma) + Building Number + (Comma) + Zip + (Space) + City
2. One comma: Street Address (separated by spaces with the last contiguous set of characters = Building Number) + (Comma) + Zip + (Space) + City

Or, if you want to break the address into its different elements, I can do that for you too. In other words:

-- name of street
-- number of building
-- street address = name of street + number of building
-- zip code
-- city

[xCav8r]

Not pretty, because I adopted it from something else I had previously written, but I did fill it with lots of comments, so hopefully you'll be able to adjust it to your needs.



Option Compare Database
Option Explicit
Option Base 1
Const strcBlankSpace As String = " "
Const strcComma As String = ","

Sub ManipulateRecordSets()
Dim db As DAO.Database
Dim rstOld As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strSQL As String
Dim varaMyParsedAddress As Variant
Set db = CurrentDb
strSQL = "SELECT Old.PostalAddress FROM tblPostalAddresses_OLD as Old;"
Set rstOld = db.OpenRecordset(strSQL)
strSQL = "SELECT New.txtStreetName, New.txtBuildingNumber, New.txtZip, " _
& " New.txtCity FROM tblPostalAddresses_New as New;"
Set rstNew = db.OpenRecordset(strSQL)
With rstOld
If .BOF = False And .EOF = False Then
Do While .BOF = False And .EOF = False
varaMyParsedAddress = ExtractAddressElements _
(.Fields!PostalAddress)
With rstNew
.AddNew
.Fields!txtStreetName = varaMyParsedAddress(1)
.Fields!txtBuildingNumber = varaMyParsedAddress(2)
.Fields!txtZip = varaMyParsedAddress(3)
.Fields!txtCity = varaMyParsedAddress(4)
.Update
End With
.MoveNext
Loop
End If
End With
End Sub

Function ExtractAddressElements(strStringToParse As String) As Variant
Dim intNumberOfCommas As Integer
Dim varaAddress As Variant
Dim varaStreetAddress As Variant
Dim varaZipCity As Variant
Dim strStreetAddress As String
Dim strBuildingNumber As String
Dim strStreetName As String
Dim strZip As String
Dim strCity As String
Dim intNumberOfStreetAddressElements As Integer
intNumberOfCommas = NumberOfOccurrencesInString(strStringToParse, strcComma)
If intNumberOfCommas >= 1 Then
varaAddress = Split(strStringToParse, strcComma, -1, vbTextCompare)
Select Case intNumberOfCommas
Case 1
' RULE: Street Name + (Comma) + Building Number
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue 78,1000 Brussels
' EVERYTHING BEFORE THE COMMA
' EG: Washington's Avenue 78
' TRIMMING GETS RID OF UNWANTED LEADING AND TRAILING SPACES
strStreetAddress = Trim(varaAddress(0))
' ELEMENT = SET OF CONTIGUOUS CHARACTERS
' EG: Washington's
' EG: Avenue
' EG: 78
' SPACES DIVIDE ELEMENTS
varaStreetAddress = Split(strStreetAddress, strcBlankSpace, _
-1, vbTextCompare)
' NUMBER OF ELEMENTS IN EVERYTHING BEFORE THE COMMA
' EG: Washtington's = 0
' EG: Avenue = 0 + 1 = 1
' EG: 78 = 0 + 1 + 1 = 2
' (This array counts from zero)
intNumberOfStreetAddressElements = UBound(varaStreetAddress)
' BUILDING NUMBER IS LAST ELEMENT BEFORE COMMA
' EG: 78
strBuildingNumber = varaStreetAddress _
(intNumberOfStreetAddressElements)
' IF THERE ARE MORE THAN TWO ELEMENTS, THEN THE STREET
' NAME IS COMPOUND
' EG: Washington's Avenue
If intNumberOfStreetAddressElements > 1 Then
' SHAVE THE TOP ELEMENT OFF THE ARRAY
' IE, GET RID OF THE BUILDING NUMBER
ReDim Preserve varaStreetAddress(0 To _
intNumberOfStreetAddressElements - 1)
' PUT EVERYTHING ELSE BACK TOGETHER AND CALL IT
' STREET NAME
strStreetName = Join(varaStreetAddress, strcBlankSpace)
' THERE'S ONLY ONE ELEMENT; THEREFORE, ONE WORD
Else
strStreetName = varaStreetAddress(LBound(varaStreetAddress))
End If
' EVERYTHING AFTER THE COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
'SECOND ELEMENT AFTER COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
Case 2
' Street Address (separated by spaces with the last contiguous
' set of characters = Building Number)
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue, 78, 1000 Brussels
' EVERYTHING BEFORE FIRST COMMA
strStreetName = Trim(varaAddress(0))
' EVERYTHING BEFORE SECOND COMMA
strBuildingNumber = Trim(varaAddress(1))
' THIS IS INCLUDED IN CASE YOU DON'T WANT TO PARSE
' THE STREET NAME AND BUILDING NUMBER
strStreetAddress = strStreetName + strcBlankSpace _
+ strBuildingNumber
' EVERYTHING AFTER THE SECOND COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER THE SECOND COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
' SECOND ELEMENT AFTER THE SECOND COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
End Select
End If
'RETURN TO SENDER :)
ExtractAddressElements = Array(strStreetName, strBuildingNumber, _
strZip, strCity)
End Function

Function NumberOfOccurrencesInString( _
strStringToSearch As String, _
strSearchForThis As String)
Dim intPositionOfSoughtString As Integer
Dim intStartPosition As Integer
' START INITIAL SEARCH FROM THIS POSITION
intStartPosition = 1
' DETECT ALL OCCURRENCES
Do
intPositionOfSoughtString = InStr(intStartPosition, strStringToSearch, _
strSearchForThis, vbTextCompare)
' AUGMENT COUNTERS ONLY WHEN STRING IS FOUND
If intPositionOfSoughtString <> 0 Then
NumberOfOccurrencesInString = NumberOfOccurrencesInString + 1
' CHANGE STARTING POSITION OF SEARCH TO LOOK AT THE PARTS OF THE
' STRING THUS FAR UNEXAMINED
intStartPosition = intPositionOfSoughtString + 1
End If
' IF InStr RETURNS 0, THEN IT WASN'T FOUND
Loop Until intPositionOfSoughtString = 0
End Function

[frade]

Hi xCav8r!

Thanks a lot for you help!
I'm trying to change a part of your code because I would
like also to add this field "Old.PostalAddress " into the new table.
Therefore I have changed the following lines (bold) but an error occurs


Option Compare Database
Option Explicit
Option Base 1
Const strcBlankSpace As String = " "
Const strcComma As String = ","

Sub ManipulateRecordSets()
Dim db As DAO.Database
Dim rstOld As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strSQL As String
Dim varaMyParsedAddress As Variant
Set db = CurrentDb
strSQL = "SELECT Old.PostalAddress FROM tblPostalAddresses_OLD as Old;"
Set rstOld = db.OpenRecordset(strSQL)
strSQL = "SELECT New.txtStreetName, New.txtBuildingNumber, New.txtZip, " _
& " New.txtCity,Old.PostalAddress FROM tblPostalAddresses_New as New,tblPostalAddresses_OLD as Old;" Set rstNew = db.OpenRecordset(strSQL)
With rstOld
If .BOF = False And .EOF = False Then
Do While .BOF = False And .EOF = False
varaMyParsedAddress = ExtractAddressElements _
(.Fields!PostalAddress)
With rstNew
.AddNew
.Fields!txtStreetName = varaMyParsedAddress(1)
.Fields!txtBuildingNumber = varaMyParsedAddress(2)
.Fields!txtZip = varaMyParsedAddress(3)
.Fields!txtCity = varaMyParsedAddress(4)
.Fields!PostalAddress = varaMyParsedAddress(5)
.Update
End With
.MoveNext
Loop
End If
End With
End Sub

Function ExtractAddressElements(strStringToParse As String) As Variant
Dim intNumberOfCommas As Integer
Dim varaAddress As Variant
Dim varaStreetAddress As Variant
Dim varaZipCity As Variant
Dim strStreetAddress As String
Dim strBuildingNumber As String
Dim strStreetName As String
Dim strZip As String
Dim strCity As String
Dim intNumberOfStreetAddressElements As Integer
intNumberOfCommas = NumberOfOccurrencesInString(strStringToParse, strcComma)
If intNumberOfCommas >= 1 Then
varaAddress = Split(strStringToParse, strcComma, -1, vbTextCompare)
Select Case intNumberOfCommas
Case 1
' RULE: Street Name + (Comma) + Building Number
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue 78,1000 Brussels
' EVERYTHING BEFORE THE COMMA
' EG: Washington's Avenue 78
' TRIMMING GETS RID OF UNWANTED LEADING AND TRAILING SPACES
strStreetAddress = Trim(varaAddress(0))
' ELEMENT = SET OF CONTIGUOUS CHARACTERS
' EG: Washington's
' EG: Avenue
' EG: 78
' SPACES DIVIDE ELEMENTS
varaStreetAddress = Split(strStreetAddress, strcBlankSpace, _
-1, vbTextCompare)
' NUMBER OF ELEMENTS IN EVERYTHING BEFORE THE COMMA
' EG: Washtington's = 0
' EG: Avenue = 0 + 1 = 1
' EG: 78 = 0 + 1 + 1 = 2
' (This array counts from zero)
intNumberOfStreetAddressElements = UBound(varaStreetAddress)
' BUILDING NUMBER IS LAST ELEMENT BEFORE COMMA
' EG: 78
strBuildingNumber = varaStreetAddress _
(intNumberOfStreetAddressElements)
' IF THERE ARE MORE THAN TWO ELEMENTS, THEN THE STREET
' NAME IS COMPOUND
' EG: Washington's Avenue
If intNumberOfStreetAddressElements > 1 Then
' SHAVE THE TOP ELEMENT OFF THE ARRAY
' IE, GET RID OF THE BUILDING NUMBER
ReDim Preserve varaStreetAddress(0 To _
intNumberOfStreetAddressElements - 1)
' PUT EVERYTHING ELSE BACK TOGETHER AND CALL IT
' STREET NAME
strStreetName = Join(varaStreetAddress, strcBlankSpace)
' THERE'S ONLY ONE ELEMENT; THEREFORE, ONE WORD
Else
strStreetName = varaStreetAddress(LBound(varaStreetAddress))
End If
' EVERYTHING AFTER THE COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
'SECOND ELEMENT AFTER COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
Case 2
' Street Address (separated by spaces with the last contiguous
' set of characters = Building Number)
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue, 78, 1000 Brussels
' EVERYTHING BEFORE FIRST COMMA
strStreetName = Trim(varaAddress(0))
' EVERYTHING BEFORE SECOND COMMA
strBuildingNumber = Trim(varaAddress(1))
' THIS IS INCLUDED IN CASE YOU DON'T WANT TO PARSE
' THE STREET NAME AND BUILDING NUMBER
strStreetAddress = strStreetName + strcBlankSpace _
+ strBuildingNumber
' EVERYTHING AFTER THE SECOND COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER THE SECOND COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
' SECOND ELEMENT AFTER THE SECOND COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
End Select
End If
'RETURN TO SENDER :)
ExtractAddressElements = Array(strStreetName, strBuildingNumber, _
strZip, strCity)
End Function

Function NumberOfOccurrencesInString( _
strStringToSearch As String, _
strSearchForThis As String)
Dim intPositionOfSoughtString As Integer
Dim intStartPosition As Integer
' START INITIAL SEARCH FROM THIS POSITION
intStartPosition = 1
' DETECT ALL OCCURRENCES
Do
intPositionOfSoughtString = InStr(intStartPosition, strStringToSearch, _
strSearchForThis, vbTextCompare)
' AUGMENT COUNTERS ONLY WHEN STRING IS FOUND
If intPositionOfSoughtString <> 0 Then
NumberOfOccurrencesInString = NumberOfOccurrencesInString + 1
' CHANGE STARTING POSITION OF SEARCH TO LOOK AT THE PARTS OF THE
' STRING THUS FAR UNEXAMINED
intStartPosition = intPositionOfSoughtString + 1
End If
' IF InStr RETURNS 0, THEN IT WASN'T FOUND
Loop Until intPositionOfSoughtString = 0
End Function

' Previous Rules:
' Examples:
' strStringToSearch = "Street of Washington,78, 1000 Brussels"
' strStringToSearch = "Street of Washington,78, 1000, Brussels"
' strStringToSearch = "Mainstreet 4, 75000, PARIS"
' ElseIf intNumberOfCommas = 2 Then
' strStreetAddress = Trim(varaAddress(0))
' varaStreetAddress = Split(strStreetAddress, strcBlankSpace,-1, vbTextCompare)
' intNumberOfStreetAddressElements = UBound(varaStreetAddress)
' strBuildingNumber =varaStreetAddress (intNumberOfStreetAddressElements)
' If intNumberOfStreetAddressElements > 1 Then
' ReDim Preserve varaStreetAddress (intNumberOfStreetAddressElements - 1)
' strStreetName = Join(varaStreetAddress, strcBlankSpace)
' Else
' strStreetName =varaStreetAddress (LBound(varaStreetAddress))
' End If
' strZip = Trim(varaAddress(1))
' strCity = Trim(varaAddress(2))
' ElseIf intNumberOfCommas = 3 Then
' strStreetName = Trim(varaAddress(0))
' strBuildingNumber = Trim(varaAddress(1))
' strZip = Trim(varaAddress(2))
' strCity = Trim(varaAddress(3))



What could I do?

Fran?ois

[xCav8r]

1. Open tblPostalAddresses_New and add a text field called txtUnparsedAddress.

2. Change the SQL to reflect this change:


strSQL = "SELECT New.txtStreetName, New.txtBuildingNumber, New.txtZip, " _
& " New.txtCity, New.txtUnparsedAddress FROM tblPostalAddresses_New as New;"

3. Update the with statement to reflect the following change:


With rstNew
.AddNew
.Fields!txtStreetName = varaMyParsedAddress(1)
.Fields!txtBuildingNumber = varaMyParsedAddress(2)
.Fields!txtZip = varaMyParsedAddress(3)
.Fields!txtCity = varaMyParsedAddress(4)
.Fields!txtUnparsedAddress = rstOld.Fields!PostalAddress
.Update
End With

[frade]

Ok xCav8r! Thanks a lot for the changes also made in SQL.

I'm really sorry but, in the two cases, I have well a space after each comma

Example:
Washington's Avenue, 78, 1000 Brussels
London's Avenue 45, 4000 Liege

I have tried to replace all spaces in my field but it's not a solution
to get a field like this: Washington'sAvenue,78,1000Brussels
In this case, your code is not usefull.

What can I change in your code?

Best regards,

Fran?ois

[xCav8r]

Frade,

All this does is move the original address unchanged to the new table. It doesn't add or subtract spaces of any kind. The examples I used were based on post 27409. Anyway, you can see these as you typed them in the aforementioned post when you look at the old table.

If you're getting "Washington'sAvenue,78,1000Brussels", then you must have changed something other than what I had indicated. I can't reproduce that on my end. Perhaps if you pasted the code again, I can see what changes have caused this.

What's the purpose of storing the full address in a field anyway? I parsed the components for you, so why can't you put together in your preferred format as necessary?

[frade]

Hi,

I'm working now on a database for which I was not a designer.
I have received the database with many discrepancies..

Storing the full address is not very usefull indeed but In my database, I have
several fields (Id_NR, address) and I would like to link the new table with the old table that contains the other fields (with the full address, not a good idea
but well with the field "ID_nr" )

You can see an example where the old table contains the address (with a space after each comma, such as in my database)

Please have look at this attached file

Thanks a lot

Fran?ois

[Norie]

Fran?ois

A bit off topic, but when I downloaded your database and unzipped it it was 9MB though it ony has 2 tables with 2 records each and the code.

When I compacted it the size reduced to 164K.

[frade]

Hi Norie,

I don't understand why your unzipped file is so huge.
First, please delete the 2 records in the table called "tblPostalAddresses_New"
then, please launch the module

I would like to isolate each zip code from the old table

Thanks a lot!

Fran?ois

[Norie]

Fran?ois

How big is the original database?

Have you compacted it lately?

[frade]

Hi Norie,

9, 16 Mo
Yes, I have changed the database lately.

I don't understand why the database is so huge with so few records..

Do you have an advice to change my code?

thanks and sorry for my poor english..

fran?ois

[Norie]

fran?ois

I mean have you compacted the database.

Tools>Database Utilities>Compact and Repair...

When you delete tables/forms/reports in a database Access doesn't quite delete them - they're lurking somewhere I think.

[frade]

Hi Norie,

Please have a look at this database now compacted

Thanks

Fran?ois

[xCav8r]

Anyone can obviously help you with the code, but I won't have access to a PC with MS Access till evening CST (US) when I get home from work. If no one has fixed it by then, I will.

Oh, and the size, that happened on my end. ;)

[xCav8r]

After looking at what you've done here, I think the VBA might be a little too complicated for you at this point. No disrespect intended. Rather than spending time writing stuff that isn't exactly what you're looking for...why don't you just send me your database and tell me what you want done? I'll adjust the code to meet your needs and post it (the code) here publicly with the hope that others might find it useful.

Feel free to remove or alter the data if it's confidential. Just leave me a few representative samples with which to work. If you don't want to post it publicly, send me an instant message and I'll give you an email or messenger address.

FYI...considering the difference in our time zones, I don't think I'll be able to return it to you until this weekend.