Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Unanswered: DLookup Functions

    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.

    Can I do a math function in a DLookup?

    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
    clallone@singlesourcetech.com

  2. #2
    Join Date
    Apr 2004
    Posts
    173
    Couldn't you test the company name or number and then fill the text box based on that. Like....

    if company_name = Satan_Co then
    me.txtinstlcost = dlookup(purchase price) * dlookup(install %)
    else
    me.txtinstlcost = dlookup(install_cost)
    end if

    Sorry if this reply doesn't help.

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    I already test the company name.
    Right now it fills in a % if the company is Satan, Inc, and a currency amount if it is any other manufacturer. My question is regarding the proper syntax of:
    me.txtinstlcost = dlookup(purchase price) * dlookup(install %)

    Can I just multiply two Dlookups? The problem with making it an AfterUpdate of Machine Model is that purchase price isn't determined until later on in the form. It's not hardcoded into a table because it is a function of base price and options.

    I'm probably looking for a way to use an AfterUpdate of PurchasePrice. It's some kind of VB code that I'm not familiar enough with to use.

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    Yes it is possible to just multiply two dlookups. Maybe my understanding of your problem is not correct.

    After update of purchase price
    test the manufacturer
    if satan then
    me.txtinstlcost = dlookup(install%) * purchasePrice
    else
    me.txtInstlcost = dlookup(installcost)
    end if

    Also since this isn't a stored value. You would need to do the same thing in the oncurrent event of the form. First testing to see if the record is new or not. Again sorry if this doesn't help.

  5. #5
    Join Date
    Sep 2003
    Posts
    228
    Quote Originally Posted by Calfuzz
    Can I just multiply two Dlookups?
    Can you multipley 2 X 2? Of course you can multiply two dlookups. All the multiplication operator sees are the two return values from dlookup... Just make sure you are not going to multiply anything by null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •