Criteria works for FindFirst but not for Dlookup when text contains

Front end is Access 2002, back end is linked Oracle tables.
My users need to describe things in feet and inches and want to use
the standard ' and " abbrevations.

On a testing form I go to a record with double or single quotes in the
location description and click a button to see if Access can tell me
the ID number and find its way back to the original record.

When the text contains double quotes the FindFirst works but the
Dloookup doesn't.
Why would that be?

This doesn't do any real work, it's just for stepping through in debug
to see what's happening
Private Sub btnFindInClone_Click()
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim strObj As String
Dim strAct As String
Dim strLoc As String
Dim intFY As Integer
Dim strArea As String

strObj = Me.OBJECT_NM
strAct = Me.ACTION_NM
strArea = Me.METRO_AREA
intFY = Me.TARGET_FY
strLoc = Me.LOCATION_DESC


Set rst = Me.RecordsetClone
' move to a semi-random record in the clone
rst.MoveLast
rst.MovePrevious
rst.MovePrevious
rst.MovePrevious
Me.Bookmark = rst.Bookmark
Debug.Print Me.NEED_ID

' This is the unique constraint of the table
strCriteria = "OBJECT_NM = '" & strObj & "'" _
& " and ACTION_NM = '" & strAct & "'" _
& " and LOCATION_DESC = " & """" & DelimitDblQuotes(strLoc) &
"""" _
& " and TARGET_FY = " & intFY _
& " and METRO_AREA = '" & strArea & "'"
Debug.Print strCriteria

' this always returns null if Location Desc contains a double quote,
' but it finds the correct record if Location Desc contains a single
quote or no quotes
varReturn = DLookup("[NEED_ID]", "FUNDINGNEED", strCriteria)
If Not IsNull(varReturn) Then
lngNewNeedID = CLng(varReturn)
Else
MsgBox "not found"
End If

' this always finds the correct record even if single or double quotes
exist in Location Desc
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = rst.Bookmark
End If

rst.Close
Set rst = Nothing

End Sub

Public Function DelimitDblQuotes(strValue As String) As String
Dim strDblQuote As String

strDblQuote = """"

' replace a double quote with 2 double quotes

DelimitDblQuotes = Replace(Nz(strValue, Space(0)), strDblQuote,
strDblQuote & strDblQuote)


End Function

LOCATION_DESC is defined as VARCHAR2(4000) in Oracle. Access considers
it as Memo data type.

The argument "criteria" is defined almost identically for FindFirst
and Dlookup:
FindFirst criteria: A String used to locate the record. It is like
theWHERE clause in an SQL statement, but without the word WHERE.
Dlookup criteria: An optional string expression used to restrict the
range of data on which the DLookup function is performed. For example,
criteria is often equivalent to the WHERE clause in an SQL expression,
without the word WHERE. If criteria is omitted, the DLookup function
evaluates expr against the entire domain. Any field that is included
in criteria must also be a field in domain; otherwise, the DLookup
function returns a Null.

Why doesn't Dlookup work when FindFirst does?
Thanks

Re: Criteria works for FindFirst but not for Dlookup when text contains double quotes

FindFist is a DAO property native to Jet. It will automatically
interpret quotes. Sql and DAO are two different environments.

I was thinking maybe you weren't escaping your double quotes in the sql
effort, but it looks like you are """". The other thing is that ODBC I
don't think does a conversion of PL sql (Oracle sql) to jet sql and the
syntax may be slightly different.

If I were you I would use ADO instead of ODBC for these kind of queries.

To get the connection string for your oracle table - just do a
debug.print on a tableDef object

Set tdf = CurrentDB.TableDefs("yourOracleODBCTable")
Debug.print tdf.Connection.

Then, I forget the complete syntax (haven't used oracle for a while) but
you can find the conection syntax on google for Oracle to OleDB. Then
the syntax for escaping special characters in pl sql is similar to
Transact Sql (sql for MS Sql server)

SELECT ‘This will display line with quote’’s word.’ FROM temp_table;
SELECT ‘This will display ””double quoted”” word.’ FROM temp_table;

and here is how you escape special characters when using the LIKE
keyword (exactly the same as tsql with the same wildcard symbol %
percent)

SELECT guest_name FROM guest_table WHERE name LIKE ‘%\_%’ ESCAPE ‘\’;

You have to include the keyword ESCAPE followed by the character you are
trying to escape.

Just know that if you are linked to ODBC server tables, ODBC does not
perform required conversions to match Jet sql. So when you start using
slightly more sophisticated queries, ODBC will fail more times than not.
ADO, on the other hand, can interpret PL sql, tsql and Jet sql very
nicely.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Criteria works for FindFirst but not for Dlookup when text

On Apr 14, 5:16 pm, Rich P wrote:
> FindFist is a DAO property native to Jet. It will automatically
> interpret quotes. Sql and DAO are two different environments.
>
> I was thinking maybe you weren't escaping your double quotes in the sql
> effort, but it looks like you are """". The other thing is that ODBC I
> don't think does a conversion of PL sql (Oracle sql) to jet sql and the
> syntax may be slightly different.
>
> If I were you I would use ADO instead of ODBC for these kind of queries.
>
> To get the connection string for your oracle table - just do a
> debug.print on a tableDef object
>
> Set tdf = CurrentDB.TableDefs("yourOracleODBCTable")
> Debug.print tdf.Connection.
>
> Then, I forget the complete syntax (haven't used oracle for a while) but
> you can find the conection syntax on google for Oracle to OleDB. Then
> the syntax for escaping special characters in pl sql is similar to
> Transact Sql (sql for MS Sql server)
>
> SELECT 'This will display line with quote''s word.' FROM temp_table;
> SELECT 'This will display ""double quoted"" word.' FROM temp_table;
>
> and here is how you escape special characters when using the LIKE
> keyword (exactly the same as tsql with the same wildcard symbol %
> percent)
>
> SELECT guest_name FROM guest_table WHERE name LIKE '%\_%' ESCAPE '\';
>
> You have to include the keyword ESCAPE followed by the character you are
> trying to escape.
>
> Just know that if you are linked to ODBC server tables, ODBC does not
> perform required conversions to match Jet sql. So when you start using
> slightly more sophisticated queries, ODBC will fail more times than not.
> ADO, on the other hand, can interpret PL sql, tsql and Jet sql very
> nicely.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***


Thanks Rich!
That's a goldmine of info. It's just what I needed to know.
thanks again,
Denise