Question Hash symbol in Access 2010

[Peter Ramshaw]

Hi.
As a newbie, I hope no-one flames me for this :-(
I have a porblem and hope someone can assist. Unknowingly, I changed a field in my database that had email addresses in it formatted as hyperlinks to text. (I had a good reason, believe me). Anyway, the field now contains the email address and the 'mail to' data that was in there, ie. a typical entry looks like this:

mmurphy@abcnet.net.au#mailto:mmurphy@abcnet.net.au#

Now, I want to get rid of everything after the hash so I highlighted the column concerned and tried a replace as follows:

Find: #*
Replace with: (ie, nothing/blank)

This will not work and I wonder why? What else can I do to get rid of the extra data in this field?

Thanks
Peter R

 

[John Big Booty]

Welcome to the forum.

Have a look at the various string manipulation functions.

Specifically Instr() and Left().

 

[John Big Booty]

You could use something like;

Me.TextFieldName = Left(Me.TextFieldName, Instr(Me.TextFieldName,"#")-1)

to replace the text in you text box with everything to the left of the #.

 

[Peter Ramshaw]

Big thanks.
I will look at them. I'm not a programmer so the sytax is a bit bewildering to me. Nevertheless I will have a go.
At least you've pointed me in the rigth direction! Much obliged.
Peter R

 

[Peter Ramshaw]

Call me thikc if you like but where would I use that expression?

Me.TextFieldName = Left(Me.TextFieldName, Instr(Me.TextFieldName,"#")-1)


PR
Would I need to run an uipdate query on the field inm question? Or something else?

 

[John Big Booty]

If it's a one of you could use an update query in which case the Update clause would be;

Left(Me.TextFieldName, Instr(Me.TextFieldName,"#")-1)

 

[Peter Ramshaw]

Thanks again. is "Me" the name of the Table the data is in? and I assume textfield name is as it appears to be ....

 

[Peter Ramshaw]

I tried with the following:

Left([Contacts].[Alternate Email Address 1],InStr([Contacts].[Alternate Email Address 1],"#")-1)

But I get "Undefined function Left in expression error ...???
Peter

 

[John Big Booty]

Sorry my bad, for a query criteria you will need;

Left(Forms!YourFomrName!YourTextFieldName, Instr(Forms!YourFomrName!YourTextFieldName,"#")-1)

Your will find that Acces will automagically convert that to;

Left([Forms]![YourFomrName]![YourTextFieldName], Instr([Forms]![YourFomrName]![YourTextFieldName],"#")-1)

Me.ThingName is a reference to a "thing" on the current form.

 

[Peter Ramshaw]

What is the I want to update is in a table? Will the forms! expression still work fore a table?
P

 

[John Big Booty]

That criteria will only work for the current record on a form.
Let me have a play in my sand box and I'll give you a universal criteria that should do the trick.

 

[Peter Ramshaw]

Sorry John, I tried it as an update query on the tabel AND tghe main form I ised and it atill can up with the undefined Left error message ... this is the sytax I used in my update query where tyhe datase I';m using is called MMDATABASE, the main tabe inside it is CONTACTS and the main switchboard form I use is called CONTACT PANEL 2:

Left([Forms]![Contacts Panel 2]![Alternate Email Address 1],InStr([Forms]![Contacts Panel 2]![Alternate Email Address 1],"#")-1)

Still illudes me .... Peter

 

[John Big Booty]

Ok your Update To criteria should be;

Left([FieldName],InStr([FieldName],"#")-1)

 

[Peter Ramshaw]

Used:
Left([Alternate Email Address 1],InStr([Alternate Email Address 1],"#")-1)

Still returned the same error "Undefined left in expression"

Maybe I'm terminal!

Peter

 

[Peter Ramshaw]

Sorry it says "Undefined function 'Left' in expression"

 

[John Big Booty]

Have a look at the date in the table, now have a look at the query in design view. Close the table and run the query and check the changes in the data in the table.

 

[Peter Ramshaw]

Thanks Bob.
This is bizarre to say the least.
I unzipped your test and ran the update and it worked fine.
I copied the code into my upate query with the only change being to the name of the field I want to update. Here is the result:

(see attached pic as I couldn't work out how to embed a picture in this post :-(

Thanks for your patience. There must be something wrong with my Access setting of something ...

Peter

 

[Peter Ramshaw]

Ooops, I meant John, not Bob! Sorry!!!!!@!

 

[John Big Booty]

Ooops, I meant John, not Bob! Sorry!!!!!@!

No worries.

Any way that is truly strange. Do you get the same error if you create a new DB and try using the Left() Function?

 

[Peter Ramshaw]

John
It worked perfectly in a new database.
What does that infer?
Peter