Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Fort Myers, FL 33907
    Posts
    5

    Unanswered: Function In Report

    I am tring to get a value to calculate in a report.

    I have all the data from a tabel and i want to DLookUp one value to calculate from a tabel called Employees. I can get the value to lookup fine. It is a Yes/No value. I get the -1 for yes just fine.
    I want to make it check this value and multiply it by a value all ready on the report to make a new number. (These are all dollar values)

    I kinda need a if then statement. I just don't know how to write one in the Expression Builder? I am new to Access and just self tought.

    Hope someone can help.

    Here is what i have so far.

    3 feilds

    RBOYesNo
    RBOAM
    Total

    I need to check the RBOYesNo Feild for a -1
    I have this feild doing a DLookup (works fine)

    if it is yes(-1) i need it to take a percentage of the total (RBOAM) is a percentage

    if it is 0 i need it to just do nothing.

    I have tried everything i could think of. Nothing will work i just get Syntax errors.

    Thanks again
    Gary

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    dlookup may not be the answer

    maybe a dsum of the amount where filed = yes

    something like this

    dim x as variant
    dim y as variant

    x = dsum("[myamt]","mytable","[myYesNofield]= -1")

    y = x * somepercentagevalue


    see the dlookup really just returns true or false
    the criteria allows for the return of the valu or it is null - no return

    you can also add onto thie by saying

    x = dsum("[myamt]","mytable","[myYesNofield]= -1 and myid = somenumber")

    I hope I understood correctly - sometimes I fly off an answer a little quick

    I would like to know what errors you get

  3. #3
    Join Date
    Jun 2003
    Location
    Fort Myers, FL 33907
    Posts
    5
    I need to put these calcs in a report. you can not use VB Editor in reports, sorry for the stupid question.

    Let me see if i can simplify this....

    I need to check a feild for a value. and based on this value add this value to a second feild for calc.

    Like if one feild has $9.50 then add that to the cost. If it is $0.00 then do nothing.

    That is one calc.

    2nd Calc.

    Check Yes/No Feild for a -1. If there add a+b. If 0 do nothing.

    Hope that i have explaned my self.

    Thanks Again
    ~Gary

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    dim x

    x = dlookup("[myamt]","mytable","[myYesNofield]= -1")

    if x = 9.5 then
    mycontrol = 9.5 + mycost
    else
    end if


    you can put this code behind the onformat event of the section of the report that is evaluating these controls (fields)

Posting Permissions

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