Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: putting a function in a table column

    Here is my quandary. I have this Access 2003 database, and it records, for simplicity's sake, expiration dates. The expiration date column can contain null values.

    Then, there is a column right next to it for "status". I want the value of that field to be either "Current", "Expiring Soon", "Expired", of "Missing" dependant on the expiration date in relation to Now().

    I've never done anything like this before inside a table, I've always done it programmatically in front end applications, which isn't a problem, but these values are going to need to be determined by the database table its self, like... I'm not even sure how this is going to work.

    DESIRED RESULT: Get a report of all the dates that are Expiring Soon. (and other similar reports).

    So, this going to have to be something that... either is done in the background whenever the database is opened or..done... When the user elects a certain report (which I really don't want to do...it sounds really... resource hoggy).

    Tips, suggestings, tricks, ideas, anyone. I'm all open.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can't because Access does not allow to base the contents of a column in a table on a user-defined function, and Access does not support triggers that would allow an automatic update of the computed column.

    The closest solution would be to run an UPDATE query on the table every time the database is open, but it's on behalf of the front-end opening the database (or the table anyway).

    Moreover, there is seldom a real good reason to store the result of a function (computed value) into a table. Such a value is dynamic (it can change more or less often) and is better handled "on the fly", when it's needed (Query, Form or Report).
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the status column should be a fioeign key to another table, use either a numeric or text value, doesn't matter which. For this sort of thing I tend to use a text value which acts as a human readable code)

    then when you run your form/report use a query you specify what code or codes you need in that query

    another advantage of using a table for this (even with so few status codes is that if some tosspot user decides to add another status code, THEY can.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Thumbs up yeah...

    After a few hours of thinking about it, I was beginning to lean in that direction... just run a query when it's needed...or a stored proc- OH WAIT. Not using SQL server. Man I'd love to just have a trigger. -_-;;

    I really wish I had addressed this when I was first designing the database... but truth be told, this was the first database application I ever ever developed professionally (haha, yeah...), so it was all... kind of a learning process.

    Thank you for your advice!
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  5. #5
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    I think what I'm going to do is just write an update query and pretend that the main switchboard onLoad() event is a trigger.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would recommend calculating the status. You can easily do this by creating a Function in a module, and then adding a field to the query that is feeding the report or form. If you use an UPDATE query the data will be stale. I would also agree with Healdem create a small table with your statuses in it:

    Status ID---------StatusDescription
    1-----------------Expiring Soon
    2-----------------Expired
    .
    .
    etc.

    Then create a little function that uses the date to determine the status:

    Code:
    Public Function GetStatus(varDate As Variant) As Long
    
        Dim datExpirationDate As Date
        Dim booMissingDate As Boolean
        Dim lngDaysUntilExpiration As Long
        
        booMissingDate = IIf(Nz(varDate, True), True, False)
        
        If booMissingDate Then
        
            GetStatus = 4
        
        Else
        
            datExpirationDate = varDate
            lngDaysUntilExpiration = DateDiff("d", Date, datExpirationDate)
            
            Select Case lngDaysUntilExpiration
            
                Case Is < 1
                    
                    'Expired
                    GetStatus = 2
                    
                Case 1 To 90
                    
                    'Expiring soon
                    GetStatus = 1
                    
                Case Else
                
                    GetStatus = 3
                    
            End Select
            
        End If
        
    
    End Function
    Then add a field in your query GetStatus([ExpirationDate]) As Status. And finally join the Status field with the tblStatus to get the StatusDescription.

    The other benefit to having a tblStatus table is if someone wants the status decriptions to be different you only have to change it once in the table.

Posting Permissions

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