Very geeky humor

[Push 0]

Microsoft sucks. They just do. Proof follows, just read the comments:

Public Sub FindWithVariables()

Dim rst As ADODB.Recordset

Dim strCriteria As String

Dim strName As String


Set rst = New ADODB.Recordset

strName = "Alfreds Futterkiste"


' This will give error 3001:

' strCriteria = "[CompanyName] = strName"

' So will this:

' strCriteria = "[CompanyName] = " & strName

' This worked in DAO, but doesn't in ADO, because ADO

' doesn't allow double-quotes for quoting

' strCriteria = "[CompanyName] =""" & strName & """"

' This works -- unless your string contains

' an apostrophe!

strCriteria = "[CompanyName]='" & strName & "'"


rst.Open "tblCustomers", CurrentProject.Connection, _

adOpenKeyset, adLockOptimistic, adCmdTable

rst.Find strCriteria

If Not rst.EOF Then

Debug.Print rst.Fields("CustomerID")

End If


' Now let's try an apostrophe:

strName = "La corne d'abondance"


' The previous solution gives error 3001 again,

' because of the embedded apostrophe

' strCriteria = "[CompanyName]='" & strName & "'"

' But we can double the embedded apostrophe:

strCriteria = "[CompanyName]=" & _

adhHandleQuotes(strName)


rst.Find strCriteria

If Not rst.EOF Then

Debug.Print rst.Fields("CustomerID")

End If


' What about two apostrophes?

strName = "Joe's and Ed's Grocery"


' This gives error 3001...ADO only understands

' ONE doubled apostrophe

strCriteria = "[CompanyName]=" & adhHandleQuotes(strName)

rst.Find strCriteria


' This is undocumented, but it works:

strCriteria = "[CompanyName]=#" & strName & "#"


rst.Find strCriteria

If Not rst.EOF Then

Debug.Print rst.Fields("CustomerID")

End If


    ' But what about this one...

strCriteria = "Joe's #9 Winner's Grill"

' You're sunk! There's no way to delimit this variable

' so that ADO can find it. More than one apostrophe, or

' any "#" within the string makes it impossible

' for ADO to find this string.

rst.Close

Set rst = Nothing

End Sub

Looks like M$ will not use the proper solution (escape character, \ in C) if it kills them, just because it's generic.



EDITED TO ADD: You can't just double quote all of your apostrophies either, because ADO only lets you do that ONCE. Just ONCE. Why, you ask? That's what I would like to know.

-- Toggle Whippin' Yahoo
Skydiving is easy. All you have to do is relax while plummetting at 120 mph from 10,000' with nothing but some nylon and webbing to save you.

[QuickDraw 0]

Man.....i'm crying......

-- Hope you don't die. --

I'm fucking winning

[indyz 1]

Quote

Looks like M$ will not use the proper solution (escape character, \ in C) if it kills them, just because it's generic.



EDITED TO ADD: You can't just double quote all of your apostrophies either, because ADO only lets you do that ONCE. Just ONCE. Why, you ask? That's what I would like to know.

MS Basics have never had a proper escape character, and I just can't figure out why. Having to triple-doublequote a string just to start it with a quotation mark is F'in impossible to read.

strSomeVar = """Doesn't make sense"""
strAnotherVar = "\"Makes better sense, and I don't need my glasses!\""
strCoolLanguagesGrokSingleQuotes = '"Perfect."'

[Push 0]

The best is:

strOneQuotationMark = """"

-- Toggle Whippin' Yahoo
Skydiving is easy. All you have to do is relax while plummetting at 120 mph from 10,000' with nothing but some nylon and webbing to save you.

[Schroeder 0]

wwwhhhhhooooossssshHhHH!!!

[Zeemax 0]

i like pie too.... LOL!

Phoenix Fly - High performance wingsuits for skydiving and BASE
Performance Designs - Simply brilliant canopies

[Push 0]

'lo Bob, you've pie?

When come back, bring pie! Wanker!

-- Toggle Whippin' Yahoo
Skydiving is easy. All you have to do is relax while plummetting at 120 mph from 10,000' with nothing but some nylon and webbing to save you.