If statement not working as it should!

[garyholc]

Ok i'm banging my head against a wall with this one.

I have a field on my form (Date to complete IA). What I want is if the date to complete IA is within 5 days of todays date, I want the field to turn orange. If the date has past, then I want it to turn red, provided that the IA Status meets certain criteria, and that the IA has not been submitted (date ia submitted).

The field turns red happily enough if the date has passed, but for example, if when testing I change the date to, say 40 days from now, the field stays orange and I cannot see why.

Ive added a msgbox to return the calculated value so I know what is being returned is correct, just the IF statement seems to ignore the value... Any ideas?

Here is the IF statement:

countdays = date_to_complete_ia - date

' if date has past, turn the field red
If countdays <= 0 And (Me.IA_Status = 4 Or Me.IA_Status = 5 Or Me.IA_Status = 7) And IsNull(Me.Date_IA_Submitted) Then
Me.Date_to_complete_IA.BackColor = 255
Else
' if date is within 5 days of now, turn it orange
If (countdays > 0 And countsdays < 6) And ((Me.IA_Status = 4 Or Me.IA_Status = 5 Or Me.IA_Status = 7) And IsNull(Me.Date_IA_Submitted)) Then
Me.Date_to_complete_IA.BackColor = 52479
Else
' leave the field white
Me.Date_to_complete_IA.BackColor = 16777215
End If
End If

 

[Rabbie]

You may need to requery and refresh your form to pick up the change of date.

 

[garyholc]

Yes i'm doing that. I am opening and closing then re-opening the form as the code is within in OnLoad event of the form. It works when I change the date to a date that has past, but not one within 5 days.

 

[Mike375]

Try writing it as a macro. In my experence macro conditions are more positive than VBA IF

If you are not real familiar with macros what you have there is a SetValue action.

Item is [Date_to_complete_IA].BackColor etc
Expression is 255

Except you drop the Me if don't reference a form name and [] textbox names

For the condition column

If countdays <= 0 And (Me.IA_Status = 4 Or Me.IA_Status = 5 Or Me.IA_Status = 7) And IsNull(Me.Date_IA_Submitted) Becomes

[countdays] <= 0 And ([IA_Status] = 4 Or [IA_Status] = 5 Or [IA_Status] = 7) And ([Date_IA_Submitted] Is Null) I don't know I messed up there but if I did the bracketing is the same as VBA

The Then Else is done by going to the next line

The macro will use the last condition that is correct

If the macro does the same as the VBA then your conditions might have "a gap" and hence no change

 

[Rabbie]

I would suggest you try stepping through you code to see exactly what is happening. Your code looks OK to me but of course it is not easy to know the effect of your other tests on Status and Datesubmitted

 

[Atomic Shrimp]

Did you copy and past your code?

Reason I ask is this:

' if date has past, turn the field red
If countdays <= 0 And (Me.IA_Status = 4 Or Me.IA_Status = 5 Or Me.IA_Status = 7) And IsNull(Me.Date_IA_Submitted) Then
Me.Date_to_complete_IA.BackColor = 255
Else
' if date is within 5 days of now, turn it orange
If (countdays > 0 And countsdays < 6) And ((Me.IA_Status = 4 Or Me.IA_Status = 5 Or Me.IA_Status = 7) And IsNull(Me.Date_IA_Submitted)) Then
Me.Date_to_complete_IA.BackColor = 52479
Else
' leave the field white
Me.Date_to_complete_IA.BackColor = 16777215
End If
End If

If that typo is in your code, it probably affects the execution of the condition...

 

[garyholc]

Thanks

However I have fixed it now by going back to basics. Just changed it to say

If (countdays = 1 or countdays = 2 etc up to 5) then


and now it works. Weird why the original IF statement wouldnt work correctly though!

Thanks
Gary

 

[Rabbie]

and now it works. Weird why the original IF statement wouldnt work correctly though!

Thanks
Gary

Not weird at all. As Mike Gurman pointed out you had a typo in your if statement. Computers can be very pedantic and do what you tell them not what you want them to do

 

[garyholc]

Did you copy and past your code?

Reason I ask is this:



If that typo is in your code, it probably affects the execution of the condition...

Ahhh sorry Mike I didnt see this reply. Yes I did cut and paste my code but i also compiled it before running it so surely it would have come up with an error saying the variable was not defined or something?

Fixed now anyway, but thanks for pointing that out, yes I did cut and paste it.

 

[Rabbie]

Ahhh sorry Mike I didnt see this reply. Yes I did cut and paste my code but i also compiled it before running it so surely it would have come up with an error saying the variable was not defined or something?

Fixed now anyway, but thanks for pointing that out, yes I did cut and paste it.

You only get the error message if you have Option Explicit set at the top of the VBA module.

Anyway glad to hear you have got it working.