HELP WITH DLOOKUP

  • Thread starter Thread starter dk
  • Start date Start date
D

dk

I need help with dlookup function
I intend to build premium calculator. I have table "tblPremium" with
following fields:
FromDays (Number)
TillDays (Number)
PremiumCode (Text)
Premium (Number)

User should imput fields Starting date (Short date), Ending Date ( Short
Date), Territory ( Text) and Option (Text)

Private Sub Option_AfterUpdate()
Dim varPremium
Dim strPremiumCode, strDateDiff
strPremiumCode = Me.Territory & Me.Option
strDateDiff = DateDiff("d", [StartingDate], [EndingDate]) + 1
varPremium = DLookup("Premium", "tblPremium", "PremiumCode= strPremiumCode
AND strDateDiff >= FromDays AND strDateDiff <= TillDays ")
Me.BasicPremium = varPremium

End Sub

Any help will be usefull !
THNX

Damir
 
You need to put your variable names outside of the quotes, in order to pick
up their current values:

DLookup("Premium", "tblPremium", "PremiumCode= '" & strPremiumCode &
"' AND FromDays <= " & strDateDiff & " AND TillDays >= " & strDateDiff)

That assumes that PremiumCode is a text field, while FromDays and TillDays
are numeric fields.

Exagerated for clarity, that's

DLookup("Premium", "tblPremium", "PremiumCode= ' " & strPremiumCode &
" ' AND FromDays <= " & strDateDiff & " AND TillDays >= " & strDateDiff)
 
Thanks Douglas,
it works excellent !

Damir
Douglas J. Steele said:
You need to put your variable names outside of the quotes, in order to pick
up their current values:

DLookup("Premium", "tblPremium", "PremiumCode= '" & strPremiumCode &
"' AND FromDays <= " & strDateDiff & " AND TillDays >= " & strDateDiff)

That assumes that PremiumCode is a text field, while FromDays and TillDays
are numeric fields.

Exagerated for clarity, that's

DLookup("Premium", "tblPremium", "PremiumCode= ' " & strPremiumCode &
" ' AND FromDays <= " & strDateDiff & " AND TillDays >= " & strDateDiff)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



dk said:
I need help with dlookup function
I intend to build premium calculator. I have table "tblPremium" with
following fields:
FromDays (Number)
TillDays (Number)
PremiumCode (Text)
Premium (Number)

User should imput fields Starting date (Short date), Ending Date ( Short
Date), Territory ( Text) and Option (Text)

Private Sub Option_AfterUpdate()
Dim varPremium
Dim strPremiumCode, strDateDiff
strPremiumCode = Me.Territory & Me.Option
strDateDiff = DateDiff("d", [StartingDate], [EndingDate]) + 1
varPremium = DLookup("Premium", "tblPremium", "PremiumCode= strPremiumCode
AND strDateDiff >= FromDays AND strDateDiff <= TillDays ")
Me.BasicPremium = varPremium

End Sub

Any help will be usefull !
THNX

Damir
Click to expand...
Click to expand...
 
Back
Top