String Criteria To Filter Form

[Alan B.]

I'm trying to open a form from a command button on a form that is already
open. Since there is no matching record ID, I'm using the LastName and
FirstName.

stLinkCriteria = "[LastName]=" & "'" & Me![TEMPLastName] & "'" And
"[FirstName]=" & "'" & Me![TEMPFirstName] & "'"

Unfortunately I'm not delimiting the string properly. How can I do this?

Regards,
Alan

 

[Douglas J. Steele]

stLinkCriteria = "[LastName]='" & Me![TEMPLastName] & _
"' And [FirstName]='" & Me![TEMPFirstName] & "'"

Exagerated for clarity, that's

stLinkCriteria = "[LastName]= ' " & Me![TEMPLastName] & _
" ' And [FirstName]= ' " & Me![TEMPFirstName] & " ' "

Note, though, that that will fail on names with apostrophes in them
(O'Reilly).

The following code doesn't have that limitation (although it will fail on
names with double quotes in them):

stLinkCriteria = "[LastName]=""" & Me![TEMPLastName] & _
""" And [FirstName]=""" & Me![TEMPFirstName] & """"

Again, exagerated for clarity, that's

stLinkCriteria = "[LastName]= " " " & Me![TEMPLastName] & _
" " " And [FirstName]= " " " & Me![TEMPFirstName] & " " " "

 

[Clifford Bass]

Hi Alan,

As Douglas says, both of those will fail if the name contains the
delimited. I deal with that by using this function:

===============================================

Public Function MakeQuoted(ByVal varValue As Variant) As String
' Function takes a variant and returns a string which is a quoted
version of the variant;
' that is, it starts with a quote character ("), then copies the input
variant, replacing
' all of the quote characters (") with double-quote characters ("") and
then ending the
' string with a quote character.

If IsNull(varValue) Then
MakeQuoted = """"""
Else
MakeQuoted = """" & Replace$(CStr(varValue), """", """""") & """"
End If

End Function

============================================

Your statement then becomes:

stLinkCriteria = "[LastName]=" & MakeQuoted(Me![TEMPLastName]) & " And
[FirstName]=" & MakeQuoted(Me![TEMPFirstName])

Note that the MakeQuoted() function will turn any type of object,
numbers, dates, etc. into a string if at all possible. But, of course this
does not deal with nulls. If you have to deal with them you will have to
adjust your statement accordingly.

Clifford Bass

stLinkCriteria = "[LastName]='" & Me![TEMPLastName] & _
"' And [FirstName]='" & Me![TEMPFirstName] & "'"

Exagerated for clarity, that's

stLinkCriteria = "[LastName]= ' " & Me![TEMPLastName] & _
" ' And [FirstName]= ' " & Me![TEMPFirstName] & " ' "

Note, though, that that will fail on names with apostrophes in them
(O'Reilly).

The following code doesn't have that limitation (although it will fail on
names with double quotes in them):

stLinkCriteria = "[LastName]=""" & Me![TEMPLastName] & _
""" And [FirstName]=""" & Me![TEMPFirstName] & """"

Again, exagerated for clarity, that's

stLinkCriteria = "[LastName]= " " " & Me![TEMPLastName] & _
" " " And [FirstName]= " " " & Me![TEMPFirstName] & " " " "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to open a form from a command button on a form that is already
open. Since there is no matching record ID, I'm using the LastName and
FirstName.

stLinkCriteria = "[LastName]=" & "'" & Me![TEMPLastName] & "'" And
"[FirstName]=" & "'" & Me![TEMPFirstName] & "'"

Unfortunately I'm not delimiting the string properly. How can I do this?

Regards,
Alan