Currency formatting in VBA

[Milothicus]

I'm trying to send a currency value to a word document using bookmarks, but can't get my variable to format as currency.

i have a field in a table, value, that's formatted as currency, data type currency. i have a control on my form (referenced as me.value) that's formatted as currency. in the record source query for the form, i set the format of the value field to currency.

here's my code:

Dim strvalue As Currency
strvalue = CCur(Me.Value)
Format strvalue, "currency"

I stepped through this code, and if the field on the form reads "£10,000.00", strvalue remains as "10000".

why, if i set the format as currency in 6 places, isn't strvalue displaying as currency?

 

[Rich]

strvalue = Format(CCur(Me.Value),"Currency")

I hope you don't have a control named Value

 

[Milothicus]

I did until now... didn't seem to create any problems, but i changed it anyway.

it works now, i put in the code you recommended, and changed strvalue's data type to string.

thanks for the help.

 

[Rich]

You've been lucky to get away with it, Value is a reserved word

 

[jamjarr]

Format Value as Currency

Hello I am new and need some help.

I have two fields that I want to pass to a word doc as currency fields.

I have fiddled around with my code (hoping for a hit but getting misses) with the suggested code.

My code for passing values to the word doc from a form are:

'Declare the follwing
Dim strvalue As Currency
Dim objWord As word.Application

strvalue = Format(CCur(Me.Variation), "Currency")

'Set word as an application and make it invisible
Set objWord = CreateObject("Word.Application")
objWord.Visible = False 'True is visible

'path and name of the template your are using.
objWord.Documents.Add ("C:\Temp\AccessTest.dot")

'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("bmCusDetails").Select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![frmLeaseAdjustment]![txtCusDetails]

objWord.ActiveDocument.Bookmarks("bmSubject").Select
objWord.Selection.Text = Forms![frmLeaseAdjustment]![Subject]

objWord.ActiveDocument.Bookmarks("bmVariation").Select
objWord.Selection.Text = Forms![frmLeaseAdjustment]![Variation]

'objWord.Selection.Text = Forms![frmLeaseAdjustment]![Variation]

objWord.ActiveDocument.Bookmarks("bmChange").Select
objWord.Selection.Text = Forms![frmLeaseAdjustment]![change]

objWord.ActiveDocument.Bookmarks("bmStaff").Select
objWord.Selection.Text = Forms![frmLeaseAdjustment]![Staff]

objWord.ActiveDocument.Bookmarks("bmDate1").Select
objWord.Selection.Text = Forms![frmLeaseAdjustment]![EffectiveDate]



'Word (or the document that you created with the template, will now open)
objWord.Visible = True


Set objWord = Nothing


End Sub

The two fields that I wish to change are Change and Variation.

Sorry if this is very simple but I have searched to no avail.

Thanks.

Regards

 

[jamjarr]

Format Value as Currency

Ignore my last post I have worked out the issue.

Thanks.

Post a reply if you require the way that I got it working.