How to create a field in a query

[melvinhagar]

I want to create a new field ONLY within a query. I don't wish to create a new field at the table level. I see that I can create a "calc" field within a query, but that involves mathmatical expression and numbers. I want to create a new field and populate its' value based on values in another existing field. Ex. If Table Field 1 contains the value "A", I want my query-only field to return value of "Apple". If Table Field 1 contains the value "O", I want my query-only field to return a valule of "Orange". Can this be done? I can find no helps on this. Everything I find talks about calc fields, or adding fields at the table level.

[aytee111]

Use the same method as a calculated field: Field name: value

e.g. TestField1: "A"
or TestField2:IIf(ThisTable.Field1="A","Apple",IIf(Th isTable.Field1="O","Orange"),etc)

[Bulzie]

What Aytee111 said. The key is the colon, left of it will be the name of column in the query. You can use this also to change the name of a column so say in table the field is called ProdNbr, in query you could change it to Product Number: [ProdNbr] so when you run the query it displays correctly.

[melvinhagar]

Nah. Can't get it to work. Can't get around syntax errors/issues and it offers no help with that. Tried format as given above by aytee111: TestField2:IIf(ThisTable.Field1="A","Apple",IIf(Th isTable.Field1="O","Orange") Format not acceptable.

[melvinhagar]

I guess I need to know in the calc field, what goes in the "Field" cell, the "Table" cell, and the "Criteria". I'm assuming the logic of the calc field goes in the criteria cell.

[aytee111]

In your first answer above there is a missing right parenthesis. Each IIf has an opening as well as a closing bracket.

This will go in the Field line. The table line will usually be empty (Access will decide that). The criteria field will be filled in with your criteria if you want any, such as ="Apple" or "Orange".

[melvinhagar]

I tried this in the Field line: EVP Group:IIf(EVP Level="EVP","EVP 0-3"),IIf(EVP Level="EVP-1","EVP 0-3"),IIf(EVP Level="EVP-4","EVP 4-99") .... got syntax errors. Attached are the details of what I'm trying to accomplish:

[rpeare]

you have to nest the if statement if you're going to have multiple categories but you really only have two"

iif([evp level] = "EVP" or [evp level] = "EVP-1" or [evp level] = "EVP-2" or [evp level] = "EVP-3", "EVP 0-3", "EVP 4 - 99")

p.s. stay away from spaces or special characters in your field names it makes them harder to deal with you'd be better off with a field named evp_level

[melvinhagar]

WORKED!!!! Thanks much rpeare.