Apostrophe Problem

[SpartanAOD]

How do I make this formula work for people with apostrophes in their names, like O'Connor?

=DLookUp("[Current Wage]","On-Call Wages","[Employee]='" & [Forms]![On-Call Records]![Name] & "'")

 

[Logit]

.
Here is a resource hat might help : https://stackoverflow.com/questions...-access-vba-when-the-field-has-a-single-quote

Another suggestion ... for your purposes, why not change the O'Connor ... to ... OConner ?

 

[SpartanAOD]

.
Here is a resource hat might help : https://stackoverflow.com/questions...-access-vba-when-the-field-has-a-single-quote

Another suggestion ... for your purposes, why not change the O'Connor ... to ... OConner ?

I won't be the only one entering data into this database, so I don't want to risk someone forgetting about that rule. Plus, I might have to compare records to other spreadsheets that use the ' in the name.

I'm not sure on how to apply the info in your link. This seems like a major oversight by the folks who created SQL

 

[james_lankford]

something like this maybe

dim temp_string as string 


' replace a single quote with two single quotes 
' yes, its difficult to see 
' but we have double quote single quote double quote 
' and then we have double quote single quote single quote double quote 
temp_string = Replace(  [Forms]![On-Call Records]![Name], "'", "''", 1, , vbTextCompare)


=DLookUp("[Current Wage]","On-Call Wages","[Employee]='" & temp_string & "'")

its not really an oversight by the creators of SQL
its just a limitation of the alphabet

you know that when you use
like
you use an asterisk

example:
select some columns from some table where name like 'jackie*'

but when do you do if the name actually contains a * ?

and in many languages \n means print a new line
and \t means print a tab

but what if you actually want to print \n or \t ?

then you have to do \\n or \\t

and don't even think about regular expressions
there are so many characters with special meaning
regular expressions are so powerful and so useful -- and so freaking difficult LOL

 

[xenou]

If you have names with single quotes you can use this formula:
DLookUp("[Current Wage]","On-Call Wages","[Employee]='" & Replace([Forms]![On-Call Records]![Name],"'","''") & "'")

The the resulting where clause is 'O''Connor', which is the correct way to use a literal string containing a single quote inside of it.

Not sure if that would work if the name had two single quotes in a row, a single quote at the beginning, or a single quote at the end (i.e., ''OConnor, O''Connor, or OConnor'' are all possible failures). I don't think that's a problem.

Nearly all programming languages use a double or single quote to enclose literal strings, and for those that do they all require what is called an escape character to allow for a double or single quote to appear inside a string. So, this wasn't an oversight but simply a common limitation of manipulating raw strings in a programming language that uses a single quotes (or double quotes) to delimit strings. As you see from the previous post, it comes up in syntax for other uses as well, not only for raw strings.

For what it's worth, whenever possible database designers will use employee ID or employee number for lookups, rather than the actual employee name, to avoid problems exactly like this (as well as improve efficiency in large databases, and, most importantly, solve headaches when for example an employee's name changes after a major life event such as marriage or divorce).

Speaking of this, I recently read that Wozniak was employee number 1 at Apple and Job was employee number 2, which made Jobs upset since he wanted to be number one. So he took employee number 0 instead! But the payroll system required a positive integer for employee number, so it didn't really work after all.

 

[SpartanAOD]

If you have names with single quotes you can use this formula:
DLookUp("[Current Wage]","On-Call Wages","[Employee]='" & Replace([Forms]![On-Call Records]![Name],"'","''") & "'")

The the resulting where clause is 'O''Connor', which is the correct way to use a literal string containing a single quote inside of it.

Not sure if that would work if the name had two single quotes in a row, a single quote at the beginning, or a single quote at the end (i.e., ''OConnor, O''Connor, or OConnor'' are all possible failures). I don't think that's a problem.

Nearly all programming languages use a double or single quote to enclose literal strings, and for those that do they all require what is called an escape character to allow for a double or single quote to appear inside a string. So, this wasn't an oversight but simply a common limitation of manipulating raw strings in a programming language that uses a single quotes (or double quotes) to delimit strings. As you see from the previous post, it comes up in syntax for other uses as well, not only for raw strings.

For what it's worth, whenever possible database designers will use employee ID or employee number for lookups, rather than the actual employee name, to avoid problems exactly like this (as well as improve efficiency in large databases, and, most importantly, solve headaches when for example an employee's name changes after a major life event such as marriage or divorce).

Speaking of this, I recently read that Wozniak was employee number 1 at Apple and Job was employee number 2, which made Jobs upset since he wanted to be number one. So he took employee number 0 instead! But the payroll system required a positive integer for employee number, so it didn't really work after all.

Works like a charm! Thank you!

I imported data from a spreadsheet that someone else had made, but didn't have their personnel ID numbers on it. I might have to bite the bullet and look them all up.

That's a neat story about Jobs. Wozniak did all the leg work; he deserved no. 1.