[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3053
  • Last Modified:

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
0
BBlu
Asked:
BBlu
  • 2
1 Solution
 
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
0
 
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.
0
 
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.
0
 
BBluAuthor Commented:
That was the problem.  Thanks, Rorya.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now