dlookup Multiple Criteria

[Guest]

I am having problems with the syntax of the following:

SD1: Nz(IIf([PolType]="19" Or [Poltype]="10" Or [PolType]="11" Or
[Poltype]="27" Or [PolType]="28",DLookUp("[SD]","[TblCharges]","[Class] =" &
[PolType] And [TransType]='"
&[TransType]"'),[Premium]*DLookUp("[SD]","[TblCharges]","[Class] =" &
[PolType])/100))

Class and Poltype and both numeric
TransType is text

Would appreciate some help. Thanks
David

 

[Van T. Dinh]

Try:

SD1: Nz(
IIf( ([PolType]=19) Or ([Poltype]=10) Or ([PolType]=11) Or
([Poltype]=27) Or ([PolType]=28),
DLookUp("[SD]","[TblCharges]",
"[Class] =" & [PolType] & " And [TransType]='" & [TransType] &
"'"),
[Premium]*DLookUp("[SD]","[TblCharges]","[Class] =" & [PolType])/100
) )

 

[Guest]

Thank you, that worked perfectly

Try:

SD1: Nz(
IIf( ([PolType]=19) Or ([Poltype]=10) Or ([PolType]=11) Or
([Poltype]=27) Or ([PolType]=28),
DLookUp("[SD]","[TblCharges]",
"[Class] =" & [PolType] & " And [TransType]='" & [TransType] &
"'"),
[Premium]*DLookUp("[SD]","[TblCharges]","[Class] =" & [PolType])/100
) )

--
HTH
Van T. Dinh
MVP (Access)

I am having problems with the syntax of the following:

SD1: Nz(IIf([PolType]="19" Or [Poltype]="10" Or [PolType]="11" Or
[Poltype]="27" Or [PolType]="28",DLookUp("[SD]","[TblCharges]","[Class] =" &
[PolType] And [TransType]='"
&[TransType]"'),[Premium]*DLookUp("[SD]","[TblCharges]","[Class] =" &
[PolType])/100))

Class and Poltype and both numeric
TransType is text

Would appreciate some help. Thanks
David

 

[Van T. Dinh]

You're welcome ... Glad to help ...

 

[Guest]

Sorry to trouble you again but I am having the same problem in a different way!
The syntax you gave me last worked perfectly in a query.
What I am now trying to do is use similar multiple criteria in a form when
it opens.

I have

Private Function Calculate_Charges()
Call Lookup_Charges
TransType = Me!TransType
PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType]")
ICLRateNow = DLookup("[ICL]", "[TblCharges]", "[Class] = [PolType]")
GSTRateNow = DLookup("[GST]", "[TblCharges]", "[Class] = [PolType]")
Me!SD.Value = SdRateNow
Me!ICL.Value = (Me!Premium * ICLRateNow) / 100
Me!GST.Value = 0
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100

When I try to add the second criteria which is [Transtype] I get the
following error message.
"The OPenform Action was cancelled".
I tried
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType]" _
and "[Transtype]" = [Transtype]") where both are text.

I will need to apply this to all the calculations i.e, gst and ICL etc.

Any ideas?

Thanks

David




End Function

The call for Lookup Charges is a public function that declares the variables
each time the form opens (I have many forms that use the same function)