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.
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
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
