.FindFirst Method

  • Thread starter Thread starter Jeefgeorge
  • Start date Start date
J

Jeefgeorge

The .FindFirst Method should recognize """

i.e.
..FindFirst "[FieldName] =" & """ & strCriteria & """

The above line doesn't find a match when the below line does

..FindFirst "[FieldName] =" & Chr(34) & strCriteria & Chr(34)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...85b4&dg=microsoft.public.access.modulesdaovba
 
This is an endless battle (quoting strings). I wrote the following procedure
that I use in every app. Your syntax would be:

..FindFirst "[Fieldname] = " & RSQ(strCriteria)

I do not use this for date types, but have another canned procdure for those.

======================
Public Function RSQ(y As Variant) As String
'short named version of ReturnSingleQuotedString
'implementation from examples in Q147687
'modified 2/27/2006 - DLT
'return double quotes if null or zero len string passsed ("''""")
Dim strY As String

Const QuoteOne = "'" 'this is double single double quote

On Error GoTo TrapIt

If IsNull(y) Then
RSQ = "''"
Exit Function

ElseIf Len(y) = 0 Then
RSQ = "''"
Exit Function
End If

strY = CStr(y)

If InStr(strY, QuoteOne) = 0 Then
RSQ = QuoteOne & strY & QuoteOne
Exit Function
Else
strY = ReplaceTheStr(strY, "'", "''")
RSQ = QuoteOne & strY & QuoteOne
Exit Function
End If

EnterHere:

Exit Function

TrapIt:
RSQ = ""
Msgbox Err.Number & vbcrlf & Err.Description
Resume EnterHere

End Function

=========================
Function ReplaceTheStr(TextIn, SearchStr, Replacement)

Dim WorkText As String, Pointer As Integer, CompMode As Integer
CompMode = 1 'text non case sensitve, can also put as param and call
On Error GoTo TrapIt

If IsNull(TextIn) Then
ReplaceTheStr = Null
Else
WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer > 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
Loop
ReplaceTheStr = WorkText
End If

EnterHere:
Exit Function
TrapIt:
MsgBox Err.Number & vbCrLf & Err.Description

Resume EnterHere
End Function
======================
 
The correct syntax is:
.FindFirst "[FieldName] = """ & strCriteria & """"

Assuming [FieldName] is a text field.

Getting the quotes right is a hard thing to get right. I struggled with a
lot of different techniques using Chr(34), contstants, variables, etc, and
none of them really worked for me. But then I came up with this "Training
Wheels" approach that finally got me over the problem. I seldom have to use
this technique anymore unless it is something complex, but here is how it
works.

As you know, in Jet SQL, you can use either a single or double quote to
delimit text values. That is not an issue until you run into data that may
have a single quote in it, like last names. (Those Irish are always causing
proglems) I can say that, I are one. So, it is preferrable to use double
qoutes. Now, we also know that to put a double qoute in a string we use two
of them, but we also have to use another one to delimit the string. So here
is what I came up with. First, write the statement using single quotes.
That is pretty straightforward.

.FindFirst "[FieldName] = """ & strCriteria & "'"

(expanded with spaces for readability)
.FindFirst "[FieldName] = ' " & strCriteria & " ' "

Now, just go back and replace each single quote with two quotes:

.FindFirst "[FieldName] = "" " & strCriteria & " "" "

Unexpanded
.FindFirst "[FieldName] = """ & strCriteria & """"
 
Dave,

I have a similar function that I use for wrapping everything. Not only is
the code more readable, it allows me to make sure I don't have any embedded
quotes inside the text that is being wrapped, and takes less keystrokes to do
this than to imbedd the extra quotes in the code.

I've done this by adding a parameter to the function for what I want to wrap
the the information with (#, ", '), and another parameter that replaces
instances of the wrapper that are imbedded in the passed value (this handles
cases where there are imbedded quotes or single quotes).

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



DaveT said:
This is an endless battle (quoting strings). I wrote the following procedure
that I use in every app. Your syntax would be:

.FindFirst "[Fieldname] = " & RSQ(strCriteria)

I do not use this for date types, but have another canned procdure for those.

======================
Public Function RSQ(y As Variant) As String
'short named version of ReturnSingleQuotedString
'implementation from examples in Q147687
'modified 2/27/2006 - DLT
'return double quotes if null or zero len string passsed ("''""")
Dim strY As String

Const QuoteOne = "'" 'this is double single double quote

On Error GoTo TrapIt

If IsNull(y) Then
RSQ = "''"
Exit Function

ElseIf Len(y) = 0 Then
RSQ = "''"
Exit Function
End If

strY = CStr(y)

If InStr(strY, QuoteOne) = 0 Then
RSQ = QuoteOne & strY & QuoteOne
Exit Function
Else
strY = ReplaceTheStr(strY, "'", "''")
RSQ = QuoteOne & strY & QuoteOne
Exit Function
End If

EnterHere:

Exit Function

TrapIt:
RSQ = ""
Msgbox Err.Number & vbcrlf & Err.Description
Resume EnterHere

End Function

=========================
Function ReplaceTheStr(TextIn, SearchStr, Replacement)

Dim WorkText As String, Pointer As Integer, CompMode As Integer
CompMode = 1 'text non case sensitve, can also put as param and call
On Error GoTo TrapIt

If IsNull(TextIn) Then
ReplaceTheStr = Null
Else
WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer > 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
Loop
ReplaceTheStr = WorkText
End If

EnterHere:
Exit Function
TrapIt:
MsgBox Err.Number & vbCrLf & Err.Description

Resume EnterHere
End Function
======================

--
Dave Thompson
Allen, TX
US


Jeefgeorge said:
The .FindFirst Method should recognize """

i.e.
.FindFirst "[FieldName] =" & """ & strCriteria & """

The above line doesn't find a match when the below line does

.FindFirst "[FieldName] =" & Chr(34) & strCriteria & Chr(34)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...85b4&dg=microsoft.public.access.modulesdaovba
Click to expand...
Click to expand...
 
Back
Top