MS Word XP Mail Merge with MS Access 2002

[Alfy101]

Hi guys can anyone help,

I'm trying to use a mail merge in MS Word XP linking to a table in an MS Access 2002 database.

All is fine except when merging a date field. If the field has a value it comes across OK but if it's blank it displays 12:00.

I've tried to change the format of the date field in Access but this doesn't help.

Thanks for any help you can give me!

 

[aaahhhrrggghhhh]

Hi.

The reason for this is that a datetime datatype (the one you are using) cannot contain a null value. Its equivalent is "12:00" (well, 1 Jan 1899).

When Word picks up the field, it sees a "12:00" time in it and displays that.

I tried this example and, instead, exported the table to Excel, then I reformatted the column (in excel) to a text data type, and used this as the datasource. That worked, however, when i introduced non-null values, it converted it back to its decimal value!

So, Unfortunately (Unless anyone has any other ideas), you are going to have to write a query to list the information for the report, and when you get to the field use the following construct :

CStr(Format(DailyHoursTable.TodayDate, "dd/mm/yyyy&quot) AS TodayDate

So, for example:

SELECT DailyHoursTable.[DHT Id], DailyHoursTable.EmplProjLinkID, DailyHoursTable.NumberOfHours, CStr(Format(DailyHoursTable.TodayDate, "dd/mm/yyyy&quot) AS TodayDate
FROM DailyHoursTable;

Because Cstr converts to a string, and format allows you to specify the layout of the date.

Have tried this and it works.

When setting-up the mailmerge, use the query that you wrote as the datasource.

If you have any further problems with this, please let me know, I have tried to explain as well as I can.

Regards,
Mr Big Dont be small. be BIG

 

[Alfy101]

Thanks for that It's great as a work around!

I hadn't seen this problem before with 2000 versions, so it bugs when you know (think) it should be fine!