Dlookup function 1

[sabavno]

I was wondering if I could setup an "IF" condition in the DLookup function. This is how it looks now.

=DLookUp("RecoveryCharges","Configuration","ModelID=Forms!ViewBb!ModelID&quot


But I have a condition. If "US" check box on the form is checked I want DLookup function to look up field "RecoveryChargesUS" and if "US" check box is not checked then I want to look up "RecoveryCharges" as it is in the above line of code

Please help me with this

Thanks

 

[scriverb]

This is the code you should use. The IIF function statement is a conditional IF. If the expression is true(1st parameter) then return the second parameter, otherwise return the third parameter. Give this a try.

=DLookUp(IIF(Forms!ViewBb![US],"RecoveryChargesUS", "RecoveryCharges","Configuration","ModelID=Forms!ViewBb!ModelID&quot

Good luck.


Bob Scriver

 

[scriverb]

This is the code you should use. The IIF function statement is a conditional IF. If the expression is true(1st parameter) then return the second parameter, otherwise return the third parameter. Give this a try.

=DLookUp(IIF(Forms!ViewBb![US],"RecoveryChargesUS", "RecoveryCharges&quot,"Configuration","ModelID=Forms!ViewBb!ModelID&quot

Good luck.


Bob Scriver

 

[scriverb]

Sorry about the double post. The second one has the missing &quot" in it. So, ignore the first post. Bob Scriver

 

[sabavno]

Thanks a lot Bob,
It worked just fine
Good luck

 

[GummowN]

I posted before when someone was talking about DMax but here is the Dlookup equivalent. By which I mean a replacement function that runs quicker then Dlookup over linked tables.

Function tLookup(pstrField As String, pstrTable As String, Optional pstrCriteria As String) As Variant
On Error GoTo tLookup_Err
' Function tLookup
' Purpose: Replace tlookup, which is slow on attached tables
' For where you can't use Tbtlookup() if there's more
' than one field in the criteria or field is not indexed.
' Created: 9 Jan 1996 T.Best
' Mod 1 Feb 1996 T.Best - Error Trapping brought in line
' with this procurement system.

Dim dbCurrent As Database
Dim rstLookup As Recordset
Dim varValue As Variant

Set dbCurrent = DBEngine(0)(0)
If pstrCriteria = "" Then
Set rstLookup = dbCurrent.OpenRecordset("Select " & pstrField & " From " & pstrTable, DB_OPEN_SNAPSHOT)
Else
Set rstLookup = dbCurrent.OpenRecordset("Select " & pstrField & " From " & pstrTable & " Where " & pstrCriteria, DB_OPEN_SNAPSHOT)
End If
If Not rstLookup.BOF Then
rstLookup.MoveFirst
varValue = rstLookup(0)
Else
varValue = Null
End If
rstLookup.Close
tLookup = varValue

tLookup_Exit:
On Error Resume Next
rstLookup.Close
Exit Function

tLookup_Err:
Select Case Err
Case Else
End Select

' Retry/Abort/Ignore
Select Case MsgBox(Error, MB_ABORTRETRYIGNORE Or MB_ICONEXCLAMATION, "Error " & Err)
Case IDABORT
Resume tLookup_Exit
Case IDRETRY
Resume
Case IDIGNORE
Resume Next
End Select
Exit Function
End Function