Multiple If Statement

[TallMan]

Good Afternoon,

I have a form which an associate needs to enter in various account criteria including dates. I have written the IF statement highlighted below and everything seemed to be working untill I tried to test the if statement which test whether or not the starts_on_pip_update date is less than the current date.

Here is the snippet of code that is not working. I am not getting any errors, it is just the "OR" statement does not seem to be firing. Do you think it is a parenthesis issue?


"Or Me.Starts_On_PIP_Update < Date"

I typed "HERE IS THE ISSUE" in the code below using the quote boxes. Is there a better way to point out the code I am trying to get you to look at going forward?

Thanks Guys!

Private Sub Command96_Click()
Dim DB As DAO.Database
Set DB = CurrentDb
Dim rs As DAO.Recordset
Dim nulltest1 As Variant
Dim nulltest2 As Variant
Dim nulltest3 As Variant
Dim nulltest4 As Variant
Dim nulltest5 As Variant
Dim nulltest6 As Variant
Set nulltest1 = Me.Amount_PIP_Update
Set nulltest2 = Me.PPM_Code_PIP_Update
Set nulltest3 = Me.Pay_Date_PIP_Update
Set nulltest4 = Me.Pay_Month_PIP_Update
Set nulltest5 = Me.Starts_On_PIP_Update
Set nulltest6 = Me.Ends_On_PIP_Update
'tests to ensure starts on is populated
If Me.Starts_On_PIP_Update = "" Or IsNull(nulltest5) Or Me.Starts_On_PIP_Update = 0 Then
MsgBox "You have not submitted the PIP update information correctly. Please make sure that all of the below criteria is met." & vbNewLine & "" & vbNewLine & "- All fields have information populated EXCLUDING the 'Ends On' field." & vbNewLine & "-The 'Starts On' and 'Ends On' Date is either the 7th or 21st of the month." & vbNewLine & "-The 'Ends On' date is not less than the 'Starts On' date."
Else
    'test passed, starts on is populated, now test to see if ends on is populated
    If IsNull(nulltest6) = False And Me.Ends_On_PIP_Update <> "" And Me.Ends_On_PIP_Update <> 0 Then
        'ends on is populated
        If IsNull(nulltest1) Or Me.Amount_PIP_Update = 0 Or IsNull(nulltest2) Or Me.PPM_Code_PIP_Update = "" Or Me.PPM_Code_PIP_Update = 0 Or IsNull(nulltest3) Or Me.Pay_Date_PIP_Update = "" Or Me.Pay_Date_PIP_Update = 0 Or IsNull(nulltest4) Or Me.Pay_Month_PIP_Update = "" Or Me.Pay_Month_PIP_Update = 0 Or IsNull(nulltest5) Or Me.Starts_On_PIP_Update = "" Or Me.Starts_On_PIP_Update = 0 Or (Me.Starts_On_PIP_Update > Me.Ends_On_PIP_Update) Or Day(Me.Starts_On_PIP_Update) <> 21 And Day(Me.Starts_On_PIP_Update) <> 7 Or [quote]HERE IS THE ISSUE[/quote] ISSUE[/code]Me.Starts_On_PIP_Update < Date Or Day(Me.Ends_On_PIP_Update) <> 21 And Day(Me.Ends_On_PIP_Update) <> 7 Then
        'variety of tests not passed
        MsgBox "You have not submitted the PIP information correctly. Please make sure that all of the below criteria is met." & vbNewLine & "" & vbNewLine & "- All fields have information populated EXCLUDING the 'Ends On' field." & vbNewLine & "-The 'Starts On' and 'Ends On' Date is either the 7th or 21st of the month." & vbNewLine & "-The 'Starts On' date is not less than todays date." & vbNewLine & "-The 'Ends On' date is not less than the 'Starts On' date."
        Else
'ACTION 'all tests passed successfully, proceed to command
        Me.User_Name_PIP = Environ("username")
        SQL = ""
        SQL = SQL & " UPDATE Main_PIP_TBL "
        SQL = SQL & " SET Main_PIP_TBL.Account = '" & Me.PIP_Account & "' "
        SQL = SQL & " ,Main_PIP_TBL.Amount = '" & Me.Amount_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.PPM_Code = '" & Me.PPM_Code_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Pay_Date = '" & Me.Pay_Date_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Pay_Month = '" & Me.Pay_Month_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Starts_On = #" & Me.Starts_On_PIP_Update & "# "
        SQL = SQL & " ,Main_PIP_TBL.Ends_On = #" & Me.Ends_On_PIP_Update & "# "
        SQL = SQL & " ,Main_PIP_TBL.Funding        = '" & Me.Funding_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Field_Changed = '" & Me.Field_Changes_pip_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Update_Date    = now() "
        SQL = SQL & " ,Main_PIP_TBL.Update_User = '" & Environ("Username") & "' "
        SQL = SQL & " WHERE Main_PIP_TBL.Account      = '" & Me.PIP_Account & "' "
        SQL = SQL & "   AND Main_PIP_TBL.autoNBR      =  " & Me.AutoNBR_PIP_Update & ";"
        DB.Execute (SQL)
        Me.Main_PIP_TBL_subform.Requery
        MsgBox "Update Completed Successfully", vbInformation, "Update Complete"
        Me.Field_Changes_pip_Update.BackColor = vbWhite
        Me.Amount_PIP_Update.Value = ""
        Me.PPM_Code_PIP_Update.Value = ""
        Me.Pay_Date_PIP_Update.Value = ""
        Me.Pay_Month_PIP_Update.Value = ""
        Me.Starts_On_PIP_Update.Value = ""
        Me.Ends_On_PIP_Update.Value = ""
        Me.Funding_PIP_Update.Value = ""
        Me.Field_Changes_pip_Update.Value = ""
        Me.AutoNBR_PIP_Update = ""
        SQL = " Select * From Main_PIP_TBL where account = '" & Me.PIP_Account & "';"
        Me.Main_PIP_TBL_subform.Form.RecordSource = SQL
        'end (variety of tests)
        End If
    'ends on is not populated
    Else
        'conduct variety of tests that do not involve ends on (it is not populated)
        If IsNull(nulltest1) Or Me.Amount_PIP_Update = 0 Or IsNull(nulltest2) Or Me.PPM_Code_PIP_Update = "" Or Me.PPM_Code_PIP_Update = 0 Or IsNull(nulltest3) Or Me.Pay_Date_PIP_Update = "" Or Me.Pay_Date_PIP_Update = 0 Or IsNull(nulltest4) Or Me.Pay_Month_PIP_Update = "" Or Me.Pay_Month_PIP_Update = 0 Or IsNull(nulltest5) Or Me.Starts_On_PIP_Update = "" Or Me.Starts_On_PIP_Update = 0 Or Day(Me.Starts_On_PIP_Update) <> 21 And Day(Me.Starts_On_PIP_Update) <> 7 Then
        'variety of tests (not involving ends on) NOT passed
        MsgBox "You have not submitted the PIP update information correctly. Please make sure that all of the below criteria is met." & vbNewLine & "" & vbNewLine & "- All fields have information populated EXCLUDING the 'Ends On' field." & vbNewLine & "-The 'Starts On' and 'Ends On' Date is either the 7th or 21st of the month." & vbNewLine & "-The 'Starts on' date is not less than todays date." & vbNewLine & "-The 'Ends On' date is not less than the 'Starts On' date."
        Else
'ACTION 'variety of tests passed successfully, proceed to command
           Me.User_Name_PIP = Environ("username")
        SQL = ""
        SQL = SQL & " UPDATE Main_PIP_TBL "
        SQL = SQL & " SET Main_PIP_TBL.Account = '" & Me.PIP_Account & "' "
        SQL = SQL & " ,Main_PIP_TBL.Amount = '" & Me.Amount_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.PPM_Code = '" & Me.PPM_Code_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Pay_Date = '" & Me.Pay_Date_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Pay_Month = '" & Me.Pay_Month_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.ends_on = '' "
        SQL = SQL & " ,Main_PIP_TBL.Starts_On = #" & Me.Starts_On_PIP_Update & "# "
        SQL = SQL & " ,Main_PIP_TBL.Funding = '" & Me.Funding_PIP_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Field_Changed = '" & Me.Field_Changes_pip_Update & "' "
        SQL = SQL & " ,Main_PIP_TBL.Update_Date    = now() "
        SQL = SQL & " ,Main_PIP_TBL.Update_User = '" & Environ("Username") & "' "
        SQL = SQL & " WHERE Main_PIP_TBL.Account      = '" & Me.PIP_Account & "' "
        SQL = SQL & "   AND Main_PIP_TBL.autoNBR      =  " & Me.AutoNBR_PIP_Update & ";"
        DB.Execute (SQL)
        Me.Main_PIP_TBL_subform.Requery
        MsgBox "Update Completed Successfully", vbInformation, "Update Complete"
        Me.Field_Changes_pip_Update.BackColor = vbWhite
        Me.Amount_PIP_Update.Value = ""
        Me.PPM_Code_PIP_Update.Value = ""
        Me.Pay_Date_PIP_Update.Value = ""
        Me.Pay_Month_PIP_Update.Value = ""
        Me.Starts_On_PIP_Update.Value = ""
        Me.Ends_On_PIP_Update.Value = ""
        Me.Funding_PIP_Update.Value = ""
        Me.Field_Changes_pip_Update.Value = ""
        Me.AutoNBR_PIP_Update = ""
        SQL = " Select * From Main_PIP_TBL where account = '" & Me.PIP_Account & "';"
        Me.Main_PIP_TBL_subform.Form.RecordSource = SQL
        'end (variety of tests)
        End If
    'end (is ends on populated?)
    End If
'end (is starts on populated?)
End If
End Sub

 

[pbaldy]

That is very difficult to read all strung out that way, but I would guess it is a parentheses issue (presuming you don't have a field named "Date" that might confuse the issue). Any time you have a a mix of AND and OR, you should clarify the logic with parentheses.

 

[SteveSchapel]

TallMan,

Yes, I do think it's a parentheses error. I am not sure exactly what, because I haven't figured out what it is all about. But, for example, in this section of the code:
Me.Starts_On_PIP_Update < Date Or Day(Me.Ends_On_PIP_Update) <> 21 And Day(Me.Ends_On_PIP_Update) <> 7
... I suspect that what you really mean is:
Me.Starts_On_PIP_Update < Date Or (Day(Me.Ends_On_PIP_Update) <> 21 And Day(Me.Ends_On_PIP_Update) <> 7)

 

[TallMan]

This is extremely strange guys. I thank you for your feedback but I am unable to get this to work. What is really boggling my mind is that this code is the "update" portion of my form. I also have a "New" section on the same form which has the exact same code. The only differenct is the unbound text boxes do not have the "_Update" at the end of them. I can't find the difference between the two private subs.....

TallMan

 

[pbaldy]

In any case I would add parentheses to clarify the logic. I'm sure Access has some internal set of rules it will follow in their absence, but I wouldn't rely on them. Who knows how this will evaluate:

1 AND 2 OR 3

whereas these are explicitly defined and there will be no question, either by Access or you/another developer as to the desired logic:

(1 AND 2) OR 3
1 AND (2 OR 3)

 

[TallMan]

Hey Guys,

I did some further research and took out the majority of the OR statements. Basically a few that were working and left the one that is not working. Do you see any reason why this if statement would not work? All I am trying to do is have the code recognize if the begin date is > the end date to through a message box. Again the issue is in the last or statementment of my code below.

I checked and the fields in the table are date/time fields. Is the < or > a symbol that can not be used with date fields? I have tried playing around with all different parenthesis scenarios and no luck.

This is so strange......Thanks in advance for any additional help.

If IsNull(nulltest1) Or Me.Amount_PIP = 0 Or IsNull(nulltest2) Or Me.PPM_Code_PIP = "" Or Me.PPM_Code_PIP = 0 Or IsNull(nulltest3) Or Me.Pay_Date_PIP = "" Or Me.Pay_Date_PIP = 0 Or IsNull(nulltest4) Or Me.Pay_Month_PIP = "" Or Me.Pay_Month_PIP = 0 Or IsNull(nulltest5) Or Me.Starts_On_PIP = "" Or Me.Starts_On_PIP = 0 Or (Me.Ends_On_PIP < Me.Starts_On_PIP) Then

 

[pbaldy]

You can use > & < with date fields. I don't believe a Date/Time field can hold a Zero Length String (""), so this test will never be met:

Me.Starts_On_PIP = ""

You'd have to test for Null, or use Not IsDate().

 

[TallMan]

This is really strange. I broke it down even further and did a simple....

If Me.Ends_On_PIP < Me.Starts_On_PIP Then
msgbox "test"

and the code is still not catching the date error.......I have tried changing the format to short date and that does nothing.

 

[pbaldy]

Format should be irrelevant, as date/time data types are actually stored as a double. What are the two dates in question? Can you post the db, or a sample of it that demonstrates this problem?

 

[TallMan]

Pbaldy,

This would be my first time posting....I remember seeing somewhere on the forum a "how to attach a db" But I cannot find it. Can you point me to the link so I can post a sample db?

 

[pbaldy]

Rather than using the quick reply, click on Post Reply or Go Advanced. Down a bit you'll see a button for attachments. Compact/repair and zipping will keep most db's small enough.

 

[TallMan]

Okay,

I think you should be good to open this now.

here is a final run down:

When you open the database you will automatically be taken to the PIP Form. on this pip form is bunch of unbound text boxes that an assocaite is going to be filling out. Unfortuneatly because of errors, I need to make the database throw up a message box if a certain number of factors are incorrect. The factors are as such:

The Start date < todays date,
The End date < the start date,
All text boxes must be filled out except for the funding box and the end date,
the start and end date day values can only be the 7th and 21st of the month.....

I know this is a challenge.....it took me a while to figure this out. i actually had to go to a buddy for help in another department.

PLease let me know if you have any questions. AND THANKS A TON!!!!

TallMan

 

[pbaldy]

The first textbox has a General Date format, the second does not. I suspect that's making it get treated as text. Changing the format of the second textbox appears to fix the problem.

 

[TallMan]

Amazing!!! I cannot believe how much time I spent trying to figure that out!

Thanks A million PBaldy!! This was a huge help.

 

[pbaldy]

Sometimes it's the simplest things that trip us up. Glad we got it going for you!