VBA convert number to string but include trailing Zero

[Snowflake68]

I have a csv file that I import into a table which contains costs of delivery. I import this table into my application on a regular basis as and when there is a cost change.

The table stores the values as Double and is formated as #.## to 2 decimal places. This shows a value for example as 2.20 but stores it as 2.2 which I understand BUT;

In part of my application I insert the carriage cost into the body of an email and I want the text to say 2.20 not 2.2

I have tried to declare my variables using either of the two methods below but neither work and dont put the trailing zero on.

Dim strCarriageCost as String
strCarriageCost = Dlookup("CarriageCost","tbl_CustomerCosts")

Dim strCarriageCost as Double
strCarriageCost = Format(Dlookup("CarriageCost","tbl_CustomerCosts") ,"0.00")

Can anyone help me please?

 

[gemma-the-husky]

Try using currency rather than double.

I would have thought the second eaxmple you quoted would work, though.

 

[Minty]

Definitely a second vote for using currency data type, but I would try to use "Fixed" as it defaults to two decimal places;

Format(Dlookup("CarriageCost","tbl_CustomerCosts") ,"Fixed")

 

[gemma-the-husky]

fwiw, I just tried

?format(12,"0.00")
12.00

 

[Gasman]

Both Currency and Double give the same result when applied to the Dim statement.?

I'm a little puzzled here as I have read many times on this site that the Format function returns a string.?
If the variable is defined as string it works as required.

 

[Snowflake68]

I have tried Currency both setting the table properties and declaring the variable as Currently and although it appears to store the value in the table OK it still only returns the variable without the trailing zero.

this is driving me nuts as it should be simply really.

 

[Snowflake68]

Right I have found a solution.

Set the table properties as Currency but declare the variable as a string but also format it as "0.00".

Thanks to everyone for their input, we got there in the end.

Dim strCarriageCost As String
strCarriageCost = Format(Dlookup("CarriageCost","tbl_CustomerCosts") ,"0.00")