Using Access 2000. I need to group the address by suburb then by street name in my report.
Two major problems here:
1. Unit number has the word "Unit" before number
2. Semi detach house has "a" or "b" after number
Examples of the addresses as below. Address 1 means the first line/field for users to key address in line one, Address 2 means second line/field two for users to key address, normally village, park or street name
eg.1, Normal addaress:
Address 1: 54
Address 2: Cale Street
Suburb: Como
eg 2, the address of semi-d house:
Address 1: 10a
Address 2: Britan Street
Suburb: Como
eg 3, the address of apartment or unit:
Address 1: Unit 5, 72 Gordon Way
Address 2: Bentley Park
Suburb: Bentley
eg 4, the address that has park or village:
Address 1: 20 Graham Crescent
Address 2: Bentley Park
Suburb: Bentley
I have problem sorting them when they are not the normal address type. I created two combo box in my form to filter the suburb and street name and my report display all the address 1 and address 2 under specific Suburb.
What should I do to sort report by street names by "ignoring" the numbers in front for the address temporary, but at the same time, still showing the full address on report?
4 2924
Hi
When I create Address Line 1 and Address Line 2 I always ask the people do you ever want to sort it? if not then its usually fine... otherwise I would create 'atomic' fields so I would add extra control like House Number, street name, unit (if applicable) etc... literally everything what I think is needed for sorting.
So in your case if you can do it I would consider redesigning the table.
Sorry if it doesn't help.
Otherwise you will have quite a few formulas to do the job and you would need to update it each time you get new scenario.
Regards
Emil
Hi Emil,
I cannot redesign the table because I am using Access to connect to our main system thru ODBC, thus all the tables are from the main system and the tables are only linked to my Access for reports. Mainly because main system only have standard reports and cannot produce what my managers required.
Any idea about the code or method to sort the address?
Hi. Two comments here. Firstly, you will need to design a custom function in VBA to parse the addresses concerned and return a version on which you can sort. Such a function could then be used within a query to provide a calculated field on which your report could be sorted.
Secondly, you need to be very clear before you design the custom function what it is you need done, otherwise your function will not cover all possibilities. Write down as many examples as you can, and in doing so write down how you want them to appear after transformation.
As an example of the pitfalls of not defining your problem clearly consider your third example in post # 1. Simply removing the 'Unit 5' component still leaves you with '72 Gordon Way', which is not in the form you require it - at least until a further application of the same function has taken place.
Be aware that text-processing problems like this, based on messy and ill-structured real world data, are non-trivial and take a lot of effort to get right (if this is possible at all). If you expect to obtain a general solution that meets your needs you will need to be very careful in specifying the transformation involved before you take this further.
In my opinion there is only so far you will be able to go with this one, as users can and will have entered addresses such as 25 a Somestreet instead of 25a, which makes it difficult to parse where the real street name begins and the qualified number ends...
-Stewart
Hi If you use only few forms to input the data then you could consider adding extra fields + VBA code to move the data to the address line 1 + address line 2 when you type it // and use the additonal columns for sorting...
Can't see a better way at the moment :)
Regards
Emil
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Andrew McLean |
last post by:
I have a problem that is suspect isn't unusual and I'm looking to see if
there is any code available to help. I've Googled without success.
Basically, I have two databases containing lists of postal addresses and
need to look for matching addresses in the two databases. More
precisely, for each address in database A I want to find a single
matching address in database B.
I'm 90% of the way there, in the sense that I have a simplistic...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much appreciated.
Thanks in advance
|
by: 2BaCook |
last post by:
Hi, I have an Access database that I am creating a java front end to.
I am trying to insert a record into a table and get a problem whenever
I try to insert into my "Number" column.
Here is my table schema:
Column 1 Name: Number Type: VARCHAR
Column 2 Name: Street Type: VARCHAR
Column 3 Name: Phone Type: VARCHAR
Column 4 Name: Comments Type: LONGCHAR
Column 5 Name: Pets Type: VARCHAR
|
by: Robert Fitzpatrick |
last post by:
Thanks to some help here on the list, I've been able to get addresses
sorting pretty well, but now I have a issue with same addresses on
different streets not grouping the streets. This is what I'm using a
substring search in the ORDER BY statement now like in this view:
SELECT tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
tblhudsimilargroups.similar_group_id, tblhudbuildings.address,
tblhudbuildings.hud_building_id,...
|
by: Peter Chant |
last post by:
I'm considering setting a website up for a club. I do not plan the contents
to be for public consumption, but on the other hand I'm not going to have
anything on there that is confidential, that would cause a problem if it
went further.
The basic reason is for publicity of club events. I want to make it easy to
use. I suspect a login with a password would be too much effort for most
people. I also note that computer literacy is not a...
| |
by: cassetti |
last post by:
Here's the issue:
I have roughly 20 MS excel spreadsheets, each row contains a record.
These records were hand entered by people in call centers.
The problem is, there can and are duplicate phone numbers, and emails
and addresses even person names. I need to sift through all this data
(roughly 300,000+ records and use fuzzy logic to break it down, so that
i have only unique records.
|
by: terbolee |
last post by:
I have two tables: Organisations & Invoices. I have a form called Invoices that is used to enter data into the invoices Table. When entering data, I'd like to be able to type into the field, and then click on the ! button to run an SQL script....which will populate some of the fields on the form. Having read discussions here, I've tried some scripting but it does not work. Not sure if I'm on the right track even. Can you help?
Private Sub...
|
by: SQL Learner |
last post by:
Hi all,
I have an Access db with two large tables - 3,100,000 (tblA) and 7,000
(tblB) records. I created a select query using Inner Join by partial
matching two fields (X from tblA and Y from tblB). The size of the db
is about 200MBs.
Now my issue is, the query has been running for over 3 hours already -
I have no idea when it will end. I am using Access 2003. Are there
ways to improve the speed performance? (Also, would the...
|
by: Pacific Fox |
last post by:
Hi all,
I have a SQL statement that allows paging and dynamic sorting of the
columns, but what I can't figure out without making the SQL a dynamic
string and executing it, or duplicating the SQL statement between an
IF and ELSE statement.
Following is the SQL statement;
set ANSI_NULLS ON
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |