Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

adding currency and percentage formatting to an email created in access VBA

Access 201 vba
creating an email in Access to send in outlook.;
I need to format the table and have certain columns with currenty and percetage formatting.. see below.
strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>CURRENT_GP</th><th>VENDOR_GUIDELINE_GP</th><th>APPROVED_PRICE</th><th>APPROVED_GP</th></tr>"

' Initialize Mailbody
MAILBODY = strhtml

With rst
  Do While Not .EOF
   ' Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & ![TARGET_GP] & " | " & ![CURRENT_PRICE] & " | " & ![CURRENT_GP] & " | " & ![VENDOR_GUIDELINE_GP] & " | " & ![APPROVED_PRICE] & " | " & ![APPROVED_GP] & vbCrLf
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & rst("TARGET_PRICE") & "</td><td>" & _
        rst("TARGET_GP") & "</td><td>" & rst("CURRENT_PRICE") & "</td><td>" & rst("CURRENT_GP") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"
   .MoveNext
  Loop
  
End With

Open in new window


Fields that need money formatting and percentage formatting
I.E.
TARGET_PRICE   TARGET_GP   CURRENT_PRICE   CURRENT_GP       : SHOULD BE
$2.00                      65%              $4.89                      45%
$5.67                      35%              $3.21                       32%


Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>CURRENT_GP</th><th>VENDOR_GUIDELINE_GP</th><th>APPROVED_PRICE</th><th>APPROVED_GP</th></tr>"

' Initialize Mailbody
MAILBODY = strhtml

With rst
  Do While Not .EOF
   ' Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & ![TARGET_GP] & " | " & ![CURRENT_PRICE] & " | " & ![CURRENT_GP] & " | " & ![VENDOR_GUIDELINE_GP] & " | " & ![APPROVED_PRICE] & " | " & ![APPROVED_GP] & vbCrLf
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & _
        format(rst("TARGET_GP"),"0%") & "</td><td>" & format(rst("CURRENT_PRICE"), "Currency") & "</td><td>" & Format(rst("CURRENT_GP"),"0%") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"
   .MoveNext
  Loop
  
End With

Open in new window

Regards
0
 
chaauCommented:
Use the Format for this. For currency and percent there are predefined formats.
So, the code will be:
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & Format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & _
        Format(rst("TARGET_GP"), "Percent") & "</td><td>" & rst("CURRENT_PRICE") & "</td><td>" & rst("CURRENT_GP") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"

Open in new window

0
 
FordraidersAuthor Commented:
Thanks Liked both methods.!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now