Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to know how to write a "conditional query" in Access 97? : o /

Posted on 2005-04-12
8
Medium Priority
?
315 Views
Last Modified: 2012-05-05
Ok...i have a problem ...i have a query that generates a calculation in a field called Salesman Overage.  Below is the formula for the field:
Salesman overage: [Commissioncalc]-([Cabinet Net]+[Fin Mat'l])

This works fine as long as JobCost < ContractPrice.

What i need to do is be able to write something that says If the JobCost > Contract Price then i need it to run an alternate forumula that looks like this:

Salesman overage: [Total]-[ContractPrice]

Is there some sort of way to write this as a conditional if then statement in the query in that field?  In other words in the field where the query has this:
Salesman overage: [Commissioncalc]-([Cabinet Net]+[Fin Mat'l])

Replace it with something that would be equivelant to this:

If jobCost < Contractprice then
Salesman overage: [Commissioncalc]-([Cabinet Net]+[Fin Mat'l])
else
Salesman overage: [Total]-[ContractPrice]

I don't want to have to create any additional fields for this.  I'm very new to access so please be specific...Thanks!!

BTW..I'm working in Access 97

0
Comment
Question by:diglife
  • 4
  • 3
8 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 2000 total points
ID: 13763242
iif(jobCost < Contractprice, [Commissioncalc]-([Cabinet Net]+[Fin Mat'l]), [Total]-[ContractPrice]) as myNewField

Iif(test,value if true, value if false)

Idea??

Dave :-)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13763254
Salesman overage:  iIf (jobCost < Contractprice,[Commissioncalc]-([Cabinet Net]+[Fin Mat'l]), [Total]-[ContractPrice])
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13763278
man, that was SUPER fast Dave !
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:flavo
ID: 13763316
1 minute... quite slow actually ;)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13763337
Oh Yeah, i know what you mean ;o)
unfortunatly EE doesn't show the "seconds" in the time.
0
 

Author Comment

by:diglife
ID: 13763778
jeez..that was quick...wish i could give ya all 500 for the help.

Thanks
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13763826
Hey diglife

Welcome to EE,
you should see Dave when he is in his full capacity, like a bullet, fast and right to the target ;o)

jaffer
0
 
LVL 34

Expert Comment

by:flavo
ID: 13763881
Glad to help diglife!

Thanks for the kind words jaffer, you 'ant too bad youself ;)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question