Solved Expression Builder

[Teri Bridges]

Hi guys I was not sure where this topic fit in the discussions.

What I am trying to do is if the days late has data I want the due in field to clear Here is the expression I used so far. The piece I am missing is clearing (Hiding) the data in the due in field:
=IIf([txt_CompleteDate]<[DueDate],0,DateDiff("d",[DueDate],[txt_CompleteDate]))

Additionally, I want to have the day of the week populate based on the date in the due date field
I tried this expression but not even close - =Format([duedate],"ddd")

Here is the expression for my Due date field - I think for the 5 working days of the week I am missing 2 days?

=IIf(Weekday([txt_ReviewDate])=4,[txt_ReviewDate]+5,IIf(Weekday([txt_ReviewDate])=5,[txt_ReviewDate]+5,IIf(Weekday([txt_ReviewDate])=6,[txt_ReviewDate]+5,[txt_ReviewDate]+3)))

 

[MajP]

What I am trying to do is if the days late has data I want the due in field to clear

The best way to do this is with conditional formatting if the Due value is <= 0 then make the forecolor and backcolor White.

=Format([duedate],"ddd")

That looks correct. Is the field name dueDate or [Due Date]?

For the DueDate might want to use a function since it will be easier to debug. Try this. I put a link to more detailed code.

Public Function AddWorkingDays(ByVal dtmDate As Date, Optional NumberWorkDays = 3) As Date
 ' http://access.mvps.org/access/datetime/date0012.htm
  Dim i As Integer
  Do
    dtmDate = Int(dtmDate) + 1
    If Weekday(dtmDate) <> vbSaturday And Weekday(dtmDate) <> vbSunday Then
      i = i + 1
    End If
  Loop Until i = NumberWorkDays
  AddWorkingDays = dtmDate
End Function

Do you want to be able to modify due date? Is this a stored field? Then you do not want a function, but want to set the value in code using the function.

 

[CJ_London]

you could use null to leave the field blank

=IIf([txt_CompleteDate]<[DueDate],Null,DateDiff("d",[DueDate],[txt_CompleteDate]))

not quite sure what your duedate calculation is intended to do - but makes sense to me, although missing 1 day
if 4 (Wed) add 5 (Mon)
if 5 (Thur) add 5 (Tue)
if 6 (Fri) add 5 (Wed)
if 7 (Sat) add 5 (Thur) (not included in your calc)
else
if 1 (Sun) add 3 (Wed)
if 2 (Mon) add 3 (Thur)
if 3 (Tue) add 3 (Fri)

You could use a simpler iif function or the switch or choose function

=dateadd('n',iif(Weekday([txt_ReviewDate])>=4,5,3),[txt_ReviewDate])
=dateadd('n',switch(Weekday([txt_ReviewDate])>=4,5,3),[txt_ReviewDate])
=dateadd('n',choose(Weekday([txt_ReviewDate]),3,3,3,5,5,5,5),[txt_ReviewDate])

 

[Teri Bridges]

The best way to do this is with conditional formatting if the Due value is <= 0 then make the forecolor and backcolor White.

That looks correct. Is the field name dueDate or [Due Date]?

For the DueDate might want to use a function since it will be easier to debug. Try this. I put a link to more detailed code.

Public Function AddWorkingDays(ByVal dtmDate As Date, Optional NumberWorkDays = 3) As Date
' http://access.mvps.org/access/datetime/date0012.htm
  Dim i As Integer
  Do
    dtmDate = Int(dtmDate) + 1
    If Weekday(dtmDate) <> vbSaturday And Weekday(dtmDate) <> vbSunday Then
      i = i + 1
    End If
  Loop Until i = NumberWorkDays
  AddWorkingDays = dtmDate
End Function

Do you want to be able to modify due date? Is this a stored field? Then you do not want a function, but want to set the value in code using the function.

What I am trying to do is if the days late has data I want the due in field to clear

The best way to do this is with conditional formatting if the Due value is <= 0 then make the forecolor and backcolor White.
I am not sure that would work, more like I am not sure how to write the expression. What I am attempting is

If the [daysLate] has data, clear the data from the [DueIn]

I am going to try your code for the [DueIn] field. Thank you
I want to [DueIn] date to auto-populate and yes I want the user to be able to adjust this date if needed. I also want the Due date to be based on M-F and for the weekday to populate in the [Weekday] field.

If you have any suggestions on sites that will help me learn expressions and codes I would greatly appreciate the guidance.

Thank you

 

[MajP]

If you can post the DB it will be easier to help. The second column has conditional formatting of white on white, making the negative values appear invisible.

If the [daysLate] has data, clear the data from the [DueIn]

Is that the same as if DueIn < 0 . If that is the case you can wrap the function for your duein with an iif
=iif(calulationforDueIn < 0, null,calculationForDueIn)

 

[Teri Bridges]

If you can post the DB it will be easier to help. The second column has conditional formatting of white on white, making the negative values appear invisible.
View attachment 110078

Is that the same as if DueIn < 0 . If that is the case you can wrap the function for your duein with an iif
=iif(calulationforDueIn < 0, null,calculationForDueIn)

I am attaching a copy of my DB. I am now thinking that I need to write the expression based in if the complete date is filled in.
So something like
=IIF(txt_completeDate <0, Null, txt_duein) I put this in the complete date field but it did not work
The form I am working on is the EventStatus_Sfrm

 

[Gasman]

I am attaching a copy of my DB. I am now thinking that I need to write the expression based in if the complete date is filled in.
So something like
=IIF(txt_completeDate <0, Null, txt_duein) I put this in the complete date field but it did not work
The form I am working on is the EventStatus_Sfrm

Nothing attached?

 

[CJ_London]

So something like
=IIF(txt_completeDate <0, Null, txt_duein) I put this in the complete date field but it did not work

‘Did not work’ means what exactly?

and dates are very unlikely to be < 0 unless it is in the 19th century

if this is supposed to be per post#3, then look again

 

[Teri Bridges]

I am attaching a copy of my DB. I am now thinking that I need to write the expression based in if the complete date is filled in.
So something like
=IIF(txt_completeDate <0, Null, txt_duein) I put this in the complete date field but it did not work
The form I am working on is the EventStatus_Sfrm

Nothing attached?

Sorry I just saw that it said file is too large

 

[MajP]

You can zip and post.

 

[Gasman]

Sorry I just saw that it said file is too large

Make a copy, compact and repair. If still too large, zip it.

 

[Teri Bridges]

Sorry I just saw that it said file is too large

Ok I dummied down my DB.
I am not sure about asking the right question in the right order. I am new to writing expressions, macros, and VBA. I am never sure when to use which.

I was using the expression builder in my report to have specific fields return specific results.

I guess I should start with the first field giving me issues and then go in order:

Issue#1: The due date field needs to be 3 working days from the entered review Date. I am trying to say if the posted review date is 9/7/23 then the due date would populate to 9/12/23. A task cannot be due on a weekend. Currently I am still getting due dates that are weekends.

Issue #2. I would like the day of the week to be displayed based on the due date. So if it is due on 9/12/23 then Tue would populate in the weekday field. Currently this is not working correctly.

Issue # 3 What I would like to do is if a Complete date is entered then the Due in shows 0.

All education and help is greatly appreciated.

 

[Teri Bridges]

Ok I dummied down my DB.
I am not sure about asking the right question in the right order. I am new to writing expressions, macros, and VBA. I am never sure when to use which.

I was using the expression builder in my report to have specific fields return specific results.

I guess I should start with the first field giving me issues and then go in order:

Issue#1: The due date field needs to be 3 working days from the entered review Date. I am trying to say if the posted review date is 9/7/23 then the due date would populate to 9/12/23. A task cannot be due on a weekend. Currently I am still getting due dates that are weekends.

Issue #2. I would like the day of the week to be displayed based on the due date. So if it is due on 9/12/23 then Tue would populate in the weekday field. Currently this is not working correctly.

Issue # 3 What I would like to do is if a Complete date is entered then the Due in shows 0.

All education and help is greatly appreciated.

Issue #1 I think I have resolved this issue. I found this code in one of the other discussions. It appears to be working.

 

[MajP]

1. Applied the function I mentioned.
2. I added a recalc
3. Added conditional formatting

 

[CJ_London]

I’m going to drop off this thread since my contribution is being ignored

 

[Teri Bridges]

I’m going to drop off this thread since my contribution is being ignored

I am sorry I was not ignoring you. I could not get it to work. I got #Name! #size! and type errors. I was trying to figure out what I was doing wrong while also trying to follow other advice. I am sorry all the contributions to my learning are greatly appreciated.

 

[Teri Bridges]

I am going to mark this as Solved. Thank you all very much for the help.

 

[cheekybuddha]

I am sorry all the contributions to my learning are greatly appreciated.

I think this sentence would benefit from some additional punctuation!