Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    mail merge with database field, data formatting (O

    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

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: mail merge with database field, data formatting (O

    In the SQL query are you able to modify the field to round the output eg Result_: Round([Result],2)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: mail merge with database field, data formattin

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: mail merge with database field, data formattin

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

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: mail merge with database field, data formattin

    Hello Hans
    The contents of the field follow

    Database d "FataCustomersTDCrates_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 <<correctly specified match on mergefield here>>" 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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: mail merge with database field, data formattin

    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):
    <pre>{ MERGEFIELD PAYMENT # "0.00" }
    </pre>

    - 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.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: mail merge with database field, data formattin

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: mail merge with database field, data formattin

    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") , ...

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: mail merge with database field, data formattin

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •