Checkboxes to change date fields

[AFKillbait]

Currently I have a due date field that uses the following expression:

=IIf(IsNull([Training]),"Not Completed",
IIf(DateAdd("m",12,[Training])<Date(),"Overdue",DateAdd("m",12,[Training])))

I also have a checkbox that makes a date field become visible if checked. I am trying to figure out how to make it so that if the checkbox is checked the due date field will adjust its DateAdd expression to be equal to the date entered into the checkbox date field.

Is this even possible?

 

[wazz]

first thing you might consider is putting things like, "Not Completed", into separate textboxes or labels. is the due date field a Date data type or text? keep text separate if it's date.

the rest is a tiny bit confusing but all looks possible. maybe focus on one thing at a time, like what shows up when the form opens, or/then, what happens if a check box is clicked (use the afterupdate event). lay it all out on paper if necessary.

perhaps create a variable for values you use repeatedly, like DateAdd("m",12,[Training]).

 

[AFKillbait]

Right now the Due Date field is an unbound textbox. It isn't actually saved to any table, it's just based off of a Completion date that is. The completion date is just a pure date field on a table.

Would the AfterUpdate event overwrite the expression for the Due Date in the Control Source?

 

[AFKillbait]

I figured it out. I just needed to add another IIf line to the current expression:

=IIf(IsNull([Training]),"Not Completed",IIf(DateAdd("m",12,[Training])<Date(),
"Overdue",IIf([Conference]=-1,[ConfDate],DateAdd("m",12,[Training]))))

 

[AFKillbait]

So now that I got that previous one working I have a new issue. I now need the text field to base its date on both a checkbox and three date fields so that if the date in the text box [TrainingDue] is before the one in the return date field [RetDate] it will display the date they are leaving minus one day.

[Training] is the original date field that the text box is based off of. Then if the Conference box is checked, and a date entered into the [DepDate], it will only change the due date text box if the [Training] date +12 months is before the Return Date [RetDate].

If the Conference box isn't checked I want it to mark "Not Complete" if there is no date in the [Training] field, or Overdue if the date there is older than 12 months.

=IIf([Conference]=-1,
IIf(DateAdd("m",12,[Training]<Date([RetDate]),DateDiff("d",1,[ConfDate]),
IIf(IsNull([Training]),"Not Completed",
IIf(DateAdd("m",12,[Training])<Date(),"Overdue",DateAdd("m",12,[Training]))))

I currently have all of this in the Control Source for the text box. When I finished putting all of that in it told me "The expression you entered has a function containing the wrong number of arguments." I know what the error means but I can't see what I'm missing.

 

[wazz]

=IIf([Conference]=-1,
IIf(DateAdd("m",12,[Training] [B][COLOR=red])[/COLOR][/B] <Date([RetDate]),DateDiff("d",1,[ConfDate]),
IIf(IsNull([Training]),"Not Completed",
IIf(DateAdd("m",12,[Training])<Date(),"Overdue",DateAdd("m",12,[Training]))))

 

[AFKillbait]

Wow, you know you were looking TOO hard when...

Thanks for that. However it is still giving me "The expression you entered has a function containing the wrong number of arguments."

 

[AFKillbait]

Tried moving things around a little bit, changing the Conference box to 0 to see if maybe I could rearrange it to work so that it looks like this:

=IIf([Conference]=0,IIf(IsNull([Training]),"Not Completed",
IIf(DateAdd("m",12,[Training])<Date(),"Overdue",
IIf(DateAdd("m",12,[Training])<[COLOR=royalblue]Date[/COLOR]([RetDate]),DateDiff("d",1,[ConfDate])
,DateAdd("m",12,[Training])))))

however I still get the same error. As near as I can tell I am missing the "Else" section of the original IIf statement, but I'm not sure which section to move where to get it to work, or if I need to add something else.

Also I notice that when I remove the Date that is in blue above it no longer gives me the previous error, but instead shows #Name? in the text box instead.

 

[wazz]

IIf([Deployed] = 0, _
    IIf(IsNull([OPSEC]), "Not Completed", _
        IIf(DateAdd("m", 12, [OPSEC]) < Date, "Overdue", _
            IIf(DateAdd("m", 12, [OPSEC]) < Date [COLOR=red][B],[/B][/COLOR] [RetDate], DateDiff("d", [COLOR=red][B][Date][/B][/COLOR], [DepDate]) [B][COLOR=red])[/COLOR][/B], _
                DateAdd("m", 12, [OPSEC]) [COLOR=red][B])))[/B][/COLOR]

you can see the important parts in red, the main one being that you need a date in the DateDiff function.

please note, i have no idea if this will do what you want, i just fixed the syntax.

 

[AFKillbait]

So if I wanted this part:

(DateAdd("m",12,[Training])<Date([RetDate])

to check to see if the training date+12 months is before their return date (RetDate) i need to separate the Date part of the expression from the [RetDate] part with a comma? I know with the Overdue part of the expression it is written: Date() to represent today's date.

 

[wazz]

try

(DateAdd("m",12,[Training]) < [RetDate])

 

[AFKillbait]

Ok, thankfully that gets rid of the major error. Now I just need to figure out why it's giving me the #Name? error.

 

[wazz]

there must be a "reference" error, meaning the name of something is wrong. maybe the code is referring to a control using the wrong name, or code in a control is referring to a field or another control using the wrong name. just check control names and field names and names used in code to make sure they match.

 

[AFKillbait]

Two letters were messed up in there... of all the silly things to miss. Thanks for all the help, it's working as intended now.

 

[kkdai]

very nice comments, I have learned so much from here!