Preventing negative numbers in report

[m0aje]

Hello,

I have a report where I have equipment tested every 90 days. I have a field called TEST DATE formatted using the medium date format
(07-Mar-14). I have another field called RETEST DATE where my query adds 90 days and displays the Retest date as (07-Jun-14).
I have a unbound box with a heading called "Day(s) passed retest date.
In the Control Source I am using =Datediff("d"[Retest Date],Now()). In this scenario the result is -90.
Is there a way for this -90 to stay at 0 until the 91st day where it will count up from there?
I am not knowledgeable in VB code. I was wondering if this could be corrected in the Control Source.

Any help would be most appreciated.

Thanks,

m0aje

 

[Mihail]

IIF(Datediff("d"[Retest Date],Now()) <= 90, 0, Datediff("d"[Retest Date],Now()))

 

[jdraw]

A couple of things:

Now refers to Date and Time
eg 7-Mar-2014 3:21:37 PM

You just need Date which will return today's date.
eg 7-Mar-2014

As for the calculation
If the retestDate is overdue by 91 days you want to count the number of days above 90

=IIf(DateDiff("d", retestDate, Date) > 90, Date - retestDate - 90, 0)

The iif works in vba, and I think should work as a control source.

Sub tetestdate()
Dim retestDate As Date
retestDate = CDate("6-Dec-2013")
Debug.Print IIf(DateDiff("d", retestDate, Date) > 90, Date - retestDate - 90, 0)
End Sub

Good luck

NOTE: I see Mihail answered while I was typing

 

[m0aje]

Greetings Mihail & JDraw,

Many thanks for your responses. I am most grateful. I will try both of your suggestions out on Monday.

You guys are the greatest. THANK YOU!

 

[Mihail]

I've edited my previous post. Sorry (should be <=90 as JDraw said).

Yes JDraw. It work as Control Source.

 

[m0aje]

Thanks Mahail. I will try it out on Monday. I appreciate your help.

Best regards,

m0aje

 

[m0aje]

Hello Mihail & JDraw,

Well I tried both strings in my query in design view so I could change the test date to see if it worked correctly.
In my design view I created Days Passed as follows.

Days Passed: IIf(DateDiff("d",[Retest Date],Now())>=90,0,DateDiff("d",[Retest Date],Now()))

I corrected some syntax errors. I changed the test date to reflect 3 days beyond the retest date and it shows the correct number of days passed, but when changed back to the original test date it still shows a negative number. (-87)

I tried this one from JDraw in the query design view:

=IIf(DateDiff("d",[Retest Date],[Test Date])>90,[Test Date]-[Retest Date]-90,0)

The result was 0 regardless of the retest date. Not sure why. It may be that this one is conflicting with the =DateAdd("d",90,[Date]) string I am using in the Retest Date.

Any further suggestions would be much appreciated. Thanks to both of you for your help and suggestions.

m0aje

 

[Mihail]

Because OURS "<=" is different from YOURS ">="
Take a look to the IIF() function syntax.

 

[m0aje]

Hi Mihail,
I tried both ways with yours and JDraws: Using a test date of 3/7/2014 the 90 day retest date results is 6/5/2014
=IIf(DateDiff("d",[Retest Date],[Test Date])<=90,[Test Date]-[Retest Date]-90,0) returns -180 days passed

=IIf(DateDiff("d",[Retest Date],[Test Date])>=90,[Test Date]-[Retest Date]-90,0) returns 0 days passed
---------------------------
Using a test date of 3/7/2014 the 90 day retest date results is 6/5/2014

IIF(Datediff("d"[Retest Date],Now()) <= 90, 0, Datediff("d"[Retest Date],Now())) returns 0 days passed.

Using a test date of 12/5/2013 the 90 day retest date results is 3/5/2014

IIF(Datediff("d"[Retest Date],Now()) <= 90, 0, Datediff("d"[Retest Date],Now())) retruns 0 days passed. With today being 3/10/2014 it should have been 5 days passed. Right?

Sorry to be a pain in the butt.. Does the syntax look correct to you? I know it doesn't take much out of place to mess it up.
Thanks for your response.
m0aje

 

[jdraw]

m0aje,

I have run my code with a few examples to show

-if retestDate and Date are less than 90 days apart the expression returns 0
-if retestDate and Date are more than 90 days apart the expression returns the number of days - 90

So
91 days apart shows 1
92 days apart shows 2
93 days apart shows 3...

Here's a test with different retest dates and the output.

Sub tetestdate()
Dim retestDate As Date
retestDate = CDate("11-Dec-2013")
Debug.Print "Date "; "      RetestDate  DaysAbove90 "; "Date-RetestDate" & vbCrLf & Date & "  " & retestDate & "  " & IIf(DateDiff("d", retestDate, Date) > 90, Date - retestDate - 90, 0) & Space(20) & Date - retestDate
End Sub

Here are the results:

Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  06/12/2013  4                    94
Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  07/12/2013  3                    93
Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  08/12/2013  2                    92
Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  09/12/2013  1                    91
Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  10/12/2013  0                    90
Date       RetestDate  DaysAbove90 Date-RetestDate
10/03/2014  11/12/2013  0                    89

Maybe we are not talking about the same thing..

 

[m0aje]

Greetings JDraw,

Thanks. I'll give this a try. I am dealing with an existing database program that was written a long time ago. It is in Access 2000. I am also a novice with Access, but do like to play around with it.
Anyway, I will keep playing around with the solutions you and Mihail gave me. There is something I am missing somewhere.

Thanks again to both you and Mihail for your help and patience with me. I appreciate it.

m0aje

 

[pbaldy]

FYI, moved to reports forum.