Solved: Access can't handle Irish names

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
I design applications in Access 2003 for a non-profit organization. One of the strangest 'bugs' we've encountered over the years is that it often pops up a 'debug' error message on any name containing an apostrophe. For example, O'Brien. If you remove the apostrophe or change it to a quote ("), no error.

The debug error says: "Run-time error '3077'. Syntax error (missing operator) in expression."

The error is caused from a VBA module with the following text:

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastFirst] = '" & Me![Combo48] & "'"
Me.Bookmark = rs.Bookmark
End Sub

The command in red above is the one that's highlighted, and I suspect the problem is with the '" and "'" in the command, but I'm not VBA literate, so don't know what I need to do to fix it so names with apostrophes will work. Can anyone help me with that?
 

OBP

Joined
Mar 8, 2005
Messages
19,896
That is a known Access problem, especially with "Find" Combos.
You could try using multiple ", I think you need 3 in the first part and 4 in the second part.
like this "[StudentName]=""" & Me![Combo22] & """"
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
That is a known Access problem, especially with "Find" Combos.
You could try using multiple ", I think you need 3 in the first part and 4 in the second part.
like this "[StudentName]=""" & Me![Combo22] & """"
Hi, again, OBP. How are things in Wales?

Not sure just 3 or 4 """" will work, because the original VBA contains apostrophes. I.e., note that it says rs.FindFirst "[LastFirst] = '" & Me![Combo48] & "'". The first set is an apostrophe and a single quote; the second is an apostrophe surrounded by quotes. So should I try rs.FindFirst "[LastFirst] = '"" & Me![Combo48] & "'"" (that is, adding a single quote to the end of each punctuation series)?
 

OBP

Joined
Mar 8, 2005
Messages
19,896
The recommended version is
rs.FindFirst "[StudentName]=""" & Me![Combo22] & """"
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
The recommended version is
rs.FindFirst "[StudentName]=""" & Me![Combo22] & """"
Well, once again, you did it. I have no idea why it works, but it does (y). Many thanks, and have a good evening.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Thanks, I forgot to say things over here are not brilliant, our Politicians are killing this country, we have a general election in a few weeks, but there is no real choice, they are all the same money grabbers.
But at leat I and the family are OK at the moment.
How about you.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
The U.S. is in much the same situation. The healthcare bill that just passed will bankrupt the country (it's forecast that by 2020, the U.S. nation debt will be 90-100% of GDP). It used to be that the Republican party would restrain spending, but that principal died years ago. So like you, we have no real choice. Times are fine at the moment for my wife and I, who are both on Social Security and Medicare. But both of those are underfunded and will have to be drastically cut in the future. The future looks pretty bleak -- worldwide.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top