MS Query with calculated field


I have created an MS Query from Excel that pulls data from a table in an Access Database.  I'd like to create a new field for the query only.  I know if/when I do this in Access, the format of a formula that uses iif is expressed as:

NewField: iif([Field1]="something",1,0)

I'm trying to do a similar format in the MS Query, but it's not accepting it. I'm assuming that the format is slightly different.  Can someone help?

 MS Query Help
BBluAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
To create a database query that prompts for filter constants each time the query is updated or that uses one or more cell values as query parameters, create the database query by selecting the menu command Data->Import External Data->New Database Query. Follow the prompts to the point in the Query Wizard where the Filter Data dialog is displayed. Add the desired filter criteria using constant values - the parameter substitution part of the query will be implemented in a later step but there must be at least one filter criterion for each desired parameter. Click Next to view the Sort dialog and Next again to view the Finish dialog. Click the radio button "View data or edit query in Microsoft Query" and click Finish.

If working with an existing query then right-click on the query data and select "Edit Query". Click Next until the Finish dialog is displayed. Click the radio button "View data or edit query in Microsoft Query" and click Finish.

The Microsoft Query window is now displayed. In the middle of the dialog each of the defined filter criteria are listed, one per column. In each criteria where a parameter is desired change the value from the constant value previously entered to a question enclosed in square brackets such as "[Enter filter value:]". Close the Microsoft Query window. When the window closes an update will be initiated and each parameter will be requested. Enter each parameter requested to complete the query. Any time the query is refreshed the query will again ask for each parameter.

To use cell values to drive the query instead of entering them each time, right-click on the query data and select Parameters. The "questions" entered using the Microsoft Query window are listed in the left column. To convert any one to use a cell value instead of prompting for the value, select the "question" and click the radio button "Get the value from the following cell:" Enter the cell address in the text entry box below the radio button. To update the query every time the cell value changes, check the check box "Refresh automatically when cell value changes". Repeat for each "question" to be converted. Click OK.

Note that there is a bug in Excel that alters the cell references when the query is edited using Microsoft Query. The bug only occurs in Excel 2002 and only when there are two or more cell references. The symptom is that the second to last reference is replaced with the last reference. E.g., if there are three parameter references, A2, A4, and A7, then, after editing, the references for the three parameters will be A2, A7, and A7. To correct, right-click on the query data, select Parameters, and re-enter the second to last reference. There is no known method to prevent the symptom from occurring. The problem was resolved in Excel 2003.

Kevin
BBluAuthor Commented:
Thanks, Kevin.  I don't want it to prompt.  I just want to convert the ChargeAmount to negative if the type is "Credit".  So I'm trying to create a new column/field and use an if statement.  I just don't know what the syntax is.
Rory ArchibaldCommented:
Add a column (Records-Add columns...), using:
 iif([Field1]='something',1,0)
using single rather than double quotes, and then give it whatever title you like.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBluAuthor Commented:
That was the problem.  Thanks, Rorya.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.