Date format in view used for mailmerge

Question

Date format in view used for mailmerge

a_ud

SSCrazy

Points: 2457
More actions
February 10, 2011 at 6:15 am

#231274

Hi all,
I need to generate a mail merge from a database made out of: [1 Access front-end] + [1 SQL Server back-end]
The control of the process is managed by VBA code in Access, that requests the data from a SQL Server view and sends that to a Word doc. This cannot be changed.
The issue is that the date in the merged letter appears as: 2011/03/10 00:00:00, that is, in reverse order and with the time added.
Question is simple: how do you control the date format so that this is not exported when merging? (BTW, the view looks normal European format: 10/03/2011 + 00.00.00... so don't answer change the view)
I'm interested in a solution for this at table level / view level /any other level you can think of that really fixes the problem.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

a_ud (2/10/2011)
...so don't answer change the view)
I'm interested in a solution for this at table level / view level /any other level

you said don't change the view, but then asked for a solution at the view level....that's kind of contradictory.

anyway, the right thing to do is to fix this in your mailmerge document itself. you can decide on the formatting of the value that the field receives:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a_ud SSCrazy Points: 2457 More actions · Answer 2

Thanks for the answer and the screenshot, SSCcertifiable. On the contradiction, basically what I meant is that the merge field has the wrong format, but the view shows the right one (except for the 00.00.00).

On your reply, that would be a solution if this were a DATE field (in Word, Insert>Category "Date & Time", then format), but this is a "Merge" field (Insert>Categories "Date & Time") that only offers basic formats (Uppercase-Lowercase... title Case).

Lowell SSC Guru Points: 323482 More actions · Answer 3

no...i think you want to insert a FIELD instead; you have much more control. also, you can manually change the format to the desired format, with string constants in it for custom formats. this screenshot is from Word 2007, but it's the same for 97-2003, or anything newer...the paths to the functions are simply different

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a_ud SSCrazy Points: 2457 More actions · Answer 4

I might not have expressed it correctly.... The problem is to merge the information existing on the DB (where one of the fields is an [Appointment date], maybe in 3 months time) with a Word doc.

You're suggesting to insert a Word Date field, but that would print the current date, wouldn't it? So this wouldn't be a mail merge, but something different.

Or there may be a way to include a date field, format it and pass the value through code, by referring to the name of the field exactly as in MergeFields ?

Lowell SSC Guru Points: 323482 More actions · Answer 5

No we are on the same page.

a mailmerge substitutes [Fieldname] in the word document with whatever value is coming from the database. but in many cases, you wan thte value from the database to be formatted...MONEY datatype formatted as currency like $1,234.56 when it is stored as 1234.5600, and dates to have a specific format.

This is very common.

so if you change your mailmerge document in Word to use fields with formats, the issue you are describing is resolved. this is a classic issue where you make the presentation layer(mailmerge in this case) prettify the raw data.

when you are designing the Word doc, it might insert the current date as a placeholder value for the field's date, but it will not put todays date (unless you do not map that field to a a value from the data)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a_ud SSCrazy Points: 2457 More actions · Answer 6

Yeah, I think we're on the same page now. I've attached a JPG for those who are not that familiar with layer/format issues, showing the code for the fields.

The image shows:

• In 1) the usual Merge field (Insert Field > Categories-Mailmerge > Mergefield). Will show any data sent from the DB.

• 2) shows the Date field I was referring to (Insert field > Categories-Date and time > Date). This will show the current date, in the format chosen.

• 3) shows a Merge field formatted as a Date field (Insert Field > Categories-Mailmerge > Mergefield). Will show what you send from the DB, but the format has been changed manually toggling the field code (the format is the bit: \@ "dd MMMM yyyy")

This seems to work perfectly, thanks!