calculate passed days on current status

[megatronixs]

Hi all,
I have a datasheet inside a form. in this datasheet, the current status is visible and the phase initial activity date. Now I need to calculate the days passed of the current selected status. There are 13 status in available to choose from. For example Status 1 till Status 13.
Whit this simple formula I calculate the total passed days:

=Date()-[phase_initial_activities]

How can I get the passed days if lets say Status 3 is current selected?

in the table I capture for each status set, the date of it. Like Status_1_date, Status_2_date.

Any ideas how to get this started?

Greetings

 

[Ranman256]

in Access, date math is done with DateAdd, or DateDiff

=DateDiff("d",[phase_initial_activities],Date())

 

[isladogs]

Have a look at my example database which calculates dates until an event or since an event: Day Tracker

 

[Mark_]

@ OP,

Just to make sure, your question is "If I change a value in a combo box, how do I have a calculated field on my form display a related value"?

After you make the selection in the combobox, do your calculation.

For myself, I create a sub that will take care of these things and would use the current value in your combobox. I'd also have the display be a label, not a text box.

Then your sub becomes

Select Case Me.Combobox.column(Which ever column you need)
   Case "First Status"
      Me.MyDisplayLabel.Caption = date()-Status_1_date & " days"
   Case "Second Status"
      Me.MyDisplayLabel.Caption = date()-Status_2_date & " days"
   .
   .
   .

For myself, I'd put the status and status date into its own table. Avoids a lot of reprogramming when you discover you need to add "Another status".

 

[megatronixs]

hi all,

I managed to get the below code, it is almost working. The only thing that I do wrong is to get the number of days passed, instead I get a date :-(

Dim rst As DAO.Recordset
Dim status As int
Set rst = CurrentDb.OpenRecordset("SELECT phase_initial_activities, status, current_status_duration FROM tbl_main_data")
    status = rst.Fields("status").Value
    rst.MoveFirst
        While Not rst.EOF
        rst.Edit
    
            Select Case rst!status
        Case "Status 1"
            MsgBox "This is Status 1"
            rst!current_status_duration = Date - phase_initial_activities & " days"
        Case "Status 2"
            MsgBox "This is Status 2"
            End Select
    
            rst.Update
            rst.MoveNext
        Wend
        rst.Close

Any clue to fix this so it will show the numbers of day instead a date?

Greetings.

 

[JHB]

Have you read post #2?

 

[megatronixs]

hi JHB,

I get run-time error "2465" database can't find the filed "|1" referred to in your expression.

I changed the code part to this:

 rst!current_status_duration = DateDiff("d", [phase_initial_activities], Date)

greetings.

 

[The_Doc_Man]

That error 2465 (can't find field) is the clever way that Microsoft tells you you either spelled or punctuated something incorrectly in the SQL statement, or spelled a field name wrong in a VBA recordset action. It is saying that whatever it THINKS is the field name, that field cannot be found in the record's .Fields collection.

 

[megatronixs]

ok, now I don't get the error, but I get the number of the date :-(

rst!current_status_duration = DateDiff("d", phase_initial_activities, Date)

 

[JHB]

You need to define that phase_initial_activities is from the recordset.

 rst!current_status_duration = DateDiff("d", [B][COLOR=Red]rst![[/COLOR][/B]phase_initial_activities[B][COLOR=red]][/COLOR][/B], Date)

 

[megatronixs]

Thanks a lot JHB
did miss that one out, silly me.

Greetings.

 

[JHB]

You're welcome, good luck.