Aligning Each Field Equally

[DMT Dave]

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"

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", "&emsp;") & strFontE & strBoxE
        .SendUsingAccount = olAccount
        .Display
        End With
End If

[DMT Dave]

Once posted by alignment of a, b and c doesn't look right on here but its sporadic alignment due to field data length whereas i am trying line vertically

[CJ_London]

what does your addspace function actually do? i.e. what character is it adding? - if chr(32) that won't work with html, you need to use &nbsp or a variation.

see this link
https://stackoverflow.com/questions/...ng-spaces-nbsp

personally would think adding a table would be the way forward - also in the above link

[DMT Dave]

Hi Ajax, the "AddSpace" is just to replace with &emsp (4 spaces)

this line does do the spaces:

.HTMLBody = "<br>" & strBoxS & strFontS & "<br>" & Replace(strTitle, "|", "<br>" & "<br>") & "<br>" & Replace(Replace(strBody, "|", "<br>"), "AddSpace", "&emsp;") & strFontE & strBoxE
AddSpace is not a field or function, its purely my own words to add space because initially i used "sp" my own insert for something to replace, wasn't good because the word span is in there so its my own word so i can use in html the &emsp

That all works and creates 4 spaces as &emsp should so, i was trying align the next record (<br>)

Perhaps how do I change my table to what would be a data sheet and i guess that would do the trick as each box (cell) in the table would be field records then next record would be directly under in a cell ????

Would that do it and how do i do it if so ???

Kindest

[DMT Dave]

The records are in the table see strBoxS (Start Table)

[CJ_London]

Your examples before and required look the same to me.

If you are putting in a table then you shouldn't need your addspace. Plus emps is not always recognised any - depends on the version of html. No idea if it works or not with outlook. suggest try my suggestion

Don't see how you are implementing and populating the table, I would expect to see <td> and </td>tags around each cell of the table

[DMT Dave]

Thanks Ajax, will adjust to your suggestion, again many thanks