Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010

    Unanswered: Calculated Field

    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.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You can create a public function that returns the value of the computed field and reference this function in a query.
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Could you possibly post an example? And how would the public function work in this case. Just not sure. Thanks

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    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
    FROM Tbl_2290_Tons;
    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.
    Item_Category, Text.
    Item_2290	Item_Category
    28	        B
    29	        D
    30	        E
    31	        F
    32	        G
    33	        H
    34	        I
    35	        J
    36	        K
    37	        L
    38	        M
    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"
        End Select
    End Function
    The query would look like this:
    SELECT Tbl_2290_Tons.[2290_Tons], GetCategory([2290_Tons]) AS Category
    FROM Tbl_2290_Tons
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts