Issue with Dates

[NSAMSA]

Hi all:

I know this likely has a really simple solution, but I've been coming back to it for days now with no success and I feel like I'm going mad trying to figure it out... so here goes.

I have a database with a table tblSOPAudits. In this table there is a "date completed" column and two yes/no columns, with one being "revision required" and the other being "retraining required" I also have a calculated column called "Action Required" where IIf([revision required]=True, "Yes", IIf([retraining required]=True, "Yes", "No")) This formula works fine. I then have a yes/no column called ActionTaken.

I then made a query wherein I have the following calculated field:

IIf([Action Required]="Yes", DateAdd("m", 2, [date completed]). This returns the accurate date, (e.g. if the date completed was 5/20/2015 then the date comes out as 7/20/2015). This was called ActionDueDate.

I then made another calculated field to determine if the action was taken by the due date with the expression, Expr1: IIf(([ActionDueDate]<Date()) And ([ActionDueDate]<>"") And ([ActionTaken]=False),"Overdue","Compliant")

On today, 1/8/16, the formula should come out as "Overdue" if the ActionDueDate is 7/20/15 and ActionTaken is False. Its coming out as compliant.

To ensure the dates were in a "date" format, I actually did a Format(XX, "Long Date") to make sure they all turned into the correct dates. They did. So why does Access think that 1/8/2016 is less than or equal to 7/20/15 in this particular query? :banghead:

Thank you in advance for alleviating my slow decent into madness

 

[plog]

To ensure the dates were in a "date" format, I actually did a Format(XX, "Long Date")

You ensured the data was not treated as dates by doing that.

Format makes whatever you put in it a string. Strings are compared character by character, so that means 1/8/2016 is less than 7/20/2015 because 1 is less than 7 as a string.

To achieve what you want, you should use the DateValue function (http://www.techonthenet.com/access/functions/date/datevalue.php) or CDate function (http://www.techonthenet.com/access/functions/datatype/cdate.php) instead.

 

[NSAMSA]

You ensured the data was not treated as dates by doing that.

Format makes whatever you put in it a string. Strings are compared character by character, so that means 1/8/2016 is less than 7/20/2015 because 1 is less than 7 as a string.

To achieve what you want, you should use the DateValue function (http://www.techonthenet.com/access/functions/date/datevalue.php) or CDate function (http://www.techonthenet.com/access/functions/datatype/cdate.php) instead.

I changed the ActionDueDate to:

IIf([ActionReq]="Yes",CDate(DateAdd("m",2,[DateComplete])),""), which for my test row returns a date of 7/20/2015

The compliant formula is the following:

IIf(([ActionDueDate]<Date()) And ([ActionDueDate]<>"") And ([ActionTaken]=False),"Overdue","Compliant")

I am still receiving a result of "Compliant" despite Date() being 1/11/2016 and the ActionDueDate being 7/20/2015. The ActionTaken yes/no box is unchecked, (False), so all of the qualifiers are True and should return the result as "Overdue". I'm still not certain what I am missing here. Thank you again for your time and help.

 

[Minty]

Are you referring to ActionDueDate in the same query that you create it?
If so it won't work - you can't refer to a calculated field in the same query it is calculated in.

 

[arnelgp]

is revising this will change anything?

IIf(([ActionDueDate]<Date()) And ([ActionDueDate]<>"") And ([ActionTaken]=False),"Overdue","Compliant")


to:

IIf(((CDate(Nz([ActionDueDate], 0)) < Date()) And ([ActionTaken]=False),"Overdue","Compliant")