Date issue in vba code

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 01:45
Joined
Jul 15, 2008
Messages
2,271
Hi Forum,

The code below returns a string used as a heading for a Statement section.

It works right up to the last sql where I am trying to add a date to the string.

Code:
Private Function LoanHeaderLabel() As String  ' Create LID Formatted as nnnnAAA being Number
                                         'and first 3 chrs of Surname LID (LDPK) is used as Variable
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim LoanID As Integer         'sqlVariable
    Dim MemberID As Integer
    Dim LIDFormat As String     'String for variable
    Dim LoanNumString As String
    Dim IssueDate As Date
    Dim DateString As String
    Dim LoanPrincipal As Currency
    
    LoanID = Me.Report.LDPK       'Loan Number from Report
    MemberID = Me.Report.ADPK      'Member Number from Report
    
                'Ensure Loan Number is 4 Digits
    LoanNumString = CStr(LoanID)       'Change Integer to String
            
        If Len(LoanNumString) = 1 Then
            LoanNumString = "000" & LoanNumString     ' If 1 chr add 3 x 0's in front
        ElseIf Len(LoanNumString) = 2 Then
            LoanNumString = "00" & LoanNumString      ' If 2 chrs add 2x 0's in front
        ElseIf Len(LoanNumString) = 3 Then
            LoanNumString = "0" & LoanNumString       ' If 3 chrs add 0 in front
        Else
            LoanNumString = LoanNumString       ' If 4 chrs, leave as is.
        End If
      
            'Find member last name (ADSurname) from Member Table (TBLACCDET) where Member ID (ADPK) exists on Report RptStatmentNewStyle
    sqlString = "SELECT TBLACCDET.ADPK, TBLACCDET.ADSurname AS LastName " & vbCrLf & _
        "FROM TBLACCDET " & vbCrLf & _
        "WHERE (((TBLACCDET.ADPK)=" & MemberID & "));"
     
          'Open Recordset
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
        
    LIDFormat = rst!LastName   'Concatenate Result of sql (LastName) to Variable LIDFormat
    LIDFormat = Left(LIDFormat, 3)
    LIDFormat = UCase(LIDFormat)
    LIDFormat = "Loan Number " & LoanNumString & LIDFormat
    
            'Find Loan Principal (LDPrin) from Loans Table (TBLLOAN) where Loan ID (LDPK) exists on Report RptStatmentNewStyle
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPrin AS LoanAmt " & vbCrLf & _
        "FROM TBLLOAN " & vbCrLf & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
            ' Reset Recordset
         Set rst = dbs.OpenRecordset(sqlString)     'Reset Recordset for Principal Amount sql
         
    LoanPrincipal = rst!LoanAmt     'Put Result of sql (LoanAmt) to Variable LoanPrincipal
    
    LIDFormat = LIDFormat & " for Principal Amount of K" & LoanPrincipal        'Add LoanPrincipal to LIDFormat
    
           [COLOR=SeaGreen]  'Find Loan Issue Date (IssueDate) from Loan Issue Status Table [/COLOR](tblLoanIssueStatus) where Loan ID (LDPK) exists on Report RptStatmentNewStyle
    sqlString = "SELECT tblLoanIssueStatus.LoanID, tblLoanIssueStatus.IssueDate AS DateIssued " & vbCrLf & _
        "FROM tnlLoanIssueStatus " & vbCrLf & _
        "WHERE (((tblLoanIssueStatus.LoanID)=" & LoanID & "));"
   
        ' Reset Recordset
         Set rst = dbs.OpenRecordset(sqlString)     'Reset Recordset for Date Issued sql
         
    IssueDate = rst!DateIssued          'Put Result of sql (DateIssued) to Variable IssueDate
    
    DateString = CStr(IssueDate)
    
    LIDFormat = LIDFormat & " Issued " & DateString
    
    LoanHeaderLabel = LIDFormat   'Variable LIDFormat as Function LoanNumberFormated() Result
    
     'Close database variables
    rst.Close
    dbs.Close

End Function

Any ideas where I have gone wrong with the Date part which starts at the Green line, Find loan Issue Date.... (I made it green)

Appreciate any advice :)
 
Found the problem.

Typo - should be "From tblLoanIssueStatus and not tnlLoanIssueStatus:eek:

Works fine now:)
 

Users who are viewing this thread

Back
Top Bottom