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_DES C
Set rst = Me.RecordsetClo ne
' move to a semi-random record in the clone
rst.MoveLast
rst.MovePreviou s
rst.MovePreviou s
rst.MovePreviou s
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 = " & """" & DelimitDblQuote s(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]", "FUNDINGNEE D", strCriteria)
If Not IsNull(varRetur n) 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 DelimitDblQuote s(strValue As String) As String
Dim strDblQuote As String
strDblQuote = """"
' replace a double quote with 2 double quotes
DelimitDblQuote s = Replace(Nz(strV alue, 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
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_DES C
Set rst = Me.RecordsetClo ne
' move to a semi-random record in the clone
rst.MoveLast
rst.MovePreviou s
rst.MovePreviou s
rst.MovePreviou s
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 = " & """" & DelimitDblQuote s(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]", "FUNDINGNEE D", strCriteria)
If Not IsNull(varRetur n) 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 DelimitDblQuote s(strValue As String) As String
Dim strDblQuote As String
strDblQuote = """"
' replace a double quote with 2 double quotes
DelimitDblQuote s = Replace(Nz(strV alue, 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
Comment