I am trying to create a field that will calculate a text value based on a field I have called 2290 Tons. I have this field working in my query in an unbound control but now I would like to use this field on a report. I don't see any way to do this. How can I get an actual calculated field in a query with an expression. What I need it to do is if the 2290 Tons field had a 28 then the new field called category would need to have a letter B in it, then if 2290 Tons had a 29 then a letter D and so on until I had all tons covered up to 38 and over.
I need to have an actual field in the query so I can use it on a report is my understading of Access. I am still trying to learn how access works and so if anyone has a better way of getting my Category field to work for the query and to use on a report I am open to suggestions.
You do not supply enough information about how the Category is computed. 28 yields B, 29 yields D, but what's the general rule?
The easiest way would consist in in computing the value of [Category] directly in the query. This is possible if you can write the function that produces this value in a single line.
SELECT Tbl_2290_Tons.[2290_Tons], IIf([2290_Tons]=28,"B",Chr([2290_Tons]+40)) AS Category
You can also create a lookup table that associates a letter to each value of the field [2290 Tons], then use a SELECT query with an inner join between the main table (the one with the [2290 Tons column] and the lookup table. This is only possible if there is a finite number of possibilities.
Ex. Table Tbl_Lookup:
Item_2290, number, indexed no duplicates.
I'll call the table that contains the column [2290 Tons] "Tbl_2290_Tons". Its possible to write a query like this:
SELECT Tbl_2290_Tons.[2290_Tons], Tbl_Lookup.Item_Category
FROM Tbl_2290_Tons INNER JOIN Tbl_Lookup
ON Tbl_2290_Tons.[2290_Tons] = Tbl_Lookup.Item_2290
The third solution consists in writing a public function in an independant module (i.e., not the module of a form or report), and make calls to that function inside the query.
Ex. (in this case the function performs exactly like the lookup table, but it could be something totally different).
Public Function GetCategory(Item_2290 As Long) As String
Select Case Item_2290
Case 28: GetCategory = "B"
Case 29: GetCategory = "D"
Case 30: GetCategory = "E"
Case 31: GetCategory = "F"
Case 32: GetCategory = "G"
Case 33: GetCategory = "H"
Case 34: GetCategory = "I"
Case 35: GetCategory = "J"
Case 36: GetCategory = "K"
Case 37: GetCategory = "L"
Case 38: GetCategory = "M"
The query would look like this:
SELECT Tbl_2290_Tons.[2290_Tons], GetCategory([2290_Tons]) AS Category