String with "'" in Customer name

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I'm building a where clause with this syntax:

"CNME LIKE '" & Forms![frmLookup]![cboCustName] & "*' "

The Customer name has an apostrophe in it and that's messing up the syntax.
Customer name might be like Sam's Club.

How do I handle this.

Thanks.
 
& Replace(Forms!frmLookup!cboCustName, "'", "''") &

That's a single quote between two double quotes in the second argument to
the Replace function, and two single quotes between two double quotes in the
third argument, i.e. replace each single quote in the original value with
two single quotes.
 
I'm building a where clause with this syntax:

"CNME LIKE '" & Forms![frmLookup]![cboCustName] & "*' "

The Customer name has an apostrophe in it and that's messing up the syntax.
Customer name might be like Sam's Club.

How do I handle this.

Thanks.
Click to expand...

Another way is to use " as a delimiter rather than ' (if your names
can be counted on not to contain " of course). To include a
doublequote in a doublequote delimited string, double the doublequote
(how's THAT for doubletalk!):

"CNME LIKE """ & Forms![frmLookup]![cboCustName] & "*"""

John W. Vinson[MVP]
 
WOW! Thanks!

John Vinson said:
I'm building a where clause with this syntax:

"CNME LIKE '" & Forms![frmLookup]![cboCustName] & "*' "

The Customer name has an apostrophe in it and that's messing up the syntax.
Customer name might be like Sam's Club.

How do I handle this.

Thanks.
Click to expand...

Another way is to use " as a delimiter rather than ' (if your names
can be counted on not to contain " of course). To include a
doublequote in a doublequote delimited string, double the doublequote
(how's THAT for doubletalk!):

"CNME LIKE """ & Forms![frmLookup]![cboCustName] & "*"""

John W. Vinson[MVP]
Click to expand...
 
I don't find the replace function...?

Is it a standard function in Access 2000?


Brendan Reynolds said:
& Replace(Forms!frmLookup!cboCustName, "'", "''") &

That's a single quote between two double quotes in the second argument to
the Replace function, and two single quotes between two double quotes in the
third argument, i.e. replace each single quote in the original value with
two single quotes.

--
Brendan Reynolds

SAC said:
I'm building a where clause with this syntax:

"CNME LIKE '" & Forms![frmLookup]![cboCustName] & "*' "

The Customer name has an apostrophe in it and that's messing up the
syntax.
Customer name might be like Sam's Club.

How do I handle this.

Thanks.
Click to expand...
Click to expand...
 
Yes it is.

There was an issue in the original release of Access 2000 though, where you
could not call Replace directly from a query. You had to create a custom VBA
function that called Replace and call your custom function from the query.
This is no longer necessary in later versions of Access, and I *think* it is
not necessary in Access 2000 if you have all the service releases for Access
and Jet installed, but I'm not 100% certain of that last point.

--
Brendan Reynolds


SAC said:
I don't find the replace function...?

Is it a standard function in Access 2000?


Brendan Reynolds said:
& Replace(Forms!frmLookup!cboCustName, "'", "''") &

That's a single quote between two double quotes in the second argument to
the Replace function, and two single quotes between two double quotes in the
third argument, i.e. replace each single quote in the original value with
two single quotes.

--
Brendan Reynolds

SAC said:
I'm building a where clause with this syntax:

"CNME LIKE '" & Forms![frmLookup]![cboCustName] & "*' "

The Customer name has an apostrophe in it and that's messing up the
syntax.
Customer name might be like Sam's Club.

How do I handle this.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top