dlookup multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble with the following

Private Function Calculate_Charges()
Call Lookup_Charges
TransType = Me!TransType
PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType] and
_[TransType] = [TransType]")
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 = ((Me!Premium + Me!SD) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End Function

Call LookupCharges is a public function which declares all the variables
I use this on an open form action. The SdrateNow Lookup only finds the first
record in the table based on the [Class] = [PolType] criteria and ignores the
[TransType] = [TransType] criteria. [TransType] is text both on the form and
in the table.

I think I have the syntax wrong but cannot seem to get right.

Would appreciate any help.

Thanks

David
 
New said:
I am having trouble with the following

Private Function Calculate_Charges()
Call Lookup_Charges
TransType = Me!TransType
PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType] and
_[TransType] = [TransType]")
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 = ((Me!Premium + Me!SD) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End Function

Call LookupCharges is a public function which declares all the variables
I use this on an open form action. The SdrateNow Lookup only finds the first
record in the table based on the [Class] = [PolType] criteria and ignores the
[TransType] = [TransType] criteria. [TransType] is text both on the form and
in the table.
Click to expand...


Yes, the syntax is inappropriate. The way you have it, it
is comparing the two fields to themselves, so the criteria
expression is always true.

You need to use the values in the procedure. Assuming the
fields are a numeric type:

SdRateNow = DLookup("[SD]", "[TblCharges]", _
"[Class] = " & [PolType] & _
" And [TransType] = " & [TransType])

IF they're a text type field, then use:

SdRateNow = DLookup("[SD]", "[TblCharges]", _
"[Class] = """ & [PolType] & _
""" And [TransType] = " & [TransType] & """")
 
Hi, David.

I don't know what the underscore is doing in there (perhaps it's been
misplaced when you pasted it?), but _every_ record has the TransType column
equal to the TransType column, so perhaps you intended to compare TransType
to a different column?

In other words, if a record has "aaa" in the TransType column, then
(TransType = TransType) will be true, because "aaa" = "aaa". The logical
comparison will result in true for every record. This is why the (Class =
PolType) is the only criteria that seems to be used, because the other
criteria is always true.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thank you, I can see what I was doing now.
Marshall Barton said:
New said:
I am having trouble with the following

Private Function Calculate_Charges()
Call Lookup_Charges
TransType = Me!TransType
PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType] and
_[TransType] = [TransType]")
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 = ((Me!Premium + Me!SD) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End Function

Call LookupCharges is a public function which declares all the variables
I use this on an open form action. The SdrateNow Lookup only finds the first
record in the table based on the [Class] = [PolType] criteria and ignores the
[TransType] = [TransType] criteria. [TransType] is text both on the form and
in the table.
Click to expand...


Yes, the syntax is inappropriate. The way you have it, it
is comparing the two fields to themselves, so the criteria
expression is always true.

You need to use the values in the procedure. Assuming the
fields are a numeric type:

SdRateNow = DLookup("[SD]", "[TblCharges]", _
"[Class] = " & [PolType] & _
" And [TransType] = " & [TransType])

IF they're a text type field, then use:

SdRateNow = DLookup("[SD]", "[TblCharges]", _
"[Class] = """ & [PolType] & _
""" And [TransType] = " & [TransType] & """")
Click to expand...
 
Thank you - I get it now

'69 Camaro said:
Hi, David.

I don't know what the underscore is doing in there (perhaps it's been
misplaced when you pasted it?), but _every_ record has the TransType column
equal to the TransType column, so perhaps you intended to compare TransType
to a different column?

In other words, if a record has "aaa" in the TransType column, then
(TransType = TransType) will be true, because "aaa" = "aaa". The logical
comparison will result in true for every record. This is why the (Class =
PolType) is the only criteria that seems to be used, because the other
criteria is always true.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


New TO Access said:
I am having trouble with the following

Private Function Calculate_Charges()
Call Lookup_Charges
TransType = Me!TransType
PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType] and
_[TransType] = [TransType]")
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 = ((Me!Premium + Me!SD) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End Function

Call LookupCharges is a public function which declares all the variables
I use this on an open form action. The SdrateNow Lookup only finds the first
record in the table based on the [Class] = [PolType] criteria and ignores the
[TransType] = [TransType] criteria. [TransType] is text both on the form and
in the table.

I think I have the syntax wrong but cannot seem to get right.

Would appreciate any help.

Thanks

David
Click to expand...
Click to expand...
 
Back
Top