Results 1 to 4 of 4
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    VBA End If

    Option Compare Database


    Private Sub txt_LdgAccID05_AfterUpdate()


    If DLookup("Check11", "t01CombinedEntity", "CmbEntID =" & DLookup("CmbEntID", "t01CombinedEntity", "EntTpe_ID01 =" & 11)) And Form_f02Journal.txt_DocDate05 >= DLookup("StartDate70", "T01BusinessInfo") Then
    If Not IsNull([Forms]![f02Journal]![txt_DocDate05]) Or Not IsNull(Me!VatCodePurchases05) Then
    Me!Vat05 = DLookup("VatRate01", "t02VatRate", "VatCode01='" & Me!VatCodePurchases05 & "' And #" & Format(Form_f02Journal.txt_DocDate05, "yyyy/mm/dd") & "# Between [StartDate11] And [EndDate13]")
    End If
    End Sub
    Click image for larger version. 

Name:	BlockIF.png 
Views:	22 
Size:	150.8 KB 
ID:	35839

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You have two IF/THEN statements (one nested inside the other). So you need two END IF lines at the end.
    Code:
    Private Sub txt_LdgAccID05_AfterUpdate()
        If DLookup("Check11", "t01CombinedEntity", "CmbEntID =" & DLookup("CmbEntID", "t01CombinedEntity", "EntTpe_ID01 =" & 11)) And Form_f02Journal.txt_DocDate05 >= DLookup("StartDate70", "T01BusinessInfo") Then
            If Not IsNull([Forms]![f02Journal]![txt_DocDate05]) Or Not IsNull(Me!VatCodePurchases05) Then
                Me!Vat05 = DLookup("VatRate01", "t02VatRate", "VatCode01='" & Me!VatCodePurchases05 & "' And #" & Format(Form_f02Journal.txt_DocDate05, "yyyy/mm/dd") & "# Between [StartDate11] And [EndDate13]")
            End If
        End If
    End Sub
    If you indent your code, it makes it more obvious when you are missing something.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or put your second if on the one line - but makes it even harder to read

    Code:
    Private Sub txt_LdgAccID05_AfterUpdate()
    
        If DLookup("Check11", "t01CombinedEntity", "CmbEntID =" & DLookup("CmbEntID", "t01CombinedEntity", "EntTpe_ID01 =" & 11)) And Form_f02Journal.txt_DocDate05 >= DLookup("StartDate70", "T01BusinessInfo") Then
            
            If Not IsNull([Forms]![f02Journal]![txt_DocDate05]) Or Not IsNull(Me!VatCodePurchases05) Then Me!Vat05 = DLookup("VatRate01", "t02VatRate", "VatCode01='" & Me!VatCodePurchases05 & "' And #" & Format(Form_f02Journal.txt_DocDate05, "yyyy/mm/dd") & "# Between [StartDate11] And [EndDate13]")
        
        End If
     
    End Sub
    edit: or use the underscore line break char

    Code:
    Private Sub txt_LdgAccID05_AfterUpdate()
    
        If DLookup("Check11", "t01CombinedEntity", "CmbEntID =" & DLookup("CmbEntID", "t01CombinedEntity", "EntTpe_ID01 =" & 11)) _
                And Form_f02Journal.txt_DocDate05 >= DLookup("StartDate70", "T01BusinessInfo") Then
            
                    If Not IsNull([Forms]![f02Journal]![txt_DocDate05]) _
                        Or Not IsNull(Me!VatCodePurchases05) Then _
                            Me!Vat05 = DLookup("VatRate01", "t02VatRate", "VatCode01='" & Me!VatCodePurchases05 & "' And #" & _
                                                                                                    Format(Form_f02Journal.txt_DocDate05, "yyyy/mm/dd") & "# Between [StartDate11] And [EndDate13]")
        
        End If
     
    End Sub

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Often times, the error messages you receive are cryptic, but in this case it is actually pretty good. It is telling you exactly what the issue is (you have an "IF" without an "END IF").

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums