Apostrophe in filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a filter on a form. The filter works perfectly until a value from the
query that controls the list contained an apostrophe. When selected the error
message "You can't assign a value to this object" appeared. When the
apostrophe is removed from the approporiate tables the error does not occur
and the filter returns to it's previous working state? Anyone come accross
anything like this before?

Please help.

John.
 
Use double-quotes around your strings instead of single quotes, e.g.:
"Surname = ""O'Brien"""

Note that double-quotes within a string have to be doubled up, so VBA knows
it is not the end of the string.
 
Thanks for your reply Allen,

This is what I had:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This is what I have now:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '"" & SurnameFilter & ""'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This has stopped the error message but the filter does not bring up the
appropriate value? Any thoughts Allen?

John.
 
Drop the Requery line. Turning the FilterOn is all that's needed.

If you don't see the expected results, ask Access what's going on.
Open the Immediate window (Ctrl+G).
Enter:
? Forms![Form1].Filter
replacing "Form1" with the name of your form.
Then check that id did turn on:
? Forms![Form1].FilterOn

Before applying a filter, I always force any edits to save:
If Me.Dirty Then Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnC said:
Thanks for your reply Allen,

This is what I had:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This is what I have now:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '"" & SurnameFilter & ""'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This has stopped the error message but the filter does not bring up the
appropriate value? Any thoughts Allen?

John.

Allen Browne said:
Use double-quotes around your strings instead of single quotes, e.g.:
"Surname = ""O'Brien"""

Note that double-quotes within a string have to be doubled up, so VBA
knows
it is not the end of the string.
Click to expand...
Click to expand...
 
Allen,

When I use the following:

Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True

It filters the records appropriately (all except the surnames containing
apostrophes)

When I double up on the brackets the filter returns all records. How would
you double up on the above lines? Am I doing something wrong? The filter is
showing as TRUE in the Immediate Window. Sorry to keep pestering you, this db
has been in use for a year & this has never come up!

Allen Browne said:
Drop the Requery line. Turning the FilterOn is all that's needed.

If you don't see the expected results, ask Access what's going on.
Open the Immediate window (Ctrl+G).
Enter:
? Forms![Form1].Filter
replacing "Form1" with the name of your form.
Then check that id did turn on:
? Forms![Form1].FilterOn

Before applying a filter, I always force any edits to save:
If Me.Dirty Then Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnC said:
Thanks for your reply Allen,

This is what I had:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This is what I have now:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '"" & SurnameFilter & ""'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This has stopped the error message but the filter does not bring up the
appropriate value? Any thoughts Allen?

John.

Allen Browne said:
Use double-quotes around your strings instead of single quotes, e.g.:
"Surname = ""O'Brien"""

Note that double-quotes within a string have to be doubled up, so VBA
knows
it is not the end of the string.

I have a filter on a form. The filter works perfectly until a value from
the
query that controls the list contained an apostrophe. When selected the
error
message "You can't assign a value to this object" appeared. When the
apostrophe is removed from the approporiate tables the error does not
occur
and the filter returns to it's previous working state? Anyone come
accross
anything like this before?

Please help.

John.
Click to expand...
Click to expand...
Click to expand...
 
The clue will be there in the immediate window as to what is wrong. For
example, there may be spurious spaces inside the string.

You could even try pasting the value of the returned Filter into the WHERE
clause in a query to see what's going on.

The doubled up quotes are very simple.
To get the string:
This string has a "word" in quotes.
you must use:
"This string has a ""word"" in quotes."

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnC said:
When I use the following:

Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True

It filters the records appropriately (all except the surnames containing
apostrophes)

When I double up on the brackets the filter returns all records. How would
you double up on the above lines? Am I doing something wrong? The filter
is
showing as TRUE in the Immediate Window. Sorry to keep pestering you, this
db
has been in use for a year & this has never come up!

Allen Browne said:
Drop the Requery line. Turning the FilterOn is all that's needed.

If you don't see the expected results, ask Access what's going on.
Open the Immediate window (Ctrl+G).
Enter:
? Forms![Form1].Filter
replacing "Form1" with the name of your form.
Then check that id did turn on:
? Forms![Form1].FilterOn

Before applying a filter, I always force any edits to save:
If Me.Dirty Then Me.Dirty = False

JohnC said:
Thanks for your reply Allen,

This is what I had:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '" & SurnameFilter & "'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This is what I have now:

Private Sub HaulierFilter_AfterUpdate()
Me.Filter = "[Surname] = '"" & SurnameFilter & ""'"
Me.FilterOn = True
Me.Form.Requery
End Sub

This has stopped the error message but the filter does not bring up the
appropriate value? Any thoughts Allen?

John.

:

Use double-quotes around your strings instead of single quotes, e.g.:
"Surname = ""O'Brien"""

Note that double-quotes within a string have to be doubled up, so VBA
knows
it is not the end of the string.

I have a filter on a form. The filter works perfectly until a value
from
the
query that controls the list contained an apostrophe. When selected
the
error
message "You can't assign a value to this object" appeared. When the
apostrophe is removed from the approporiate tables the error does
not
occur
and the filter returns to it's previous working state? Anyone come
accross
anything like this before?

Please help.

John.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top