.Filter = badString

[Ripper]

.Filter = "EXISTS(SELECT tblInmates.InmateId FROM tblInmates " _
& "WHERE (tblInmates.LstName LIKE *'" &
[Forms]![frmLockAllocations]![cboNameLookup] & "'*) AND " _
& "(tblInmates.InmateId = tblLockAllocations.InmateId))"

Compiler does not like something about this string; the wild card
characters, I think. Error msg is "You can't assign a value to this object",
although I think that is not true because I have many strings assigned to
..Filter with no problem. Please help...

Many thanks,,

Rip

 

[Dirk Goldgar]

.Filter = "EXISTS(SELECT tblInmates.InmateId FROM tblInmates " _
& "WHERE (tblInmates.LstName LIKE *'" &
[Forms]![frmLockAllocations]![cboNameLookup] & "'*) AND " _
& "(tblInmates.InmateId = tblLockAllocations.InmateId))"

Compiler does not like something about this string; the wild card
characters, I think. Error msg is "You can't assign a value to this
object", although I think that is not true because I have many strings
assigned to .Filter with no problem. Please help...

Hey there, Ripper -

It looks to me like you have your single-quotes (around the object of the
LIKE operator) in the wrong place. Try this:

.Filter = _
"EXISTS(SELECT tblInmates.InmateId FROM tblInmates " & _
"WHERE (tblInmates.LstName LIKE '*" & _
[Forms]![frmLockAllocations]![cboNameLookup] & _
"*') AND (tblInmates.InmateId = tblLockAllocations.InmateId))"

Note, though, that this won't work for someone whose name includes an
apostrophe, like "O'Brian". This version would be safer:

.Filter = _
"EXISTS(SELECT tblInmates.InmateId FROM tblInmates " & _
"WHERE (tblInmates.LstName LIKE " & Chr(34) & "*" & _
[Forms]![frmLockAllocations]![cboNameLookup] & "*" & Chr(34) & _
") AND (tblInmates.InmateId = tblLockAllocations.InmateId))"

 

[Tom van Stiphout]

On Mon, 2 Nov 2009 20:05:26 -0500, "Dirk Goldgar"

Or replace any single-quotes by two single-quotes:
strName = Replace([Forms]![frmLockAllocations]![cboNameLookup], "'",
"''")
.Filter = _
"EXISTS(SELECT tblInmates.InmateId FROM tblInmates " & _
"WHERE (tblInmates.LstName LIKE '*" & _
strName & _
"*') AND (tblInmates.InmateId = tblLockAllocations.InmateId))"

-Tom.
Microsoft Access MVP

.Filter = "EXISTS(SELECT tblInmates.InmateId FROM tblInmates " _
& "WHERE (tblInmates.LstName LIKE *'" &
[Forms]![frmLockAllocations]![cboNameLookup] & "'*) AND " _
& "(tblInmates.InmateId = tblLockAllocations.InmateId))"

Compiler does not like something about this string; the wild card
characters, I think. Error msg is "You can't assign a value to this
object", although I think that is not true because I have many strings
assigned to .Filter with no problem. Please help...

Hey there, Ripper -

It looks to me like you have your single-quotes (around the object of the
LIKE operator) in the wrong place. Try this:

.Filter = _
"EXISTS(SELECT tblInmates.InmateId FROM tblInmates " & _
"WHERE (tblInmates.LstName LIKE '*" & _
[Forms]![frmLockAllocations]![cboNameLookup] & _
"*') AND (tblInmates.InmateId = tblLockAllocations.InmateId))"

Note, though, that this won't work for someone whose name includes an
apostrophe, like "O'Brian". This version would be safer:

.Filter = _
"EXISTS(SELECT tblInmates.InmateId FROM tblInmates " & _
"WHERE (tblInmates.LstName LIKE " & Chr(34) & "*" & _
[Forms]![frmLockAllocations]![cboNameLookup] & "*" & Chr(34) & _
") AND (tblInmates.InmateId = tblLockAllocations.InmateId))"