Date format on export to Word 1

[SmallTime]

I have a number of fields in a report that I export to word as bookmarks. All works well expect for the date field. I've formatted this in my report as dddd d mmm yyyy (say Tuesday 13 Dec 2005). However, when exported to Word it displayed as 13/12/2005.

Is this an issue I need to tackle in word or can it be done in Access? All pointers are welcome and appreciated.

Kind regards

 

[THWatson]

SmallTime
Two questions...
1. How are you setting the formatting for the date field in Access?

2. Are you Exporting as an RTF document?

If so, the formatting stayed in a test document I exported.

Tom

 

[SmallTime]

Thanks for the quick response

The date format in access is dddd d mmm yyyy. However, even if I use the 'long date' format the result's the same.

I'm passing the data to a Word document (dot) as a bookmark, to make things easier to understand here's an example of part of the relevant code. (TxtDate is the field in question).


Dim TxtDate As Date

Set objWord = New Word.Application

With objWordDoc.Bookmarks
.Item("TxtDate").Range.Text = Nz(Me.TxtDate)
End With


Many Thanks

 

[THWatson]

SmallTime
It appears to me that you must be using Access 2003. Correct?

If so, the commands for doing what you are wanting to do are a bit different from Access 2000, so I am unable to emulate exactly what you want. Sorry.

Tom

 

[SmallTime]

I'm using Access 2000.

No worries if you can't figure it. Maybe someone else will have a go

Thanks for taking a look.

Have a pleasant New Year

Regards

 

[THWatson]

SmallTime
Oh, so it should work in Access 2000.

Mine errored out on "New Word.Application"

Maybe it's a matter of references needing to be set at my end.

Are you running this from a command button on a form? If so, could you post the complete code behind the command button?

Tom

 

[SmallTime]

Yes you need a ref to Microsoft Word (X) Object Library and also a Word Template to place the bookmarks in
For your ref here's the whole code

Dim objWord As Word.Application
Dim doc As Word.Document
Dim objWordDoc As Object
Dim bolOpenedWord As Boolean
Dim Datex As String
Dim fname As String
Dim savePath As String
Dim NameForFile As String
Dim TxtOurRef As String
Dim TxtDate As String
Dim Fullname As String
Dim TxtAdLine1 As String
Dim PostCode As String
Dim Text110 As String ' Dear …
Dim TxtPara1 As String
Dim User As String
Dim TxtPossition As String
Dim ScreenName As String

Set objWord = New Word.Application
objWord.Visible = False
Set objWordDoc = objWord.Documents.Add(Template:="C:\MyTemplate.dot", NewTemplate:=False)

With objWordDoc.Bookmarks
.Item("TxtOurRef").Range.Text = Nz(Me.TxtOurRef)
.Item("TxtDate").Range.Text = Nz(Me.TxtDate)
.Item("Fullname").Range.Text = Nz(Me.Fullname)
.Item("TxtAdLine1").Range.Text = Nz(Me.TxtAdLine1)
.Item("PostCode").Range.Text = Nz(Me.PostCode)
.Item("ScreenName").Range.Text = Nz(Me.ScreenName)
.Item("Text110").Range.Text = Nz(Me.Text110)
.Item("TxtPara1").Range.Text = Nz(Me.TxtPara1)
.Item("User").Range.Text = Nz(Me.Caseworker)
.Item("TxtPossition").Range.Text = Nz(Me.TxtPossition)
End With

savePath = "C:\MyNewDoc"
'MsgBox savePath
Datex = Mid$(Now(), 1, 2) & Mid$(Now(), 4, 2) & Mid$(Now(), 9, 2)
'MsgBox Datex
NameForFile = Me.NameForFile
fname = "DocName " & Me.Fullname & "_" & NameForFile & "_" & Datex & ".doc"
'MsgBox fname
NameForFile = savePath & "\" & fname
'MsgBox NameForFile
'doc.Fields.Update

objWord.ActiveDocument.SaveAs NameForFile
'objWord.ActiveDocument.Close SaveChanges = False
'objWord.Quit
MsgBox "letter for " & Me.Fullname & " has been saved in:" & (Chr(13) & Chr(10)) & savePath
objWord.Visible = True
objWord.ActiveDocument.Activate

 

[SmallTime]

I'm knocking off for the day. It'll take me a couple of hours to get home so won’t be able to speak to you until then.

Thanks for you continued help.

 

[THWatson]

SmallTime
Try this...

Change the line
.Item("TxtDate").Range.Text = Nz(Me.TxtDate)

to
.Item("TxtDate").Range.Text = Nz(Format(Me.TxtDate, "dddd d mmm yyyy"))

That worked for me.

Tom

 

[SmallTime]

Many, many thanks for persevering. It worked just fine for me too.

I wish I would get my head around this formatting thing.

A well deserved star.

Thanks