VBA to generate quote in MS Word (GST on the fly)

[naigy]

Hi All,

uI have written a quote routine that is working well with taking the details
from a form and exporting to word in the correct positions. I have one
problem where the item attracts GST (all have a part group code of SPVC). In
the database it is not important to log GST amounts etc but when generating a
quote for a customer it needs to be on the form which I really need in word
format unfortunately (not a report).

I have written the following routine which works for the most part but has
the problem that it doesn't just keep two decimal spaces (for cents). GST for
me is 10%.

Example of this problem is say the item price is $2.47 it will output $2.717.
In reality I want this to come out as $2.72 as it should round to the closest
full cent.

If rs.Fields("PartGroupCode") = "SPVC" Then
'Add comment to partdesc "inc GST"
If rs.Fields("Quantity") = 1 Then
oTable.Cell(Position, 2).Range.Text = rs.Fields
("PartDesc") & " inc GST"
oTable.Cell(Position, 3).Range.Text = "$" & rs.Fields
("Sale_Cost") * 1.1
Else
oTable.Cell(Position, 2).Range.Text = rs.Fields
("PartDesc") & " x " & rs.Fields("Quantity") & " @ $" & rs.Fields
("SalesPrice") * 1.1 & " ea inc GST"
oTable.Cell(Position, 3).Range.Text = "$" & rs.Fields
("Sale_Cost") * 1.1

Also need the exported value to have .00 if it is a full dollar amount (ie
$110.00 instead of $110)

I figure I need something similar to a right or left function which
references the decimal point but also need something to take into account the
roundings and the full dollar amount scenarios.

I have searched for terms that I thought may contain this information but had
no success but if there is something already if someone could point me to the
appropriate topic. Any assistance is appreciated.

 

[Alex Dybenko]

Hi,
you can round price as:

oTable.Cell(Position, 3).Range.Text = "$" & round(rs.Fields("Sale_Cost") *
1.1,2)

as for $110.00 instead of $110 - suggest that you apply corresponding format
to this cell:


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[naigy via AccessMonster.com]

Thanks Alex.

Greatly appreciate it. Both problem solved. I need to verify the rounding is
working correctly (rounding up or down as appropriate but initial checks look
good.

FYI for others
The formatting was done as follows
oTable.Cell(Position, 3).Range.Text = "$" & Format(rs.Fields("Sale_Cost") * 1.
1, "0.00")

Hi,
you can round price as:

oTable.Cell(Position, 3).Range.Text = "$" & round(rs.Fields("Sale_Cost") *
1.1,2)

as for $110.00 instead of $110 - suggest that you apply corresponding format
to this cell:

Hi All,

[quoted text clipped - 49 lines]

the
appropriate topic. Any assistance is appreciated.