MS Query with calculated field

Posted on 2011-10-05
Last Modified: 2012-05-12

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
Question by:BBlu
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.


    Author Comment

    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.
    LVL 85

    Accepted Solution

    Add a column (Records-Add columns...), using:
    using single rather than double quotes, and then give it whatever title you like.

    Author Comment

    That was the problem.  Thanks, Rorya.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now