Apostrophe's causing access to bomb out

R

Ruth Cheesley

Hello, I wonder if you can help me!

I have recently started as an IT Technician in a school and one of my
responsibilities is producing the school reports which are generated by means
of an access database "back end" and word files with macros as a "front end"
(read only).

As I did not design the databases and am not that skilled in the coding side
of things in access, I am a bit out of my depth with one problem we have,
that apparently has been there since the databases were designed and a "bodge
job" is being applied each time.

Basically, the students are added into the database and then put into their
form groups, teaching groups, etc. No problems here.

Teachers then download their reports via a word document with macro's
matching their initials to their classes/teaching groups. No problem here.

When the teachers then upload their results (again via macro in word) to the
access database, we have a problem. Any students such as O'Brien or O'Malley
as random examples, with an apostrophe, cause it to bomb out. It does not
report this to the user, so when we come to collate the reports, anyone after
this point in the report from that teacher is not uploaded.

The "bodge job" I mentioned is that if, before uploading their results into
the database, the teacher simply deletes the apostrophe and then re-enters it
again, it works. Inevitably they never actually do this, and we only find
out near the deadline that the reports are missing by which time, odds on
they have been lost/deleted/etc.

I just wondered if anyone could possibly help me in a language I can
understand rather than a page of sql code as a response to similar questions
(which I don't!) hence why I posted in this forum. I would very much like to
get this sorted as it is somewhat annoying!

I am guessing it is something to do with the database assuming that the
apostrophe is a command, and therefore it stops here, but further than that I
am somewhat stuck, and anything I have found on the web or on these forums
consists of lots of code which isn't really that helpful to me! I have
checked that they are actually being entered intially with the correct
apostrophe which they are.

Any advice greatly appreciated! Please email me on (e-mail address removed)

Thanks,

Ruth
 
J

John W. Vinson

On Mon, 16 Apr 2007 13:24:01 -0700, Ruth Cheesley <Ruth
I just wondered if anyone could possibly help me in a language I can
understand rather than a page of sql code as a response to similar questions
(which I don't!) hence why I posted in this forum. I would very much like to
get this sorted as it is somewhat annoying!

The solution is to change your Word macro so that it delimits the name being
uploaded with " rather than with '. Could you perhaps post the VBA of the Word
macro? It should be pretty easy to send you a quick fix if we can see the
code.

The reason for the error is that it's trying to insert

'O'Brien'

into the table. The apostrophe in the name is seen as the end of the string,
and it's left with a meaningless (to Access) Brien' following the mispercieved
"end". If it were instead inserting

"O'Brien"

it would work fine.

John W. Vinson [MVP]
 
D

Damian S

Hi Ruth,

To get around this, you need to replace a single apostrophe with two single
apostrophe's... eg: O'Brien becomes O''Brien (note that is two single
apostrophe's, not a double quote).

You could either (a) adjust your "upload" macro to use the replace function
to replace the offending apostrophe, or instruct your teachers to enter two
single apostrophe's when entering the data.

Hope this explains the issue and somewhat helps.

Damian.
 
R

Ruth Cheesley

Hello John and Damian,

Thank you both for your responses, I appreciate it. I will take a look at
your suggestions and get back to you tomorrow, as I very much want to get
this sorted out before reports are due .... goodness knows we don't need any
more stress created!

I would rather we fixed the problem rather than have the staff do something
like the double apostrophe so will investigate accordingly.

Regards,

Ruth Cheesley
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top