How to use DLookup with string variable Criteria

[JrMontgom]

I am trying to use DlookUp where the criteria is a string expression.


The table I am using is tblCEFNames with fields [FundCompare], string;[PercentPortFolio] as Number and [ID] AutoNumber field

Dim strFName As String
Dim intPercent As Integer
Dim strFundComp As String
Dim strCrit As String
Dim intID As Integer

intID = txtSubFundName.Column(0)
'Debug.Print Forms!frmCEFList.Form![frmCEFNames subform]!SubFundName.Value
'Get Fund name from combobox  column 1
strFName = txtSubFundName.Column(1)


'Create Criteria string 
strCrit = "[ID] = " & intID & ""


'ERROR Runtime error '94' Invalid Use of Null
strFundComp = DLookup("[FundCompare]", "tblCefNames", "[ID] = 1")
'same error:(
strFundComp = DLookup("[FundCompare]", "tblCEFnames", strCrit)


intPercent = DLookup("[PercentPortfolio]", "tblCEFnames", strCrit)

I would really appreciate any help on this problem as It sure looked easy in the Help file but the examples skirted using a string criteria.

[June7]

What you have should work. What happens?

However, those variables cannot handle Null. If DLookup does not find a match, returns Null. Might want to handle possibility.


intPercent = Nz(DLookup("[PercentPortfolio]", "tblCEFnames", strCrit), 0)