Compare two dates in IF statement

[Sabosis]

Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....

 

[Piet Linden]

Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....

Post the expression for your IIF statement.

IIF(DateDiff("d",SomeDate, Date())>180, "Do X", "Do Y") ???

If you need an event to be fired, you need to use VBA

 

[Dale Fye]

Or you could use the following:

if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
'do X
else
'do Y
endif

This will give you 6 full months from today, instead of using a # of days.

 

[Sabosis]

Or you could use the following:

if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
   'do X
else
   'do Y
endif

This will give you 6 full months from today, instead of using a # of days..

----
HTH
Dale

Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -

- Show quoted text -

How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.

If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function

 

[Dale Fye]

What do you mean by "didn't work correctly in my report"?

Where did you put this in the report? That's a pretty complicated IF
statement.

Did you really mean to use <=Dateadd("m", -6, Date) ? This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.

As far as I can tell, the syntax of your IF statement is correct. But
because of the OR clauses, you may be getting results that are different than
what you want.

----
HTH
Dale

Or you could use the following:

if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
'do X
else
'do Y
endif

This will give you 6 full months from today, instead of using a # of days..

----
HTH
Dale

Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -

- Show quoted text -

How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.

If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function

 

[Sabosis]

What do you mean by "didn't work correctly in my report"?

Where did you put this in the report?  That's a pretty complicated IF
statement.

Did you really mean to use <=Dateadd("m", -6, Date) ?  This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.

As far as I can tell, the syntax of your IF statement is correct.  But
because of the OR clauses, you may be getting results that are different than
what you want.

----
HTH
Dale

Or you could use the following:
if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
   'do X
else
   'do Y
endif
This will give you 6 full months from today, instead of using a # of days..
----
HTH
Dale
:
Hello-
What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -
- Show quoted text -

How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.

If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -

- Show quoted text -

Dale-

That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.

Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......

 

[Sabosis]

What do you mean by "didn't work correctly in my report"?

Where did you put this in the report?  That's a pretty complicated IF
statement.

Did you really mean to use <=Dateadd("m", -6, Date) ?  This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.

As far as I can tell, the syntax of your IF statement is correct.  But
because of the OR clauses, you may be getting results that are different than
what you want.

Or you could use the following:
if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
   'do X
else
   'do Y
endif
This will give you 6 full months from today, instead of using a # of days..
----
HTH
Dale
:
Hello-
What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I wantto
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -
- Show quoted text -
How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.
If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -

- Show quoted text -

Dale-

That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.

Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......- Hide quoted text -

- Show quoted text -

I tried to split the expression up in the following way. It is finding
the only rep on the report that does have a corrective date greater
than 6 months ago, but is displaying a "#error" on her record instead
of returning a "q". Any ideas?

Private Function LevelDown()

If ([CorrectiveDate] >= DateAdd("m", -6, Date)) Then
LevelDown = ""
Else
Call LevelDown1
End If
End Function
..............................................................................................
Private Function LevelDown1()

If ([CorrLevelID] = 1) And ([TotalOccurances] < 6 Or [UnpaidTime] <
16) Or ([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 2) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 3) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 4) And ([TotalOccurances] < 2 Or [UnpaidTime]
< 8) Then
LevelDown = "q"
End If
End Function

 

[Dale Fye]

Where did you put this code?

I'm thinking that it should be in the Format event of the detail section of
your report.

Is there any chance that one of the fields ([CorrectiveDate], [CorrLevelID],
[TotalOccurances], [UnpaidTime] could be NULL?

I'm still concerned about the logic you are using in LevelDown1, but my
guess is that the problem you are having is that in your LevelDown1 function
you are trying to set a value for LevelDown, not LevelDown1.

I've broken your logic up into smaller bites so you can see what your query
is really doing. Take a look at this and see if this logic makes sense.

Public Function LevelDown() as string

Set the default value of LevelDown
LevelDown = ""

IF [CorrectiveDate] <= DateAdd("m", -6, date()) then
Select Case [CorrLevelID]
Case 1
if ([TotalOccurances] < 6 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 4 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 2
if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 3
if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 4
if ([TotalOccurances] < 2 OR [UnpaidTime] < 8) then
LevelDown = "q"
Endif
End Select
End if
End function

----
HTH
Dale

What do you mean by "didn't work correctly in my report"?

Where did you put this in the report? That's a pretty complicated IF
statement.

Did you really mean to use <=Dateadd("m", -6, Date) ? This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.

As far as I can tell, the syntax of your IF statement is correct. But
because of the OR clauses, you may be getting results that are different than
what you want.
:
Or you could use the following:

if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
'do X
else
'do Y
endif

This will give you 6 full months from today, instead of using a # of days..
:
Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -

- Show quoted text -

How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.

If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -

- Show quoted text -

Dale-

That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.

Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......- Hide quoted text -

- Show quoted text -

I tried to split the expression up in the following way. It is finding
the only rep on the report that does have a corrective date greater
than 6 months ago, but is displaying a "#error" on her record instead
of returning a "q". Any ideas?

Private Function LevelDown()

If ([CorrectiveDate] >= DateAdd("m", -6, Date)) Then
LevelDown = ""
Else
Call LevelDown1
End If
End Function
..............................................................................................
Private Function LevelDown1()

If ([CorrLevelID] = 1) And ([TotalOccurances] < 6 Or [UnpaidTime] <
16) Or ([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 2) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 3) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 4) And ([TotalOccurances] < 2 Or [UnpaidTime]
< 8) Then
LevelDown = "q"
End If
End Function

 

[Sabosis]

Where did you put this code?

I'm thinking that it should be in the Format event of the detail section of
your report.

Is there any chance that one of the fields ([CorrectiveDate], [CorrLevelID],
[TotalOccurances], [UnpaidTime] could be NULL?

I'm still concerned about the logic you are using in LevelDown1, but my
guess is that the problem you are having is that in your LevelDown1 function
you are trying to set a value for LevelDown, not LevelDown1.

I've broken your logic up into smaller bites so you can see what your query
is really doing.  Take a look at this and see if this logic makes sense..

Public Function LevelDown() as string

    Set the default value of LevelDown
    LevelDown = ""

    IF [CorrectiveDate] <= DateAdd("m", -6, date()) then
         Select Case [CorrLevelID]
             Case 1
                  if ([TotalOccurances] < 6 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 4 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 2
                  if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 3
                  if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 4
                  if ([TotalOccurances] < 2 OR [UnpaidTime] < 8) then
                      LevelDown = "q"
                  Endif
         End Select
    End if
End function

----
HTH
Dale

What do you mean by "didn't work correctly in my report"?
Where did you put this in the report?  That's a pretty complicated IF
statement.
Did you really mean to use <=Dateadd("m", -6, Date) ?  This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 monthsin the
future, as your original post stated.
As far as I can tell, the syntax of your IF statement is correct.  But
because of the OR clauses, you may be getting results that are different than
what you want.
----
HTH
Dale
:
Or you could use the following:
if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
   'do X
else
   'do Y
endif
This will give you 6 full months from today, instead of using a# of days..
----
HTH
Dale
:
Hello-
What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -
- Show quoted text -
How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.
If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -
- Show quoted text -
Dale-
That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.
Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......- Hide quoted text -
- Show quoted text -

I tried to split the expression up in the following way. It is finding
the only rep on the report that does have a corrective date greater
than 6 months ago, but is displaying a "#error" on her record instead
of returning a "q". Any ideas?

Private Function LevelDown()

If ([CorrectiveDate] >= DateAdd("m", -6, Date)) Then
LevelDown = ""
Else
Call LevelDown1
End If
End Function
............................................................................­...................
Private Function LevelDown1()

If ([CorrLevelID] = 1) And ([TotalOccurances] < 6 Or [UnpaidTime] <
16) Or ([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 2) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 3) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 4) And ([TotalOccurances] < 2 Or [UnpaidTime]
< 8) Then
LevelDown = "q"
End If
End Function- Hide quoted text -

- Show quoted text -

Dale, I like your approach and will test it tonight. Yes, Unpaid time
could be null when the report is run for some employees. I will let
you know if this works.

Thanks-

Scott

 

[Sabosis]

Where did you put this code?

I'm thinking that it should be in the Format event of the detail section of
your report.

Is there any chance that one of the fields ([CorrectiveDate], [CorrLevelID],
[TotalOccurances], [UnpaidTime] could be NULL?

I'm still concerned about the logic you are using in LevelDown1, but my
guess is that the problem you are having is that in your LevelDown1 function
you are trying to set a value for LevelDown, not LevelDown1.

I've broken your logic up into smaller bites so you can see what your query
is really doing.  Take a look at this and see if this logic makes sense.

Public Function LevelDown() as string

    Set the default value of LevelDown
    LevelDown = ""

    IF [CorrectiveDate] <= DateAdd("m", -6, date()) then
         Select Case [CorrLevelID]
             Case 1
                  if ([TotalOccurances] < 6 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 4 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 2
                  if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 3
                  if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
                      LevelDown = "q"
                  Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
                      LevelDown = "q"
                  Endif
             Case 4
                  if ([TotalOccurances] < 2 OR [UnpaidTime] < 8) then
                      LevelDown = "q"
                  Endif
         End Select
    End if
End function

What do you mean by "didn't work correctly in my report"?
Where did you put this in the report?  That's a pretty complicated IF
statement.
Did you really mean to use <=Dateadd("m", -6, Date) ?  This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.
As far as I can tell, the syntax of your IF statement is correct. But
because of the OR clauses, you may be getting results that are different than
what you want.
----
HTH
Dale
:
Or you could use the following:
if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
   'do X
else
   'do Y
endif
This will give you 6 full months from today, instead of usinga # of days..
----
HTH
Dale
:
Hello-
What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text-
- Show quoted text -
How about if you need to add a second condition using an AND inthe
expression? I have the following expression, but it did not work
correctly in my report.
If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -
- Show quoted text -
Dale-
That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.
Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......- Hide quoted text -
- Show quoted text -
I tried to split the expression up in the following way. It is finding
the only rep on the report that does have a corrective date greater
than 6 months ago, but is displaying a "#error" on her record instead
of returning a "q". Any ideas?
Private Function LevelDown()
If ([CorrectiveDate] >= DateAdd("m", -6, Date)) Then
LevelDown = ""
Else
Call LevelDown1
End If
End Function
............................................................................­­...................
Private Function LevelDown1()
If ([CorrLevelID] = 1) And ([TotalOccurances] < 6 Or [UnpaidTime] <
16) Or ([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 2) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 3) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 4) And ([TotalOccurances] < 2 Or [UnpaidTime]
< 8) Then
LevelDown = "q"
End If
End Function- Hide quoted text -

- Show quoted text -

Dale, I like your approach and will test it tonight. Yes, Unpaid time
could be null when the report is run for some employees. I will let
you know if this works.

Thanks-

Scott- Hide quoted text -

- Show quoted text -

Dale-

The expression seems to work. Is there anything that needs to be done
to cover null values?

 

[Dale Fye]

It depends, what do you want to do if the [UnpaidTime] is NULL, you could
change all of the expressions that use [UnpaidTime] to NZ([UnpaidTime], 0) if
a NULL is equivalent to 0. It would look like:

NZ([UnpaidTime], 0)

----
HTH
Dale

Where did you put this code?

I'm thinking that it should be in the Format event of the detail section of
your report.

Is there any chance that one of the fields ([CorrectiveDate], [CorrLevelID],
[TotalOccurances], [UnpaidTime] could be NULL?

I'm still concerned about the logic you are using in LevelDown1, but my
guess is that the problem you are having is that in your LevelDown1 function
you are trying to set a value for LevelDown, not LevelDown1.

I've broken your logic up into smaller bites so you can see what your query
is really doing. Take a look at this and see if this logic makes sense.

Public Function LevelDown() as string

Set the default value of LevelDown
LevelDown = ""

IF [CorrectiveDate] <= DateAdd("m", -6, date()) then
Select Case [CorrLevelID]
Case 1
if ([TotalOccurances] < 6 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 4 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 2
if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 3
if ([TotalOccurances] < 4 OR [UnpaidTime] < 16) then
LevelDown = "q"
Elseif ([TotalOccurances] < 2 AND [UnpaidTime] < 8) Then
LevelDown = "q"
Endif
Case 4
if ([TotalOccurances] < 2 OR [UnpaidTime] < 8) then
LevelDown = "q"
Endif
End Select
End if
End function
:
On Apr 21, 12:58 pm, Dale Fye <[email protected]> wrote:

What do you mean by "didn't work correctly in my report"?

Where did you put this in the report? That's a pretty complicated IF
statement.

Did you really mean to use <=Dateadd("m", -6, Date) ? This means that the
[CorrectiveDate] is more 6 months or more in the past, not 6 months in the
future, as your original post stated.

As far as I can tell, the syntax of your IF statement is correct. But
because of the OR clauses, you may be getting results that are different than
what you want.
:
Or you could use the following:

if me.[CorrectiveDate] > DateAdd("m", 6, Date()) Then
'do X
else
'do Y
endif

This will give you 6 full months from today, instead of using a # of days..
:
Hello-

What is the best way to compare two dates using an IF statement in
VBA. On my report, I have a field called "CorrectiveDate". I want to
use an IF THEN statment to use today's date, and if six months greater
than todays date, then do X, ELSE do Y. What is the best way to write
this out, I have been having troubles....- Hide quoted text -

- Show quoted text -

How about if you need to add a second condition using an AND in the
expression? I have the following expression, but it did not work
correctly in my report.

If ([CorrectiveDate] <= DateAdd("m", -6, Date)) And ([CorrLevelID] =
1) And ([TotalOccurances] < 6 Or [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
Else
LevelDown = ""
End If
End Function- Hide quoted text -

- Show quoted text -

That is correct. The Corrective Date field in my report should be
compared against current date, and if the Corrective Date is more than
six months ago, then the field in my report called LevelDown should
populate a "q". The LevelDown field is formatted as Wingdings3, so it
actually displays a down arrow in the field. The date isssue is the
first part of the expression as you can see, then it looks at other
report fields to see if additional criteria are met.
The problem I am having is that I am getting a down arrow in the field
when the CorrectiveDate is not six month or more in the past on some
records. I have another expression called LevelUp which does the same
process, but does not use the CorrectiveDate. It works fine, just
evaluating the corrective level and combinations of TotalOccurances
and UnpaidTime. It is the LevelDown that I am having issues with.

Is there a way to break this expression apart and have the expression
qualify the date first as a stand alone expression, then if that logic
is true, move onto the next step and start evaluation the corrective
level, TotalOccurances and Unpaid time combinations as I do for the
LevelUp part of the report? It just seems that once I added the date
part to LevelDown, things got too complicated.......- Hide quoted text -

- Show quoted text -

I tried to split the expression up in the following way. It is finding
the only rep on the report that does have a corrective date greater
than 6 months ago, but is displaying a "#error" on her record instead
of returning a "q". Any ideas?

Private Function LevelDown()

If ([CorrectiveDate] >= DateAdd("m", -6, Date)) Then
LevelDown = ""
Else
Call LevelDown1
End If
End Function
............................................................................­­...................
Private Function LevelDown1()

If ([CorrLevelID] = 1) And ([TotalOccurances] < 6 Or [UnpaidTime] <
16) Or ([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 2) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 3) And ([TotalOccurances] < 4 Or [UnpaidTime]
< 16) Or ([TotalOccurances] < 2 And [UnpaidTime] < 8) Then
LevelDown = "q"
ElseIf ([CorrLevelID] = 4) And ([TotalOccurances] < 2 Or [UnpaidTime]
< 8) Then
LevelDown = "q"
End If
End Function- Hide quoted text -

- Show quoted text -

Dale, I like your approach and will test it tonight. Yes, Unpaid time
could be null when the report is run for some employees. I will let
you know if this works.

Thanks-

Scott- Hide quoted text -

- Show quoted text -

Dale-

The expression seems to work. Is there anything that needs to be done
to cover null values?