Bloody Irish - Kidding

[Lister]

Hi everyone.

I have hit a wee issue with an Irish name that I have just never even thought of before.

The UserName is "G O'Conner" and I have a bit of VB that filters for a user along these lines

Dim strName as String
Dim strUserName as String

strUserName = "G O'Conner"

'Function to get the users last name
'Returns "O'Conner"
strName = fGetLastName(strUserName)

strSQL = "SELECT inUserID FROM tbl_Contacts WHERE strLastName LIKE '" & strName & "' AND boRetired = FALSE"

Since O'Conner has a single quotation embedded within his name I get an error...

Error Number: 3075 ~ Syntax error (missing operator) in query expression...

A silly little error, has anyone struck this before and can let me know what a work around would be?

Cheers

 

[boblarson]

Just change your code to this:

strSQL = "SELECT inUserID FROM tbl_Contacts WHERE strLastName = " & Chr(34) & strName & Chr(34) & " AND boRetired = FALSE"

Notice I changed your LIKE to = because you weren't using any wild cards anyway which meant that the name had to be exact as you had it.

If you want LIKE so that any part of the name can be used you would need wildcards:

strSQL = "SELECT inUserID FROM tbl_Contacts WHERE strLastName = [B][COLOR=red]*[/COLOR][/B]" & Chr(34) & strName & Chr(34) & "[B][COLOR=red]*[/COLOR][/B] AND boRetired = FALSE"

 

[pbaldy]

Instead of the single quotes, concatenate Chr(34) into the string:

...LIKE " & Chr(34) & strName & Chr(34) & " AND...

 

[boblarson]

Instead of the single quotes, concatenate Chr(34) into the string:

...LIKE " & Chr(34) & strName & Chr(34) & " AND...

Great idea!

But seriously - I have gone to exclusively using Chr(34) instead of using single quotes just to be consistent and that way I don't end up with these "exceptions" which I must then manage. So, most of the time here you'll see me using Chr(34) instead of ' even though the ' would work.

 

[pbaldy]

Great minds think alike!

If you want LIKE so that any part of the name can be used you would need wildcards:

strSQL = "SELECT inUserID FROM tbl_Contacts WHERE strLastName = [B][COLOR=red]*[/COLOR][/B]" & Chr(34) & strName & Chr(34) & "[B][COLOR=red]*[/COLOR][/B] AND boRetired = FALSE"

Wouldn't the wild card have to be inside the Chr(34)? I think that will evaluate to

WHERE strLastName = *"O'Brien"* AND

I don't do a lot of text searching but I would have thought it needed to be

WHERE strLastName Like " & Chr(34) & "*" & strName & "*" & Chr(34) & " AND

so it will evaluate to

WHERE strLastName Like "*O'Brien*" AND

 

[Lister]

Fantastic

Worked a treat, I hate the Irish a lot less now.
Still kidding

Thanks very much for your help.

 

[pbaldy]

Being half Irish, I had to help.

 

[boblarson]

Wouldn't the wild card have to be inside the Chr(34)?

OOPS - yep.

 

[Dairy Farmer]

Maybe this will help.