Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    21

    Unanswered: Need to change status depending on dates

    Is there any easy way to change a status field based on days between dates. For example, I have a job start date of 6/17/04 and a job end date of 6/20/04. I want the status of the job to show "ACTIVE" on 6/17, 6/18, 6/19, 6/20 but after 6/20 the job status should be "COMPLETED". Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    probably need some more context, but this may fit:

    iif(testDate between startDate and endDate, "ACTIVE", "COMPLETED")
    where testDate, startDate, endDate all need to be date types.

    is there a logic problem? dates before startDate will show "COMPLETED" !!
    possible fix:
    iif(testDate < startDate, "", iif(testDate between startDate and endDate, "ACTIVE", "COMPLETED"))

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    My version:

    iif(testDate > endDate, "COMPLETED", "ACTIVE")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thought you hadn't had your coffee yet!
    you are (blindingly obviously) right. izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Drinking as we speak..


    sweet, sweet nectar of the gods....
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2002
    Posts
    21

    More specifics

    I should have been more specific. I am trying to change the status in my form's list box and have several statuses - Completed, Active, Tied Out, Set-up, Stips, Cancelled. I have a stips box (txtdate) where the user enters a date and I have a cancelled check box (chkcancel) on the form as well. There are job start and end date boxes that the user populates. Here is my code:

    If Nz(txtstartdate) < Nz(txtEnd) And Nz(txtEnd) >= Date And chkcancel = Unchecked And Nz(txtdate) = " " Then
    cmbstatus.Value = "Active"
    ElseIf Nz(txtstartdate) >= Date And Nz(txtEnd) > Date And chkcancel = Unchecked And Nz(txtdate) = " " Then
    cmbstatus.Value = "Setup"
    ElseIf Nz(txtstartdate) < Date And Nz(txtEnd) = Date And chkcancel = Unchecked And Nz(txtdate) = " " Then
    cmbstatus.Value = "Completed"
    ElseIf Nz(txtdate) > Date Then
    cmbstatus.Value = "Stips"
    ElseIf Nz(txtstartdate) <= Date And Nz(txtEnd) < Date And chkcancel = Unchecked And Nz(txtdate) < Date Then
    cmbstatus.Value = "Tied-Out"
    ElseIf chkcancel = Checked Then
    cmbstatus.Value = "Cancelled"

    It does not work right now but hopefully you can see where I am going..

Posting Permissions

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