Welcome

Hi,

Welcome to the fifth edition for 2013. I have added the tip title in the subject, so hopefully you can find relevant tips more easily, thus I will only be doing one tip per Newsletter.

Lots of things happening on the home front:

My daughter's little baby girl is now 3 months old. How time flies. My daughter Kirstie, wishes to be a stay at home mum, so she has started an online business.

www.behealthyhappyandhot.com

(hot stands for Helping Others Thrive)

I realise it is not on topic for this Newsletter, but if any of you are interested in simple ways to be healthy, happy or to help others thrive, it would be great if you could visit her website, watch a video or two, and subscribe. She would really appreciate it, so would I. :0)

We have had plenty of good rain, so the next drought has been postponed for a while.

My son has finally gone back to work after a year off following a snowboarding accident.

In this issue you will find a short but a good tip about using the Replace function.

Microsoft Access Tips

Tip One: Usint the Replace funcition in a query

281-1_2

CELL:326-CDM:1-AP

264-2_2

CELL:382-CDM:2-AP

CELL:218-CDM:10-AP

545-2_1

CELL:246-CDM:5-AP

206-2_1

CELL:522-CDM:1-AP

194-5_1

90-1_1

521-1_1

164-1_1

I need to remove the "CELL:" on the rows that contain the word " CELL: "

So for I have created this:

Mid([Unit Id],6,20)

Now it works, but it's also removed the Numbers. on the fist five places.

Answer:

The reason the Mid function does not work, is that it does not take into account the content of the first 5 characters, so it removes them all, whether it is is 'Cell' or not

In this instance it would be better to use the 'Replace' function.

To do this you will need to use an update query. First make a copy of the table, in case there is a muck up :0)

In the update query, choose the field you wish to update, then in the update to row type the following:

Replace (Nameoffield,"CELL:", "")

Change NameofField to the field name that contains the above information.

What this does: It replaces the work 'CELL:" with an empty string "", thus removing it from the table.

Hope this tip is of benefit.

Cheers