Share via

In Access, VB, I am needing help with a IF/THEN scenario involving dates.

Anonymous
2025-05-27T15:00:28+00:00

I have a form that I use for task scheduling and keeping track of dates when I performed that task. It is a simple form where I have a "STATUS" field combo box with "Not Started" and "Completed" for source options. Also, in the sourced table, I have columns identified for each of the four quarters in the year. I need help with the code that, if "STATUS" is selected "Completed", and today is, for example, is in the second quarter of this year, THEN enter "TODAYS DATE" in the "Quarter2" field.

Thanks,

Jeff Bomberger

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-05-27T16:20:05+00:00

I think you need something like

DIM Q AS BYTE
Q = DatePart("q", Date()) 

If Q = 1 Then CompletedDate1 = Date() 
If Q = 2 Then CompletedDate2 = Date() 
If Q = 3 Then CompletedDate3 = Date() 
If Q = 4 Then CompletedDate4 = Date()

Was this answer helpful?

1 person found this answer helpful.
0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-27T21:18:43+00:00

    I would strongly recommend that you take Scott's advice.  In relational database terms what you have is a binary many-to-many relationship type between tasks and statuses.  CompletedDate is a non-key attribute of the relationship type.  Such a relationship type should be modelled in one way only, which is by means of a table which resolves the relationship type into two one-to-many relationship types.  In addition to the two foreign keys the table can have non-key columns which represent attributes of the relationship type.

    The important thing about modelling a relationship type in this way is that it scientifically mirrors the way in which the two entity types, tasks and statuses, are related in the real world with which the database is concerned.  Fortunately we don't need to concern ourselves with the relational algebra in which the science is expressed.  The point is that it is science and therefore subject to formal and immutable rules.  The bottom line is that if the database models the reality as a mirror of the relationships in the reality, the database can be relied on to behave in the same way as the reality, and we won't  suddenly find that the database contradicts that reality and presents us with bad data.

    If you want to familiarise yourself with the process of Normalization  you might like to take a look at Normalization.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    This little demo file provides as simple as possible, but no more so, an introduction to the subject.  Don't concern yourself unduly with the formal definitions of each Normal Form, but try and gain a good understanding of the first three Normal Forms to start with, and how these govern good database design.

    Was this answer helpful?

    0 comments
  2. George Hepworth 22,855 Reputation points Volunteer Moderator
    2025-05-27T19:49:13+00:00

    "... it works, but not very efficiently. "

    That's one of the key considerations.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2025-05-27T17:22:42+00:00

    I would expand on George's suggestion. I would have a child table named tblTaskStatus like this:

    TaskStatustID (PK Autonumber)

    TaskID (FK)

    StatusID (FK)

    CompletedDate

    You would then have a subform on your Tasks mainform bound to this table and linked on TaskID to enter the Status. On the subform you would have a combo to select the Status and enter the Completed Date, you would calculate the Quarter not store it.

    Was this answer helpful?

    0 comments