Hi Guys, I have successfully written some code that will out put total and average costings between selected periods, all works great, the only additional finishing (tidy up) would be to align the fields with the next line, how can this be done please ?
Note: i have not put the full code on as its quite long and not as if it's not working because it is, it's just a question of alignment
Currently the result is something like:
a a a a a a a
b b b b b b b
c c c c c c c c
whereas i would like to align:
a a a a a a a
b b b b b b b
c c c c c c c
etc
I have no doubt its because of the length of field results ie: more characters in December than June and different results making various length for each line
If there is a method to align all regardless of field length would be fab ?
i am already adding 4 spaces by replacing "AddSpace"
Code:
strBoxS = "<table style='text-align:left;border:3px solid black;font-family:Arial;border-collapse:collapse;padding:25px'><th>" & "FUEL PRICES" & "</th><tr style='background:white;mso-highlight:blue'>"strMarkS = "<span style='background:yellow'>"
strMarkE = "</span>"
strBoxE = "</tr></table>"
strFontS = "<font size='3'>"
strFontE = "</font>"
If lYearStart < lYearEnd Then
strTitle = "Fuel Prices Between " & lYearStart & " And " & lYearEnd & "|"
strSubj = "Fuel Prices Between " & lYearStart & " And " & lYearEnd
strSQL = "SELECT tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo, Sum(tblExpenses.TotalAmount) AS SumOfTotalAmount, Sum(tblExpenses.Litres) AS SumOfLitres, " _
& "Avg(tblExpenses.PricePerLitre) AS AvgOfPricePerLitre " _
& "From tblExpenses " _
& "GROUP BY tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo " _
& "HAVING (((tblExpenses.ItemRequired) = '" & strItem & "') AND ((tblExpenses.Year) Between " & lYearStart & " And " & lYearEnd & ")) " _
& "ORDER BY Avg(tblExpenses.PricePerLitre) DESC;"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do Until rs.EOF
If rs.Fields("MonthNo") = MonthNow Then
strMonth = "<span style='background:yellow'>" & MonthName(rs.Fields("MonthNo")) & "</span>"
End If
If rs.Fields("MonthNo") <> MonthNow Then
strMonth = MonthName(rs.Fields("MonthNo"))
End If
strBody = strBody & rs.Fields("ItemRequired") & "AddSpace" & strMonth & " " & rs.Fields("Year") & _
"AddSpace" & Format(rs.Fields("SumOfTotalAmount"), "Currency") & _
"AddSpace" & "Litres: " & rs.Fields("SumOfLitres") & "AddSpace" & "PPL " & Format(rs.Fields("AvgOfPricePerLitre"), "Currency") & "|"
rs.MoveNext
Loop
Set olItem = olApp.CreateItem(olMailItem)
Set olAccount = olApp.Session.Accounts.Item(1)
With olItem
.To = strMailTo
.subject = strSubj
.HTMLBody = "<br>" & strBoxS & strFontS & "<br>" & Replace(strTitle, "|", "<br>" & "<br>") & "<br>" & Replace(Replace(strBody, "|", "<br>"), "AddSpace", " ") & strFontE & strBoxE
.SendUsingAccount = olAccount
.Display
End With
End If