Solved: DLookup record to compare

[Trevor]

I am trying to use a DLookup to compair the value for a field stored in the table on the current record, and my code is only showing the 6th record, I have tried placing it in onCurrent, OnChange, afterupdate and still the same result anyway here is my code, I am trying to filter by the IDNumber for the record being shown at that time
Dim LfollowupComments as string
LfollowupComments = DLookup("[FollowUpComments]", "VMSU-CLT", Me.IDnumber)

the conditions that will be performed don't matter because it does't display the current field data for that record.

[Qubit]

DLookup(what, where, condition)
your statement doesn't have the condition properly set:

LfollowupComments = DLookup("[FollowUpComments]", "VMSU-CLT", Me.IDnumber)

it should probably be:

LfollowupComments = DLookup("[FollowUpComments]", "VMSU-CLT", "[VMSU-CLT.IDnumber]=" & Me.IDnumber)

Make certain the idnumber fieldname above is correct for your VMSU-CLT table.

.q

[Trevor]

Yes that may work (I hav't tried it) I figured out a way last night and was gonna update my post but you beat me to it , thanx
I am using ;
DLookup("[field to retrieve]", "table", "[lookup by field] = """ & lookupByField & """")

[Qubit]

One other note regarding the quotes around the string in the condition:
I ran across the case where the string I was trying to test for had a single quote in it: i.e. O'Brien

When you wrap this in single quotes, it will return the O, stopping after the first single quote.

So, I wrote the following function to handle the quoting of strings:

Public Function WrapQuote(strText as string) as string
Dim strReturn as string
strReturn = Chr$(34) & strText & Chr$(34)
WrapQuote = strReturn
End Function

This way you can term your statement:

DLookup("[field to retrieve]", "table", "[lookup by field] = " & WrapQuote(lookupByField))

Also, I always wrap DLookups in Nz( , ) in case of nulls.

.Q

[Trevor]

Thanks, please check my altternat =sum() post.