MS Access query syntax

shayser

I'm not very familiar with Access but I have to do a query on a database.

The database has a table with an address field. This is used for the full address, i.e., streetnumber , streetname, town/city, county.

I want to run a query to return records by individual county.

I've tried various keywords like "in", "=", "like" etc but can't get any results returned unless I use the full and exact address as it is in the field.

It's not my database. I can't make any changes to it so can't split the address data into separate fields for each element of the address.

How can I get what I want returned?

Evil Phil

I don't have access handy. Have you tried something like

select * from AddressTable where Address like '%Dublin%'

Spunj

I think the wildcard character in access is "*" not "%".

Evil Phil

Well you are the man with two brains

Spunj

hehe nope I got a head full of lemons...

shayser

Thanks lads, that works well.

One thing, and it's not terribly important, using the wildcard like you suggest returns results like Dublin Road, Cork when I am querying for Dublin.

keeffe2001

first of all the address column should have been built as seperate columns (street column, county column etc)

If you have no choice how about this


select * from AddressTable where Address like '*, Dublin'

this will work if all the addresses are in the format of '...comma space county'

bonkey

Well, yes, it would.

You're querying for "something containing the word Dublin".

If you assume that the county is always the last word, drop the wildcard at the end (so LIKE '*Dublin'). However, this won't find anything where there is, for example, a full stop after the county-name, or where a Dublin city address has been entered giving a last line of something like "Dublin 4".

wingnut

Not too well up on Access this but would it be possible to and a 'and not like *cork*

the only thing is you would loose address like the Cork Road, Dublin! lol

Seth Brundle

keeffe2001 wrote:

first of all the address column should have been built as seperate columns (street column, county column etc)

If you have no choice how about this


select * from AddressTable where Address like '*, Dublin'

this will work if all the addresses are in the format of '...comma space county'

That would work for comma-space-country but not if it was entered via a multiline textfield as the data will be entered presumably over several lines - the end of each contains a line break.

SELECT Table1.address1
FROM Table1
WHERE (((Table1.address1) Like "*,Dublin"));

wingnut wrote:

Not too well up on Access this but would it be possible to and a 'and not like *cork*

the only thing is you would loose address like the Cork Road, Dublin! lol

So don't put in the second * so that you are searching for
"and not like *cork"

OfflerCrocGod

If the database does not have addresses broken up into their component pieces then it's a badly designed database and should be reworked tbh - can you ask to add a few extra columns? The should be at least some sort of state and city column.

Seth Brundle

OfflerCrocGod wrote:

If the database does not have addresses broken up into their component pieces then it's a badly designed database and should be reworked tbh - can you ask to add a few extra columns? The should be at least some sort of state and city column.

I would be inclined to disagree on the basis that it depends on what the address information will be used for.

OfflerCrocGod

kbannon wrote:

I would be inclined to disagree on the basis that it depends on what the address information will be used for.

I don't know what it's used for but the problems that the OP is having is because the info is not properly spread out so clearly there is a need to break it up. There is no loss in separating it out and there are potential problems in having it all in one column so why not? You would never put a full name in a database you would always separate it out to last_name/first_name - it's essentially the same principal for addresses if you ever plan to gather useful info out of them. And there is a lot of interesting data to be mined from address.

Eoin

This isn't too pretty, but see if it works for you:

SELECT 
trim(mid(Address, InStrRev(Address,",")+1 , len(address)))
AS Expr1
FROM tblAddress;

This query should take all the text from where the last comma in the address is, to the end of the string.

So, if you run it on a table with the following addresses:

my house, my street, Dublin
my house, the street, Dublin 2
a house, some street, Dublin 4.
the gaff, the lane, Co. Dublin.

You will get the following results:

Dublin
Dublin 2
Dublin 4.
Co. Dublin.

Is this any goood to you?

EDIT:

Sorry - that doesn't do the search for you.

If your table has these rows (new one in bold):

my house, my street, Dublin
my house, the street, Dublin 2
a house, some street, Dublin 4.
the gaff, the lane, Co. Dublin.
[b]the house, dublin road, Cork[/b]

then this query will do the Dublin search for you - ignoring the likes of Dublin Road, Cork - and catering it for spaces, full stops and postal codes after Dublin:

SELECT *
FROM tbladdress
WHERE trim(mid(Address,InStrRev(Address,",")+1,len(address))) 
like '*Dublin*';

Eoin

so did that work?