Field "AS" plus Criteria restriction

[mzza]

Hi,

I am trying to write a query which will select only records from a table where "USER_ID" = "21", but I want the results of this query to display "Birmingham" instead of "21". Is there a way to do this (without a lookup table)? I have tried the following:

Field:	USER_ID: "Birmingham"
Table:	tbl_Sales
Total:	Group By
Sort:
Show:	Y
Criteria:	"21"

<tbody>
</tbody>

... but it returns no data (presumably because it is evaluating for "21" AFTER applying the AS clause to show all USER_IDs as "Birmingham" (??)

I'm probably doing something really noobish here...

 

[Joe4]

I would recommend NOT using the same name as your Table Field Name as your Calculated Field Name.
So, I would create a calculated field like this:

UserID: IIF([USER_ID]=21,"Birmingham",[USER_ID])

and then putting "Birmingham" in the Criteria for this field.

Note that if the USER_ID is numeric, you should NOT put quotes around the 21. Only do that if it is a Text/String field.

 

[mzza]

That's working perfectly, thank you Joe!

 

[Joe4]

You are welcome!