Dlookup On Stings Containing An Apostrophe Or Quote 2

[DevelopV]

Dlookup on strings that contain an apostrophe (') or a quote (") results in an error!

eg
strCtriteria = "Apple"
ProductId = dlookup("ProductId","tblProduct","Product = '" & strCtriteria & "'") works

but if
strCtriteria = "Apple's"
ProductId = dlookup("ProductId","tblProduct","Product = '" & strCtriteria & "'") fails - syntax error (missing operator) in query expression......

Is it only apostrophe (') and quote (") that I need worry about?

I never know the position of the apostrophe (') or quote (") in the string!

How do I handle strings that may contain an apostrophe (') or a quote (")?

Thanks in advance

 

[TheAceMan1]

How are ya DevelopV . . .

Try:

[blue]strCtriteria = Replace("Apple's", "'", "''")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DevelopV]

Do I only have to worry about apostrophe?

Do I have to worry about quotes?

 

[TheAceMan1]

DevelopV . . .

I've never seen or heard of a quotation being used in a name. However ... incase you do have this problem the following should do:

[blue]strCtriteria = Replace(Replace("Apple's", "'", "''"), """", "''")[/blue]

You have this problem because [blue]apostrophe & quote[/blue] are control characters in access that are used in pairs. Its the oddball that confuses parsing and causes errors. When you want to include them in text you need to use two ... (Apple[red]''[/red]s instead of Apple[red]'[/red]s) or (Apple[red]""[/red]s instead of Apple[red]"[/red]s). This is what the dual [blue]Replace[/blue] function does. It replaces the apostrophe or quote with two apostrophe's. Note that the source of the value doesn't change ... its the strCriteria that does ... and this is proper.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DevelopV]

My problem is that users can include both apostrophe & quote in a text field. They may want to include these in, for example, a product description. In certain instances I need to do a dlookup using the field.

 

[PHV]

A safe way:

ProductId = DLookup("ProductId", "tblProduct", "Product='" & Replace(strCtriteria, "'", "''") & "'")

Don't worry for double quote with the above

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

DevelopV . . .

The function takes care of both. Just have modify slightly to take care of double quotations. Try the following:

[blue]strCriteria = Replace(Replace(strCriteria, "'", "''"), """", """""")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]