String Criteria To Filter Form

  • Thread starter Thread starter Alan B.
  • Start date Start date
A

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
 
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] & " " " "
 
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

Douglas J. Steele said:
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!)


Alan B. said:
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
Click to expand...
Click to expand...
 
Back
Top