I have a question about how to perform a calculation. I'm using Access 2003 in Windows XP.
I have three fields of different measurements, which I'll call A, B, and C. Those numbers get crunched to yield X, Y, and Z, respectively, in a query. No problem so far. What I want to do next is calculate N, but how I do that depends on the value of A/B:
If A/B >30
Then N=(Y+Z)/2
If A/B <10
Then N=(X+Z)/2
If 10<= A/B <=30
Then N=( (X+Y)/2 + Z)/2
Can I make this happen in a query?
I've tried various ways of entering If...Then...ElseIf..., with different combinations of parentheses and brackets to separate terms, but I keep getting an error that says "The expression you entered contains invalid syntax. You may have entered an operand without an operator." After I click OK, my first Then in the statement is highlighted.
I would appreciate any help in getting this calculation to work. I'm still new at Access, so please give step-by-step instructions if you can. Many thanks!
6  4927 
What's the code you're using? You can't just use an if/then in a query. You have to use iif(expression, value if true, value if false).
What's the code you're using?
This is my latest attempt in the query's design view:
TSI: If ([TNdTP]>30) Then (([TSITP]+[TSISD])/2)
[Else (([TSITN]+[TSISD])/2)]
When I change that If to iif, it gives me this error: The expression you entered has a function containing the wrong number of arguments.
It looks like you replied before I made my edits. You'll need to use the iif() function. And you can nest them within each other as a quick fix.
This is my latest attempt in the query's design view:
TSI: If ([TNdTP]>30) Then (([TSITP]+[TSISD])/2)
[Else (([TSITN]+[TSISD])/2)]
When I change that If to iif, it gives me this error: The expression you entered has a function containing the wrong number of arguments.
Well, you can't merely change If to iif because the syntax would be wrong. - TSI: iif ([TNdTP]>30, [TSITP]+[TSISD])/2, [TSITN]+[TSISD])/2)
Using nested iif statements makes it work. Thanks so much!
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Michael Hagstrom |
last post by:
Good Day -
I have written a form which collects input for a fare calculation. The form
collects the following:
1) Departure or Destination Group (1,2,or 3)
2) Number of Adults
3) Number of...
|
by: Gx |
last post by:
I have this form with the fields:
* Product: (drop down combo box)
with choices - X, Y
* Amount: (currency )
* Balance (currency)
I want to associate product X with the percentage...
|
by: Aravind |
last post by:
Hi folks.
I have a form, frmHistory, that has the following code for its On Open
event.
=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=
Private Sub Form_Open(Cancel As Integer)
...
|
by: Pres |
last post by:
I am not an experienced programmer. I do have a question regarding
workday calculations.
I have 3 fields. CURDATE, NUMDAYS, CALCDATE
After entering the first two fields, normally the current...
|
by: vg-mail |
last post by:
Hello all,
I have identical design for form and report but I am getting
calculation error on form and everything is OK on report. The form and
report are build up on SQL statement. The...
|
by: sara |
last post by:
i am studying a computer engineering and i started taking programming
using C++ since month
i have question i think it`s easy for you all *prof.programmer* but
it`s bit diffecult for me plzz i...
|
by: 60325 |
last post by:
This is the page where I collect the data in drop-down boxes with
values of 1-10 and send it to a submitted page to do calculations.
Example:
Employee1 TeamScore(1-10)
Employee2 ...
|
by: beach.dk |
last post by:
Hi,
I'm trying to implement a simple hash algorith called rs_hash in
javascript,
but I cannot get a correct result.
In c the code looks like this:
|
by: Colin Clark |
last post by:
Hello,
I have a database of scores for a sports club. I want to display a continuous subform showing a person's scores for the season along with a running calculation based on those scores (the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
