Find Record

[Don S]

Using Access 2007 I have created a combo box using the combo box wizard to
find specific records on a form. The wizard created an embeded macro where
Action: SearchForRecord
Arguments: , , First, ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"
Record: First
Where Condition: ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"

It finds all records as selected except for those which have an " ' " in the
name such as Arby's or Ryan's.

In a previous post I was instructed to replace the single apostrophy (')
with double apostrophy ("). However, doing that resulted in the macro not
working at all.

Again thanks for all your help.

 

[Danny J. Lesandrini]

I think the suggestion to "replace a single apostrophy with a double apostrophy"
was misunderstood. What you want is for the evaluated string to have any
embedded apostrophy doubled, that is to say, have two single apostrophy marks
in place of every one aspotrophe mark.

So this ...
"Mark O'Toole"
becomes this ...
"Mark O''Toole"

It may look like the above string has 3 Quote Marks, but in fact, it has two Quote
marks (one at either end) and two apostrophy marks (in the middle).

The way we usually accomplish that is with the Replace function.

Where Condition: ="[Name] = " & "'" & Replace([Screen].[ActiveControl], "'", "''") & "'"

Hope this is making sense. It's kind of like the "Who's on first" routine.

 

[Don S]

I have entered the code you suggested
="[Name] = " & "'" & Replace([Screen].[ActiveControl],"'",""") & "'"

but I get a dialogue box as follows:
Microsoft can’t parse the expression: ’ ="[Name] = " & "'" &
Replace([Screen].[ActiveControl],"'",""") & "'" ‘


The single apostrophy at the beginning and end were added in the dialogue
box by MS.

Thanks

I think the suggestion to "replace a single apostrophy with a double apostrophy"
was misunderstood. What you want is for the evaluated string to have any
embedded apostrophy doubled, that is to say, have two single apostrophy marks
in place of every one aspotrophe mark.

So this ...
"Mark O'Toole"
becomes this ...
"Mark O''Toole"

It may look like the above string has 3 Quote Marks, but in fact, it has two Quote
marks (one at either end) and two apostrophy marks (in the middle).

The way we usually accomplish that is with the Replace function.

Where Condition: ="[Name] = " & "'" & Replace([Screen].[ActiveControl], "'", "''") & "'"

Hope this is making sense. It's kind of like the "Who's on first" routine.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Using Access 2007 I have created a combo box using the combo box wizard to
find specific records on a form. The wizard created an embeded macro where
Action: SearchForRecord
Arguments: , , First, ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"
Record: First
Where Condition: ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"

It finds all records as selected except for those which have an " ' " in the
name such as Arby's or Ryan's.

In a previous post I was instructed to replace the single apostrophy (')
with double apostrophy ("). However, doing that resulted in the macro not
working at all.

Again thanks for all your help.

 

[Douglas J. Steele]

Try

="[Name] = " & "'" & Replace([Screen].[ActiveControl].Value,"'",""") & "'"

Screen.ActiveControl returns a reference to a control: you want the value
contained in that control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have entered the code you suggested
="[Name] = " & "'" & Replace([Screen].[ActiveControl],"'",""") & "'"

but I get a dialogue box as follows:
Microsoft can't parse the expression: ' ="[Name] = " & "'" &
Replace([Screen].[ActiveControl],"'",""") & "'" '


The single apostrophy at the beginning and end were added in the dialogue
box by MS.

Thanks

I think the suggestion to "replace a single apostrophy with a double
apostrophy"
was misunderstood. What you want is for the evaluated string to have any
embedded apostrophy doubled, that is to say, have two single apostrophy
marks
in place of every one aspotrophe mark.

So this ...
"Mark O'Toole"
becomes this ...
"Mark O''Toole"

It may look like the above string has 3 Quote Marks, but in fact, it has
two Quote
marks (one at either end) and two apostrophy marks (in the middle).

The way we usually accomplish that is with the Replace function.

Where Condition: ="[Name] = " & "'" & Replace([Screen].[ActiveControl],
"'", "''") & "'"

Hope this is making sense. It's kind of like the "Who's on first"
routine.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Using Access 2007 I have created a combo box using the combo box wizard
to
find specific records on a form. The wizard created an embeded macro
where
Action: SearchForRecord
Arguments: , , First, ="[Name] = " & "'" & [Screen].[ActiveControl] &
"'"
Record: First
Where Condition: ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"

It finds all records as selected except for those which have an " ' "
in the
name such as Arby's or Ryan's.

In a previous post I was instructed to replace the single apostrophy
(')
with double apostrophy ("). However, doing that resulted in the macro
not
working at all.

Again thanks for all your help.

 

[John W. Vinson]

Using Access 2007 I have created a combo box using the combo box wizard to
find specific records on a form. The wizard created an embeded macro where
Action: SearchForRecord
Arguments: , , First, ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"
Record: First
Where Condition: ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"

It finds all records as selected except for those which have an " ' " in the
name such as Arby's or Ryan's.

In a previous post I was instructed to replace the single apostrophy (')
with double apostrophy ("). However, doing that resulted in the macro not
working at all.

Again thanks for all your help.

The problem is that the apostrophe is being seen as the closing singlequote
for the string argument. Try using " as a delimiter instead of '. The trick is
that in order to insert a doublequote in a string delimited by doublequotes
you have to use a double doublequote (how's THAT for doubletalk!). Try

Where Condition: ="[Name] = """ & [Screen].[ActiveControl] & """"

That's three doublequotes after the [Name] =, and four at the end.

 

[Don S]

John, THANKS, your solution worked!

Using Access 2007 I have created a combo box using the combo box wizard to
find specific records on a form. The wizard created an embeded macro where
Action: SearchForRecord
Arguments: , , First, ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"
Record: First
Where Condition: ="[Name] = " & "'" & [Screen].[ActiveControl] & "'"

It finds all records as selected except for those which have an " ' " in the
name such as Arby's or Ryan's.

In a previous post I was instructed to replace the single apostrophy (')
with double apostrophy ("). However, doing that resulted in the macro not
working at all.

Again thanks for all your help.

The problem is that the apostrophe is being seen as the closing singlequote
for the string argument. Try using " as a delimiter instead of '. The trick is
that in order to insert a doublequote in a string delimited by doublequotes
you have to use a double doublequote (how's THAT for doubletalk!). Try

Where Condition: ="[Name] = """ & [Screen].[ActiveControl] & """"

That's three doublequotes after the [Name] =, and four at the end.