1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access can't handle Irish names

Discussion in 'Business Applications' started by DKTaber, Apr 14, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    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?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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] & """"
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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)?
     
  4. OBP

    OBP

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

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Well, once again, you did it. I have no idea why it works, but it does (y). Many thanks, and have a good evening.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  7. DKTaber

    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.
     
  8. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916817

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice