Query create a due date (1 Viewer)

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
I want to create a due date and if the due date falls on a weekend move it to monday

I can write DueDate=[startdate]+3 this gives me my due date

I can write =IIF(Weekday([duedate])=1,[duedate]+1,IIF(Weekday([duedate])=7,[duedate]+1)) and that works now I have a second duedate field

I want to combine the statements into one duedatefield. I cannot seem to figure out how to write it
 

Attachments

  • new due date.png
    new due date.png
    37.2 KB · Views: 126

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
19,164
Have you tried replacing [duedate] in your second expression with the full expression from your first one? Meaning, where you have [duedate], try using [startdate]+3.
 

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
Have you tried replacing [duedate] in your second expression with the full expression from your first one? Meaning, where you have [duedate], try using [startdate]+3.
Yes it still only gives me the duedate excluding the weekends valuse it does not populate the due date if it is not a weekend
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
19,164
Yes it still only gives me the duedate excluding the weekends valuse it does not populate the due date if it is not a weekend
Oh, I see. Just add the same expression one more time at the end for the False argument of the second IIf() expression. For example,
Code:
IIf(Weekday(startdate+3)=1,startdate+4,IIf(Weekday(startdate+3)=7,startdate+5,startdate+3))

Edit: Fixed typos
 
Last edited:

June7

AWF VIP
Local time
Today, 09:33
Joined
Mar 9, 2014
Messages
4,451
Watch out for those typos - dropped the second t in startdate.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
19,164
Watch out for those typos - dropped the second t in startdate.
That's what I get for using my phone. Probably should go to bed instead. Thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:33
Joined
May 7, 2009
Messages
16,810
you can also use Switch:

DueDate: [startDate] + Switch(Weekday([stardate])=4, 5, Weekday([startdate])=5, 4, True, 3)
 
Last edited:

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
Oh, I see. Just add the same expression one more time at the end for the False argument of the second IIf() expression. For example,
Code:
IIf(Weekday(startdate+3)=1,startdate+4,IIf(Weekday(startdate+3)=7,startdate+5,startdate+3))

Edit: Fixed typos
DueDate: IIF(Weekday(StartDate)=4,StartDate+5,IIF(WeekDay(StartDate)=5,StartDate+4,StartDate+3))

Works!!!!
 

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
don't be, just google the syntax. it is equevalent to In-line "If...Then..." statement.
I am getting better I can write simple ones, but when I try to get a value first then return the results I am having troubles.

I have another one. I need to calculate the days due. I can do that... [review date]-Date()

But i also have to consider the status

I need to calculate the days due and if the status is complete return 0if the status is complete return 0, if the status is in process, return the calculated days due, if the status is over due return the calculated days due
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:33
Joined
May 7, 2009
Messages
16,810
[days due]: Iif([status] & "" = "complete", 0, DateDiff("d", [review date], date()))
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
19,164
DueDate: IIF(Weekday(StartDate)=4,StartDate+5,IIF(WeekDay(StartDate)=5,StartDate+4,StartDate+3))

Works!!!!
Glad to hear you got it sorted out. Good luck with your project.
 

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
[days due]: Iif([status] & "" = "complete", 0, DateDiff("d", [review date], date()))
I want to thank you for not making me feel like a total idiot. I truly appreciate the help learning. Just so you know I am building a DB for fun So I can learn new skills. I have taken some on line courses that have gotten me kick started, but as I progress I am finding so many people do things different. I try and deconstruct what is being done and then reconstruct it and try to apply the information to my data base. I guess I need to find some better Macro courses to help me along.
 

June7

AWF VIP
Local time
Today, 09:33
Joined
Mar 9, 2014
Messages
4,451
Note that a macro in Access is very different from what may be called a macro elsewhere. Access offers two programming tools: macros and VBA (3 tools if you count SQL). This thread has been about building expression (calculated field) in query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:33
Joined
Feb 19, 2002
Messages
36,868
What about holidays? Here's a link to a database of useful date functions. What you need is in there. You will need to update the holiday table since the db is old.

 

Teri Bridges

Member
Local time
Today, 12:33
Joined
Feb 21, 2022
Messages
53
What about holidays? Here's a link to a database of useful date functions. What you need is in there. You will need to update the holiday table since the db is old.

I am too afraid to mess it up with adding holidays....LOL
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:33
Joined
Feb 19, 2002
Messages
36,868
Take a look at the code. It is written for you in the database. I just haven't updated the data in the table. Also, if you are not in the US, you would have different holidays.
 

Users who are viewing this thread

Top Bottom