Puzzled by DLOOKUP syntax

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

How do I write a DLOOKUP in code which will find the correct
effective date and its load factor, if I can pass as parameters the
ResourceType, ChargeDate and CompanyName?

CurrentLoadFactor = DLOOKUP("LoadFactor", "tblLoadFactor", & _
"[ResourceType] = '" & rs![ResourceType] & "' and " & _
"[CompanyName] = " & rs![CompanyName] & "' and " & _
"[Effective date]
.............??........rs![ChargeDate]...........??.................)

I need to find the closest effective date prior to the ChargeDate, then
return the LoadFactor.

ResourceType EffectiveDate LoadFactor CompanyName
FEF 1/1/02 1.0424 ABC
FEF 1/1/02 1.15788 XYZ
FEF 1/1/03 1.15053 XYZ
FEF 1/1/03 1.0299 ABC
FEF 1/1/04 1.0234 ABC
FEF 1/1/04 1.1461 XYZ
MTS 1/1/02 1.0424 XYZ
MTS 1/1/02 1.0424 ABC
MTS 1/1/03 1.0299 ABC
MTS 1/1/03 1.0299 XYZ
MTS 1/1/04 1.0234 ABC
MTS 1/1/04 1.0234 XYZ

Thanks,
Mich
 
try the following. first you have to make a query which calculate days
between chargeDate and EffectiveDate, so you need to get a record with
lowest value. now you can sort query above by difference, and use dfirst to
get first row (als applying filtering by recouce and company)
 
You can use the DMax() function to return the Max
Effective Date that is less than the Charge Date (and
with matching company name and resource type). The
syntax would be something like the following (watch
wrapping though - and check for typo's).

CurrentLoadFactor = DLookup
("[LoadFactor]", "tblLoadFactor",
"[ResourceType] = '" & rs![ResourceType] & "' AND
[CompanyName] = '" & rs![CompanyName] & "' AND
[EffectiveDate] = #'" & DMax
("[EffectiveDate]", "tblLoadFactor",
"[ResourceType] = '" & rs![ResourceType] & "' AND
[CompanyName] = '" & rs![CompanyName] & "' AND
[EffectiveDate] < #' & rs!ChargeDate & "#") & "#")

Or, if you wanted to make it a little more readable, you
could do the DMax() first and assign to a variable, and
use that in the DLookup().

Note that you could also do what you are trying to do by
using sql to open a recordset and provide the value,
which could be faster than the domain aggregate
functions. But, I wouldn't worry about it unless you
experience some problems with execution speed.

HTH, post back if it doesn't work or if you have any
other questions.

-Ted Allen
-----Original Message-----
How do I write a DLOOKUP in code which will find the correct
effective date and its load factor, if I can pass as parameters the
ResourceType, ChargeDate and CompanyName?

CurrentLoadFactor = DLOOKUP
Click to expand...
("LoadFactor", "tblLoadFactor", & _
"[ResourceType] = '" & rs![ResourceType] & "' and " & _
"[CompanyName] = " & rs![CompanyName] & "' and " & _
"[EffectiveDate]
.............??........rs! [ChargeDate]...........??.................)

I need to find the closest effective date prior to the ChargeDate, then
return the LoadFactor.

ResourceType EffectiveDate LoadFactor CompanyName
FEF 1/1/02 1.0424 ABC
FEF 1/1/02 1.15788 XYZ
FEF 1/1/03 1.15053 XYZ
FEF 1/1/03 1.0299 ABC
FEF 1/1/04 1.0234 ABC
FEF 1/1/04 1.1461 XYZ
MTS 1/1/02 1.0424 XYZ
MTS 1/1/02 1.0424 ABC
MTS 1/1/03 1.0299 ABC
MTS 1/1/03 1.0299 XYZ
MTS 1/1/04 1.0234 ABC
MTS 1/1/04 1.0234 XYZ

Thanks,
Mich


.
Click to expand...
 
I am running this in code, I am creating a temporary table for a very
complex report. I have used a CreateQueryDef to create a recordset and am
stepping through the records one by one and adding information to this new
table, summaries and so on based on data in the query which is already very
complex.

Now as I step through the records I need to find a Load Factor, multiply by
a charge amount and add it to a category which is in the temp table, and on
the report.

I am hoping for a DLOOKUP, not another query.
If the charge date is 1 May 04 I need the effective date of 1/1/04
If the charge date is 12 Feb 02 I need the effective date of 1/1/02, etc

Thanks,
Mich

Alex Dybenko said:
try the following. first you have to make a query which calculate days
between chargeDate and EffectiveDate, so you need to get a record with
lowest value. now you can sort query above by difference, and use dfirst to
get first row (als applying filtering by recouce and company)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



M Skabialka said:
How do I write a DLOOKUP in code which will find the correct
effective date and its load factor, if I can pass as parameters the
ResourceType, ChargeDate and CompanyName?

CurrentLoadFactor = DLOOKUP("LoadFactor", "tblLoadFactor", & _
"[ResourceType] = '" & rs![ResourceType] & "' and " & _
"[CompanyName] = " & rs![CompanyName] & "' and " & _
"[Effective date]
............??........rs![ChargeDate]...........??.................)

I need to find the closest effective date prior to the ChargeDate, then
return the LoadFactor.

ResourceType EffectiveDate LoadFactor CompanyName
FEF 1/1/02 1.0424 ABC
FEF 1/1/02 1.15788 XYZ
FEF 1/1/03 1.15053 XYZ
FEF 1/1/03 1.0299 ABC
FEF 1/1/04 1.0234 ABC
FEF 1/1/04 1.1461 XYZ
MTS 1/1/02 1.0424 XYZ
MTS 1/1/02 1.0424 ABC
MTS 1/1/03 1.0299 ABC
MTS 1/1/03 1.0299 XYZ
MTS 1/1/04 1.0234 ABC
MTS 1/1/04 1.0234 XYZ

Thanks,
Mich
Click to expand...
Click to expand...
 
Back
Top