VBA And Access and DLookups = GRR

[Guest]

Hi there,

My issue is a little evil. I'm trying to create a report that is a balance
sheet of the company's machine sales. Each machine has an install and
warranty amount allotted to it. All manufacturers except one have hardwired
amounts tied to the machine model. Therefore, my code uses a DLookup to pull
up the correct amount if the manufacturer matches, and plinks it into a bound
textbox. This reports perfectly.

Now here's my problem. The red-headed stepchild manufacturer. Let's call
them Satan, Inc. Satan, Inc's install and warranty cost is a multiplier of
the customer purchase price. So right now I do the same thing as above, but
lookup the % multiplier, and list it in the textbox. So it uses the same
field as above, but just lists .15. I would like it to pull from the customer
purchase price, multiply it by "Install%", and plink it into the
abovementoined bound textbox. Then everyone would be happy.

My lookups look like this:

Me.txtinstallcost = DLOOKUP("[InstallCost]","[MachineModel]","[ModelID]=" &
cbModel.Value)

for non-Satan,Inc companies

and

Me.txtinstallcost = DLOOKUP("[Install%]","[MachineModel]","[ModelID]=" &
cbModel.Value)

for Satan, Inc.

There has to be some way to do what I'm thinking of. I just can't find it.

Thanks so much for your help,

Cherish
(e-mail address removed)

Can I do a math function in a DLookup?

 

[Frank Stone]

hi,
stickler of a problem. you cant do a math function in
dlookup but you can do a math function in a hidden text
box on your form.
doing this would cause you to use a iif statement in your
normal text box
iif(condition???, good dlook, evil dlook)
untested. pure theory. the "condition???" is the sticker.
I have done the hidden text box thing but not with a
dlookup.
sorry if i'm not much help
regards
Frank

-----Original Message-----
Hi there,

My issue is a little evil. I'm trying to create a report that is a balance
sheet of the company's machine sales. Each machine has an install and
warranty amount allotted to it. All manufacturers except one have hardwired
amounts tied to the machine model. Therefore, my code uses a DLookup to pull
up the correct amount if the manufacturer matches, and plinks it into a bound
textbox. This reports perfectly.

Now here's my problem. The red-headed stepchild manufacturer. Let's call
them Satan, Inc. Satan, Inc's install and warranty cost is a multiplier of
the customer purchase price. So right now I do the same thing as above, but
lookup the % multiplier, and list it in the textbox. So it uses the same
field as above, but just lists .15. I would like it to pull from the customer
purchase price, multiply it by "Install%", and plink it into the
abovementoined bound textbox. Then everyone would be happy.

My lookups look like this:

Me.txtinstallcost = DLOOKUP

("[InstallCost]","[MachineModel]","[ModelID]=" &

cbModel.Value)

for non-Satan,Inc companies

and

Me.txtinstallcost = DLOOKUP

("[Install%]","[MachineModel]","[ModelID]=" &

 

[John Vinson]

hi,
stickler of a problem. you cant do a math function in
dlookup

Eh? sure you can. See below.

Now here's my problem. The red-headed stepchild manufacturer. Let's call
them Satan, Inc. Satan, Inc's install and warranty cost is a multiplier of
the customer purchase price. So right now I do the same thing as above, but
lookup the % multiplier, and list it in the textbox. So it uses the same
field as above, but just lists .15. I would like it to pull from the customer
purchase price, multiply it by "Install%", and plink it into the
abovementoined bound textbox. Then everyone would be happy.

My lookups look like this:

Me.txtinstallcost = DLOOKUP

("[InstallCost]","[MachineModel]","[ModelID]=" &

cbModel.Value)

for non-Satan,Inc companies

and

Me.txtinstallcost = DLOOKUP

("[Install%]","[MachineModel]","[ModelID]=" &

cbModel.Value)

for Satan, Inc.

IIF([Manufacturer] = "Satan, Inc.",
[Price] * DLOOKUP("[Install%]","[MachineModel]","[ModelID]=" &
cbModel.Value),
DLOOKUP("[InstallCost]","[MachineModel]","[ModelID]=" & cbModel.Value)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps