mail merge with database field, data formatting (O

Topic

New Reply

Hello loungers
I am mail merging with a database field containing a SQL statement enabling a one to many result.

The problem occurs with the financial data …
this is displayed to a gazillion decimal places, 2 would suffice.

If I was merging normally I would format the merge field with a picture switch.

Has anyone got a solution to formatting the results within a database field.

Thanks in advance.

Geof

Reply | Quote

Replies

In the SQL query are you able to modify the field to round the output eg Result_: Round([Result],2)

Reply | Quote

Thanks for the reply Andrew,
Alas no.
Even rounding the fractional component doesnt help.

Truncating the remainder with left() doesnt help.
Word pads the result with trailing zeroes in the merge result
Cheers
Geof

Reply | Quote

Can you provide some information about the SQL statement, in particular about the field you’re having problems with?

Reply | Quote

Hello Hans
The contents of the field follow

Database d “F:DataCustomersTDCrates_remits.mdb” c “Provider=Microsoft.Jet.OLEDB.4.0;Password =””,s “SELECT remits.TRANS_DATE, remits.PAYMENT, remits.GST_CONTENT
FROM remits INNER JOIN residents ON remits.RID_ID = residents.RID_ID WHERE <>” h

The problem is similar to that experienced with mail merging from excel data sources that surfaces with office 02 I think. These I resolve easily enough.

I guess I am going to perform the merge differently.

Regards and thanks for your interest.

Geof

Reply | Quote

Have you tried adding a number format switch to the merge field in Word?
– Right-click the relevant merge field.
– Select Toggle Field Codes from the popup menu.
– Make it look like this (where PAYMENT is the name of the merge field):

{ MERGEFIELD PAYMENT # "0.00" }

– Press F9 to toggle field codes off and to update the result.

Note: The brackets { } have been created by Word, don’t type them yourself or try to overtype them.

Reply | Quote

Hello Hans

The problem is that these columns are returned as part of a table as a result of the database field.

They are not discrete mergefields, I dont think you can add a number format switch to the database (table) field.
Cheers

Geof

Reply | Quote

Sorry, should have seen that. Not enough coffee yet this morning.

You wrote that using Round didn’t work; have you tried using Format? For example

SELECT remits.TRANS_DATE, Format(remits.PAYMENT, “0.00”) , …

Reply | Quote

Hello Hans

I cant make format command work in this context. Error reports ‘unable to open database …’
Interesting to note though that use of

format(fieldname, “0.00”)

within an MS Access query designer is translated to use FIXED in the sql statement.

I guess I will do the job from Access as a report.

Cheers and thanks again for the efforts.

Geof

Reply | Quote