Question Hash symbol in Access 2010

[John Big Booty]

I'm guessing corruption in your DB. You could try do a Compact and Repair on your DB, failing that you will need to start again, unfortunately.

 

[Peter Ramshaw]

Aaargghghhh! Don't say that!!!!
I repaired etc and it's no different.
Couldn't it be that the function is missing? (Not that I know how to find out and/or replace it if it is).
P

 

[John Big Booty]

This article might give you an alternative to starting again from scratch.

 

[boblarson]

It could be suffering from a reference problem. Try DECOMPILING it and then COMPILE again.

To DECOMPILE:

1a) Create a shortcut with the following
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "d:\My Documents\access\mayapp.mdb" /decompile
As appropriate for your environment of course. Note that if your path to your MDB contains a space it must be enclosed in double quotes just as the MSACCESS.EXE component. You can create a short cut by right clicking in the directory of your choice, highlighting new and creating
or
1b) Create a one line .bat or .cmd line with the above text in it it using notepad.
Hint: Right click in your directory of choice, highlight new and select Text Document. You will be presented with a new file named "New Text Document.txt" or similar. Rename that file to something like "decompile.bat". Then right click that file and select edit. Notepad should then run opening up that file.

2) Click on the short cut or click on the .bat or .cmd file and, in Access 97, you'll get the message "... has converted the code in ... to your current version of Visual Basic". This message does not appear in Access 2000 or newer. Hold the shift key down when you click on Okay so the MDBs autoexec macro won't execute or the start-up form will not be opened. If you have any class modules exit the MDB and re-enter it. This will keep the class modules intact.
3) Now compact the MDB to clean out the old VBA code..
4) Hit Ctrl+G to open the debug/immediate window or go into any module and click on Debug. If Access 97 click on Compile and Save All Modules. If Access 2000 or newer click on Compile. Compiling is particularly important to see if there were any problems and for performance reasons.
5) Then compact again.

 

[Peter Ramshaw]

Bab Larson, you my friend are a bloody genius!!!!
I did as you've described and John's code ran perfectly!
Well done and thank you both.
Now, just one other small thing before I run this on my 'real' database.
If you look back thjrough the thread (hope you're following too John) I wanted to remove everything from the first # synbol onwards. The code now does this effectively.
Trouble is, judging from John's test moduke, if I run this on my (say) "Work Email Address" field, it lookms like ti will delete entirlkey any addres sthat does not have a # in it; ie. the oidd few records where there is just an email address and not the double up I described prior - ie like this james@abc.net.com.au rather than james@abc.net.com.au#james@abc.net.com.au#
The short question is, what do I have to do to prevent the non-stuffed email addresses from not being deleted by John's string?

Cheers
Peter R

 

[John Big Booty]

If you put the following in the Criteria of your Update query only those records that contain a "#" will be updated;

InStr([YourFieldName],"#")<>"0"

 

[Peter Ramshaw]

Thanks John, so is this the crietria I need?

Left([Alternate Email Address 1],InStr([Alternate Email Address 1],"#")<>"0")

or would it be:

Left([Alternate Email Address 1],InStr([Alternate Email Address 1],"#")<>"0")-1)

I ask as the first one deleted everything in the field :-( luckily I only used a few test records.

 

[John Big Booty]

Your Update clause will be;

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

whilst the Criteria will be;

InStr([YourFieldName],"#")<>"0"

 

[John Big Booty]

Have a look at the data in the table, and the structure of the query, then run the query and check the changes to the data in the table.

 

[Peter Ramshaw]

At last, John. I got it to work! Thank you so much and thanks too to Bob for recompiling which must have assisted as well.
Phew! That's one thiong off my mind.,
Kudos to you both.
Peter R

 

[John Big Booty]

Glad you got it sorted