calculate passed days on current status (1 Viewer)

megatronixs · Jul 11, 2018

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:

Code:

=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 · Jul 11, 2018

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

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

isladogs · Jul 11, 2018

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

Mark_ · Jul 11, 2018

@ 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

Code:

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 · Jul 20, 2018

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 :-(

Code:

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 · Jul 20, 2018

Have you read post #2?

megatronixs · Jul 20, 2018

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:

Code:

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

greetings.

The_Doc_Man · Jul 20, 2018

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 · Jul 20, 2018

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

Code:

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

JHB · Jul 20, 2018

You need to define that phase_initial_activities is from the recordset.

Code:

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

megatronixs · Jul 20, 2018

Thanks a lot JHB

did miss that one out, silly me.

Greetings.

JHB · Jul 20, 2018

You're welcome, good luck.

calculate passed days on current status (1 Viewer)

megatronixs

Registered User.

Ranman256

Well-known member

isladogs

MVP / VIP

Mark_

Longboard on the internet

megatronixs

Registered User.

JHB

Have been here a while

megatronixs

Registered User.

The_Doc_Man

Immoderate Moderator

megatronixs

Registered User.

JHB

Have been here a while

megatronixs

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread