Names with Apostrophes

[Redchrome]

Not sure if this is the right sub-forum, but I have a issue with 3 employees who have apostrophes in their names.



I have a table for "Employee List" with a field for last name.
I have a 2022 Uniform Order Table with a matching last name field.
I have an query to merge last first and middle name driven by this "FullName: [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & [Suffix]"
The form to write to the table uses this as its Row Source "SELECT [2022EmployeeNameQuery].FullName, [2022EmployeeNameQuery].[Employee ID] FROM 2022EmployeeNameQuery; "

When selecting the name from the form its not actually writing to the record it should. I know it's an issue with the apostrophes (As they are the only 3 of 219 employees with an issue)

I am "self"-taught in access via trial-and-error mixed with forums and YouTube. How messed up did I make myself, and what can I do to fix this. Thanks in advance for all your help.

--RC

[orange]

The issue you are having is well known. To resolve the issue you have to "escape the quote".

The simplest method to escape single quotes in SQL is to use two single quotes. For example, if you wanted to show the value O’Reilly, you would use two quotes in the middle instead of one.

SELECT 'O''Reilly' AS.......

[ranman256]

pick your poison,
you can either use single quotes, or the double, but not both.
I allow single qutoes only, before running query. I use dbl-quotes to define the strings.
I fix the field via:

replace([field],chr(34),"'")

[Welshgasman]

You can also use Replace() and replace the single quote with two single quotes I believe?

[Redchrome]

Orange, maybe I'm not grasping where since the apostrophe is in the middle of the field, and not concatenated from an "O" field and a "Brien" field. The SQL for the query that merges name and drive the combo box is this.

SELECT EmployeeListTable.[Employee ID], [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & [Suffix] AS FullName
FROM EmployeeListTable
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & [Suffix];

[orange]

Show us the field values you are working with that are causing the issue.

[Redchrome]

Cropped to remove as much personal data as I could and still give what issue is. As seen the apostrophe is part of the data in the name in the field. Thanks again for any help from all comments so far, I am very very green with this, some of the comments i have no clue what they even mean, but appreciate it anyway.

Edit: Not sure why picture didn't upload, trying again.

Edit: Still not posting, may be work security block. Will try from home later.

[Redchrome]

[orange]

name'withquoteinmiddle could be handled using name''withquoteinmiddle

1 single quote gets replaced with 2 single quotes

[Redchrome]

So in the actual last name field I would enter their name as O''Brien (two singles quotes not a double) for example instead of O'Brien?

[orange]

That's the result you need in order to process the apostrophe.
As Paul said, you could use

Replace("name'withquote", "'","''")

[Welshgasman]

No, as that is what you would see all the time, and that is not their name.
You use the replace or whatever, when you are manipulating the names.

[ssanfu]

I have a function is a standard module

Function fConvertQuotesSingle(InputVal)
   fConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

Usage is:

& fConvertQuotesSingle(vLast) & ", " & fConvertQuotesSingle(vFirst) & ", " &

or

& "', '" & Nz(fConvertQuotesSingle(r.Fields("LName")), "")

There are from Queries in code....