Update query help to find particular word

[delph]

Hi, I have a table with an address field where I need to do an update query to remove the 2nd word if it is "THE" if the 1st word is a number.

Can anyone point me in the right direction? Is there a find or locate function in Access?

Many thanks for any help.

 

[DCrake]

I kinda get the grasp of what you mean can you post a couple of examples of before and after.

David

 

[Galaxiom]

I would write a custom function.

Use Split function to parse the field into an array.
http://office.microsoft.com/en-us/access/HA012289111033.aspx

Test the first member of the array with the IsNumeric function.
http://office.microsoft.com/en-us/access/HA012288681033.aspx

If IsNumeric(MyArray(0)) Then etc '(Arrays count from zero)

Test the second member with:
If MyArray(1) = "the" Then etc

Concatenate the array back together in a loop as appropriate from the test results.

For the loop you will want the UBound function which returns the upper bound (last member position) of the array.

http://office.microsoft.com/en-us/access/HA012289291033.aspx

 

[delph]

Hi David,

The field will contain the 1st line of an address.

Before it would be:
1 The Avenue or 26 The View

After I need it to be:
1 Avenue or 26 View

I know it sounds a strange thing to do but it's to get it in the correct format for the customer.

Thanks for any help you can give, Sue

 

[delph]

GalaxiomAtHome Thanks for your input - this looks pretty complicated to me - newbie at this. Can this be done as an update query?

thanks

 

[DCrake]

The it is easy to do

Use Replace("1 The Avenue"," The "," ")

Where the first paramter is the Address field, the second is the phase to look for, and the third is the replacement value.

Notice how I have added a space either side of the word "The" this stops word like Thetford being changed to "tford".

David

 

[delph]

Many thanks David, that works a treat!

 

[Galaxiom]

Many thanks David, that works a treat!

Perhaps I'm being pedantic but David's solution does not meet the original specification of removing the second word "the" if the first word is a number. It simply removes any instance of the word "the".

So address lines:

"The Domain" becomes "Domain"
"The Theatre" becomes "Theatre"
"Temple of the Sun" becomes "Temple of Sun"
"The Church of Jesus Christ of the Latter Day Saints" becomes "Church of Jesus Christ of Latter Day Saints"

 

[Galaxiom]

- this looks pretty complicated to me ..... Can this be done as an update query?

Perhaps not what you want this time but I would strongly encourage you to learn to write VBA functions sooner rather than later. New developers often put his off because they think it is an advanced skill and instead write massively complex expressions into queries that can be a nightmare to debug.

Once you write a function you can repeatedly use it anywhere in the project. Improve on it or fix it in one place and it is updated everywhere. Paste it into another project. Most developers build up a library of them over time. They are far easier to write then complex expressions because the code doesn't all go on one line.

One often overlooked advantage of a function over an expression in a query is the fact that the function result is immediately reflected in the query results as it is updated without rerunning the query.

When you use an expression, each time you need to edit it you must revert to design view then rerun the query to so see the effect of the change. This can waste a lot of time. With the function you can watch the changes in the query results live while you edit the function code. It can save a lot of time particularly when you are trying to do complex extraction with nested IIF statements.