format output

[Joanne]

Could some please help me to format the output from my query to my
document.
I need the output to be in this format $1,111.00
In the table design, I chose this format type, and it shows up that
way in my form and in my table, but does not print that way in my
document bookmark.

IN the query grid I tried this without success:
StartPayRate: Format ("#,##0.00")
and I tried this
StartPayRate: Format([payrate], ("$0,000.00"))
again without success.
I got this from the help file about custom currency formatting but
clearly I am not understanding what I am reading.
Your help is much appreciated
Joanne

 

[Duane Hookom]

What do you mean by "without success"? We can't see your results or what you
have found is wrong. This may be what you want:

StartPayRate:Format([payrate],("$#,###.00"))

 

[Joanne]

Duane
By without success I mean that the query will not run, I get a syntax
error on my format statement, or I get the actual $#,000.00 in my
table.
I will try your suggestion
Thanks very much
Joanne

 

[Duane Hookom]

My reply should have been:
StartPayRate:Format([payrate],"$#,###.00")
Don't know why I had the extra ()s.

--
Duane Hookom
MS Access MVP

Duane
By without success I mean that the query will not run, I get a syntax
error on my format statement, or I get the actual $#,000.00 in my
table.
I will try your suggestion
Thanks very much
Joanne

What do you mean by "without success"? We can't see your results or what you
have found is wrong. This may be what you want:

StartPayRate:Format([payrate],("$#,###.00"))

 

[Joanne]

Duane
I used your method in the query grid but now when I try to enter
something in the field on my input form, it won't take any input.

Not understanding much about this, I went back to the query grid and
changed [payrate] to [startpayrate] but then I get an error 'circular
argument'. So I tried using payrate:format ([startpayrate],
("$#,###.00")) but then I get a parameter input box asking for payrate
when I try running the query. Actually, I am lost here.

Can you please explain to me how I can get this to work properly?
Thanks a bunch
Joanne

 

[Duane Hookom]

I thought the results of the query were going to a document, not a form
where the value needed to be edited. When a query feeds a form, I NEVER
apply any formatting in the query. All formatting is done in the text box
properties.

--
Duane Hookom
MS Access MVP

Duane
I used your method in the query grid but now when I try to enter
something in the field on my input form, it won't take any input.

Not understanding much about this, I went back to the query grid and
changed [payrate] to [startpayrate] but then I get an error 'circular
argument'. So I tried using payrate:format ([startpayrate],
("$#,###.00")) but then I get a parameter input box asking for payrate
when I try running the query. Actually, I am lost here.

Can you please explain to me how I can get this to work properly?
Thanks a bunch
Joanne

What do you mean by "without success"? We can't see your results or what you
have found is wrong. This may be what you want:

StartPayRate:Format([payrate],("$#,###.00"))

 

[Joanne]

Duane
In the text box for the currency input I have chosen this formatting
$1,111.00 and on my input form that is how it is formatted, as well as
in my table, it holds that format also.
But when I use the data from my table to fill bookmarks in MSWord docs
thru automation, the dollar amount in my word document is simply 1111.
This is what I am trying to fix. Can you help me with this please?
Joanne
ps I thought it was too late for a reply on my original post of 10-9
so I reposted my question in a new thread for today. Please ignore my
ignorance on how these forums work.

 

[Duane Hookom]

I'm lost since you have both a query that feeds a MSWord doc as well as an
input form. Where does the field display and does it need to be edited?

 

[Joanne]

Duane
I have a field in my table called "StartPayRate"
I have a control on my input form to accept the starting pay rate
input, it is formatted as currency both in the table design and in the
properties of the control on the form, in this manner $1,111.00.
The control source for the control on the input form is the
'startpayrate' field in the table.
I then use the data in the table to fill bookmarks in MSWord docs.
The input form shows the correct format, ie $1,111.00
The table stores the number in the correct format, again $1,111.00
But the output in the bookmark on the msword doc is 1111
Actually it is in the msword doc that I need to have the formatted
output, I really don't care at all about the format in the table
because the user will never see it, and the fact that the control on
the input form is in proper format is great because the user can see
it and recognize it.
I hope I am making it clear to you what I am trying to do so that you
can better help me out here.
Thank you
Joanne

 

[Duane Hookom]

The value is always store as a number with no dollar signs or commas etc. A
number is a number is a number. It doesn't make any difference how the value
is displayed in the table or in a form.

I expect you have code to find and update the bookmark. This is where you
can find your solution. If you can't figure this out, come back with your
code.

 

[Joanne]

Duane
Here is the code I use to update my bookmarks. Only 1 field is a
currency field, the rest are all dates and text.

Function UpdateBookmark(BookmarkToUpdate As String, TextToUse As
String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Function

This is my best guess how this formatting would be accomplished.
After the bookmark has been updated, I'm thinking I need an if then
end if statement to check and see if the field is currency, and if it
is then apply the print image $#,##0.00 to force the formatting. I am
not exactly sure how to write this properly.

Thanks for all your time and help. I really appreciate it.
Joanne

 

[Duane Hookom]

Where is the TextToUse coming from? Also, I think you can format fields
within Word.

 

[Joanne]

TextToUse is coming from the field in my access table that is of type
currency. I was reading about formatting fields in word, is a
bookmark the same as a field? Again, I am not sure how to do it. They
talked about using autoformat but I don't know how to put the
formatting in the bookmark. I can't use the number needing to be
formatted as currency in an autoformat because it changes with each
use of the program, as in example: 22 being the number coming into the
bookmark from the table field, and autoformat would have an entry to
change the 22 to $22.00 (or autocorrect could do this) but like I
said, the number changes with each use of the app, so that approach is
not any good in this instance.

Joanne

 

[Duane Hookom]

I am not sure about formatting in the bookmark.
You haven't stated how you get the value from the table. You must format the
value from the table prior to passing it to the function. Can we see the
code that opens the table and calls the function UpdateBookmark()?

 

[Joanne]

Function SetBkms()
Dim oWordapp As Object
Dim oDocName As Object
Dim oRst As DAO.Recordset
Dim BsSql As String 'sql stmt to get docs with bookmarks
Dim sfilename As String
Dim BMRange As Range
'Open instance of MSWord
Set oWordapp = CreateObject("Word.Application")
oWordapp.Visible = False

BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList" &
_
" WHERE tblDocumentList.Bookmarks = True "
'AND tblDocumentList.GroupName = '" & sGroupName = "'"
'Open the recordset that is based on the return of the sql query
Set oRst = CurrentDb.OpenRecordset(BsSql)

'Check for records in recordset. If there are records, do until end
of file
If Not oRst Is Nothing Then
Do While Not oRst.EOF
sfilename = "" & oRst("DocNamePath")
'If the file is actually there (checking in case someone deleted it)
If Len(Dir$(sfilename)) > 0 Then
'open doc and if it opened successfully
Set oDocName = oWordapp.Documents.Open(sfilename)
If Not oDocName Is Nothing Then

'Set the bookmarks in the doc
With oWordapp.ActiveDocument.Bookmarks
If oDocName.Bookmarks.Exists("StartPayRate") = True Then
UpdateBookmark "StartPayRate", Nz(Me!StartPayRate, "")
End If

End If
End With
oDocName.Save
DoEvents
oDocName.Close
Set oDocName = Nothing
oRst.MoveNext 'Get the next file in record set and run thru the above
loop again
End If
End If
Loop
End If
oWordapp.Quit
oRst.Close
Set oWordapp = Nothing
Set oDocName = Nothing
Set oRst = Nothing
End Function

Function UpdateBookmark(BookmarkToUpdate As String, TextToUse As
String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Function

This is the way I am setting the bookmarks - I included setting only
the bookmark in question.

Thanks again
Joanne

 

[Duane Hookom]

With oWordapp.ActiveDocument.Bookmarks
If oDocName.Bookmarks.Exists("StartPayRate") = True Then
UpdateBookmark "StartPayRate", Format(Nz(Me!StartPayRate, ""),"$#,###.00")
End If

 

[Joanne]

Thank you so much Duane