Handling employees with apostrophe in name (O'Leary) in reports

I’m looking for some help with syntax in a SQL script. I working with a restaurant that has an employee with an apostrophe in their name (O’Leary). When I try to run an report from a script with the variable {Username} it throws an error. I tried using the syntax (O.CreatingUserName = Left(’{UserName}, 1) & “’” & Mid(’{UserName}’, 3) but something is wrong with my syntax. Any help would be greatly appreciated.

Take the apostrophy out of their name would be easiest solution :grinning:

I agree, but there is already a lot of data in the database with the apostrophe included. I’m sure there is a way to include the apostrophe if I get the syntax right.

(O.CreatingUserName = Left('{UserName}, 1) & "'" & Mid('{UserName}', 3)

Reports don’t really support that type of syntax.

In any case, you can see by the code highlighter above what might be wrong with the syntax. It should be something like this, though I doubt this will work either:

(O.CreatingUserName = Left('{UserName}', 1) + "'" + Mid('{UserName}', 3))

To get support for full SQL syntax, you will need to compose a query entirely out of SQL and have the Report display the results. I can probably help you with that, if you show the report and expected results. In the WHERE clause, it would be something like this:

CreatingUserName = SUBSTRING(@1, 1, 1) + '''' + SUBSTRING(@1, 3, 99)

Thank you for the response. The report the script is in works fine except for the 1 user with the apostrophe in his name. My current SQL script is:

SELECT O.CreatingUserName, CONVERT(nvarchar, CAST(SUM(O.Price * O.Quantity) AS Money), 1) AS Total, CONVERT(nvarchar, CAST(SUM(O.Price * O.Quantity * 0.08) AS Money), 1) AS Share
dbo.MenuItems AS MI ON MI.Id = O.MenuItemId
WHERE (MI.GroupCode = ‘Beer’ OR
MI.GroupCode = ‘Wine’ OR
MI.GroupCode = ‘Liquor’ OR
MI.GroupCode = ‘Cocktail’ OR
MI.GroupCode = ‘Open Bar’) AND (O.DecreaseInventory = 1) AND (O.CreatedDateTime > ‘{Start}’) AND (O.CreatedDateTime <= ‘{End}’) and (O.CreatingUserName = ‘{Username}’

GROUP BY O.CreatingUserName

Any help would be appreciated.

I took the easy way out and removed the apostrophes from the SQL database and from his user name. It’s working fine now. I would still like to know if it is possible to encase a special character in a report variable.

QMcKay showed you above some possible syntax to use.

I tried your syntax but it still threw an error. It doesn’t like the apostrophe even in the double quotes.

Open Character Map there is a lot of characters like apostrophe use that instead.

It won’t be that simple - I was just showing how to escape a single quote.

Honestly, removing the single quote from the Username is the easiest solution, since you will continue to run into this problem with all your User-based Reports.

1 Like

I agree. No reason to make it harder than it needs to be.