# Thread: Function In Report

1. Registered User
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. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
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. Registered User
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. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
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
•