Block if without an end if error

D

DK8

Registered User.
Local time
Yesterday, 18:02
Joined
Apr 19, 2007
Messages
72
I put the following code in the before update subroutine of my form's textbox:

If Me.STATUS_DATE.Value = "06/06/2079" Then 'display message box, added by DEK 04/27/2007, for checking that the entered date is in short date range
MsgBox "Is this the status/term date when the employee status is being changed?", vbYesNo + vbDefaultButton1 + vbDefaultButton2
End If

If Me.STATUS_DATE.Value >= "06/07/2079" Then 'display message box, added by DEK 04/27/2007, to see if the entered date is outside of short date range
MsgBox "Your entry was an invalid date! Insert Cancelled, enter the change date in the following format: 00/00/0000"
Cancel = True
Me.STATUS_DATE.Value = datStatusDate
End If

Else
If Me.STATUS_DATE.Value = Now() Then 'Inputs the current system date/time
End If

Can anybody tell me why I'm getting this message? Thanks in advance!
 
I think this bit is the problem.

DK8 said:
If Me.STATUS_DATE.Value = "06/06/2079" Then 'display message box, added by DEK 04/27/2007, for checking that the entered date is in short date range
MsgBox "Is this the status/term date when the employee status is being changed?", vbYesNo + vbDefaultButton1 + vbDefaultButton2
End If

If Me.STATUS_DATE.Value >= "06/07/2079" Then 'display message box, added by DEK 04/27/2007, to see if the entered date is outside of short date range
MsgBox "Your entry was an invalid date! Insert Cancelled, enter the change date in the following format: 00/00/0000"
Cancel = True
Me.STATUS_DATE.Value = datStatusDate
**** End If ****

Else
If Me.STATUS_DATE.Value = Now() Then 'Inputs the current system date/time
End If
Click to expand...
 
you end the second if before the else.:

...end if

else...

change the second bit to:

if ...
...datStatusDate

Elseif Me.STATUS_DATE.Value = Now() Then 'Inputs the current system date/time
End If
 
Thanks, but I'm still getting the same message, read on:

Here is the entire subroutine, with the changes you suggested:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (BlankField(Me.EMPLOYEE_SSN.Value)) Then
Me.EMPLOYEE_SSN.Value = Forms![main employee form]![EMPLOYEE SSN]
End If

If (blnAddNewRecord And Not (blnDoAdd)) Then
blnAddNewRecord = False
End If

If (BlankField(Me.SERVICE_CODE.Value)) Then
Cancel = True
End If

If (Me.STATUS_CODE.Value <> varStatusCode) Then 'changed by RRB 03/25/2003
If (Me.STATUS_CODE.Value = "06") Then
varRetVal = MsgBox("Do you want this employee to appear on the Cancellation report?", vbYesNo + vbDefaultButton1 + vbQuestion + vbApplicationModal, "CANCELLATION")

If (varRetVal = vbYes) Then
blnAddToCancelLetter = True

datStatusDate = ReturnCancellationDate() 'commented out to use the cdeffective date in verifystatus

Me.STATUS_DATE.Value = datStatusDate
Else
Me.STATUS_DATE.Value = Now()

End If

If Me.STATUS_DATE.Value = "06/06/2079" Then 'display message box, added by DEK 04/27/2007, for checking that the entered date is in short date range
MsgBox "Is this the status/term date when the employee status is being changed?", vbYesNo + vbDefaultButton1 + vbDefaultButton2
End If

If Me.STATUS_DATE.Value >= "06/07/2079" Then 'display message box, added by DEK 04/27/2007, to see if the entered date is outside of short date range
MsgBox "Your entry was an invalid date! Insert Cancelled, enter the change date in the following format: 00/00/0000"
Cancel = True
If Me.STATUS_DATE.Value = datStatusDate Then

Else
Me.STATUS_DATE.Value = Now() 'Inputs the current system date/time
End If
'************
End Sub

Can you find any other reason why this isn't working? Thank you,
 
Having taken out the comments, simply for ease of reading, I think the lines in Bold font are missing End Ifs.

Code: 
[B]  If (Me.STATUS_CODE.Value <> varStatusCode) Then   [/B]
[B]        If (Me.STATUS_CODE.Value = "06") Then[/B]
               varRetVal = MsgBox("Do you want this employee to appear on the Cancellation report?", vbYesNo + vbDefaultButton1 + vbQuestion + vbApplicationModal, "CANCELLATION")
  
                 If (varRetVal = vbYes) Then
                       blnAddToCancelLetter = True        
                       datStatusDate = ReturnCancellationDate() 'commented out to use the cdeffective date in verifystatus        
                       Me.STATUS_DATE.Value = datStatusDate
                 Else
                       Me.STATUS_DATE.Value = Now()      
                 End If
      
                 If Me.STATUS_DATE.Value = "06/06/2079" Then 
                       MsgBox "Is this the status/term date when the employee status is being changed?", vbYesNo + vbDefaultButton1 + vbDefaultButton2
                 End If
        
[B]                 If Me.STATUS_DATE.Value >= "06/07/2079" Then     [/B]              
                       MsgBox "Your entry was an invalid date! Insert Cancelled, enter the change date in the following format: 00/00/0000"
                       Cancel = True
                       If Me.STATUS_DATE.Value = datStatusDate Then
        
                       Else
                           Me.STATUS_DATE.Value = Now()       
                       End If
End Sub
 
Thank you, but my code still failed, can you or anyone help?

I have narrowed down where the problem code is below:

If Me.STATUS_DATE.Value = "06/06/2079" Then 'display message box, added by DEK 04/27/2007, for checking that the entered date is in short date range
MsgBox "Confirm that this the status/term date when the employee status is being changed"


If Me.STATUS_DATE.Value >= "06/07/2079" Then 'display message box, added by DEK 04/27/2007, to see if the entered date is outside of short date range
MsgBox "Your entry was an invalid date! Insert Cancelled, enter the change date in the following format: 00/00/0000"
Cancel = True
End If

Me.STATUS_DATE.Value = datStatusDate

Else
If Me.STATUS_DATE.Value = Now() Then 'Inputs the current system date/time
End If

I am still getting the block if without end if error message. If anyone could help pinpoint the problem, I would greatly appreciate it! Thank you
 
Removing the actual instructions from between the Ifs and End Ifs, it's easier to see.

The first line in Bold had no End If.

Code: 
[B]If Me.STATUS_DATE.Value = "06/06/2079" Then  [/B]       
      If Me.STATUS_DATE.Value >= "06/07/2079" Then 

      End If
                
Else
        If Me.STATUS_DATE.Value = Now() Then 

        End If
 
Thank you once again, but still not working

I tried adding an end if where you suggested, same thing:( I have been over and over this code. I want to fix this problem today, can anyone help? Thx.:
 
Thank you

Thanks, but I don't believe in installing extra software, unless it serves some specific purpose for our organization. Do you use that software and does it work for you?
 
First of all, using the Smart Indenter (as mentioned by RG), I found that you are missing two end if's to correspond with these two lines:

Code: 
    If (Me.STATUS_CODE.Value <> varStatusCode) Then    'changed by RRB 03/25/2003
        If (Me.STATUS_CODE.Value = "06") Then
            varRetVal = MsgBox("Do you want this employee to appear on the Cancellation report?", vbYesNo + vbDefaultButton1 + vbQuestion + vbApplicationModal, "CANCELLATION")

Second, these lines makes no sense:
If Me.STATUS_DATE.Value = "06/06/2079" Then

and
If Me.STATUS_DATE.Value >= "06/07/2079" Then

Are you really checking for a date that is 72 years into the future? I doubt that this database will survive that long.
 
If you're looking for votes, I've used Smart Indenter for years; it's a great little tool.
 
thanks

I am trying to validate if a user enterd date fits the criteria for short date. I understand the valid range for short date to be 1/1/1900 to 06/06/2079. I'm trying to test for and reject anything outside of the current date and the end of that date range. Does anyone have suggestions on how to better accomplish this? And hearing some other feedback on the smart indenter helps put my mind at ease. Thank you all in advance for any suggestions/commments.
 
DK8 said:
Do you use that software and does it work for you?
Click to expand...
Yes I do (and did on your post as did Bob) and it works great for just this sort of problem. You can see the problem in seconds. Then as for these lines:
If Me.STATUS_DATE.Value = "06/06/2079" Then
and
If Me.STATUS_DATE.Value >= "06/07/2079" Then
Click to expand...
Are you really trying to see if one string is "=" or "=>" another string? The date delimiter is an octothorp "#".
 
DK8 said:
I am trying to validate if a user enterd date fits the criteria for short date. I understand the valid range for short date to be 1/1/1900 to 06/06/2079. I'm trying to test for and reject anything outside of the current date and the end of that date range. Does anyone have suggestions on how to better accomplish this? And hearing some other feedback on the smart indenter helps put my mind at ease. Thank you all in advance for any suggestions/commments.
Click to expand...

I don't know about validating for short date. Is there a problem with just validating that it is a date using IsDate?

As for Smart Indenter I have also used that for many years and it works like a charm.
 
pbaldy said:
If you're looking for votes, I've used Smart Indenter for years; it's a great little tool.
Click to expand...
Hi Paul,
I think you were the one that turned me onto that neat add-in and I thank you *very* much.
 
further clarification

I should have mentioned before the reason this problem came up. A user entered 2207 for the year instead of 2007. This caused a SQL error: the conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. (Microsoft OLE DB Provider for SQL Server). Can anyone help? Thanks again
 
Then, you can probably still check for those using the octothorps:

If Me.STATUS_DATE = #06/06/2079# Then

etc.
...
 
How about creating your own User Defined Function (UDF) in a standard module that can validate the date to your requirements. Then the "hard coded" date requirements you are imposing are all in one location and easily maintained.
 
RuralGuy said:
How about creating your own User Defined Function (UDF) in a standard module that can validate the date to your requirements. Then the "hard coded" date requirements you are imposing are all in one location and easily maintained.
Click to expand...

makes even more sense.
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
Loop without Do stumped
Replies
6
Views
249
MajP
MajP
A
Solved VBA AfterUpdate event
2
Replies
23
Views
1,027
Isaac
Isaac
C
Cancel closing form without error message
Replies
11
Views
815
Pat Hartman
P
C
Checking if records exist
Replies
12
Views
743
cheekybuddha
cheekybuddha
Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
782
Jason Lee Hayes
Jason Lee Hayes

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom