Currency Formatting - Word 2000 Chris Stammers 5/10/2007 4:49:00 PM I need to format a currency field to be £0.00 if greater than £1 and 00p if less than £1 in the data source. The data source holds the data as 1.00 or ..50. I have created this query: {IF { MERGEFIELD } > 1 "£{MERGEFIELD\#,0.00 } "{ MERGEFIELD\#.00 }p" } The query does work but where there is just .50, it shows in the merge doc with a leading decimal point. My question is this - is there a formatting switch to get rid of the decimal point in the case where it is just pence? I have tried taking the decimal point away and the query stops working. I tried susbtituting the hash sign for a '[ at ]' instead and that didn't work either. Any suggestions? Thanks, Chris

Re: Currency Formatting - Word 2000 "Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> 5/10/2007 6:19:53 PM Multiply it by 100 {IF { MERGEFIELD } > 1 "£{MERGEFIELD\#,0.00 } "{ = { MERGEFIELD\#.00 }* 100 }p" } -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in message news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... [Quoted Text] >I need to format a currency field to be £0.00 if greater than £1 and 00p if > less than £1 in the data source. The data source holds the data as 1.00 or > .50. I have created this query: {IF { MERGEFIELD } > 1 > "£{MERGEFIELD\#,0.00 } > "{ MERGEFIELD\#.00 }p" } > > The query does work but where there is just .50, it shows in the merge doc > with a leading decimal point. My question is this - is there a formatting > switch to get rid of the decimal point in the case where it is just pence? > I > have tried taking the decimal point away and the query stops working. I > tried > susbtituting the hash sign for a '[ at ]' instead and that didn't work either. > Any > suggestions? > > Thanks, > Chris

Re: Currency Formatting - Word 2000 "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 5/10/2007 6:27:30 PM Try { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#£,0.00 }" "{ ={ MERGEFIELD myamount } * 100 \#00p } Peter Jamieson "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in message news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... [Quoted Text] >I need to format a currency field to be £0.00 if greater than £1 and 00p if > less than £1 in the data source. The data source holds the data as 1.00 or > .50. I have created this query: {IF { MERGEFIELD } > 1 > "£{MERGEFIELD\#,0.00 } > "{ MERGEFIELD\#.00 }p" } > > The query does work but where there is just .50, it shows in the merge doc > with a leading decimal point. My question is this - is there a formatting > switch to get rid of the decimal point in the case where it is just pence? > I > have tried taking the decimal point away and the query stops working. I > tried > susbtituting the hash sign for a '[ at ]' instead and that didn't work either. > Any > suggestions? > > Thanks, > Chris

RE: Currency Formatting - Word 2000 Chris Stammers 5/11/2007 8:01:03 AM Peter and Doug - Thank you. Regards, Chris "Chris Stammers" wrote: [Quoted Text] > I need to format a currency field to be £0.00 if greater than £1 and 00p if > less than £1 in the data source. The data source holds the data as 1.00 or > .50. I have created this query: {IF { MERGEFIELD } > 1 "£{MERGEFIELD\#,0.00 } > "{ MERGEFIELD\#.00 }p" } > > The query does work but where there is just .50, it shows in the merge doc > with a leading decimal point. My question is this - is there a formatting > switch to get rid of the decimal point in the case where it is just pence? I > have tried taking the decimal point away and the query stops working. I tried > susbtituting the hash sign for a '[ at ]' instead and that didn't work either. Any > suggestions? > > Thanks, > Chris

Re: Currency Formatting - Word 2000 Chris Stammers 5/12/2007 2:42:00 PM Hello again, Thanks for this query. I have placed it into 2 documents. It works in one place in one document. However it seems that when the query is converting to pence, only part of the paragraph it is part of will generate. The pound formatting works fine. However, the other document I have placed it in doesn't work at all if that query is placed in it; all I get is 'Field Calculation Error in Record 1' etc. Any ideas? Thanks, Chris "Peter Jamieson" wrote: [Quoted Text] > Try > > { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#£,0.00 }" > "{ ={ MERGEFIELD myamount } * 100 \#00p } > > Peter Jamieson > "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in message > news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... > >I need to format a currency field to be £0.00 if greater than £1 and 00p if > > less than £1 in the data source. The data source holds the data as 1.00 or > > .50. I have created this query: {IF { MERGEFIELD } > 1 > > "£{MERGEFIELD\#,0.00 } > > "{ MERGEFIELD\#.00 }p" } > > > > The query does work but where there is just .50, it shows in the merge doc > > with a leading decimal point. My question is this - is there a formatting > > switch to get rid of the decimal point in the case where it is just pence? > > I > > have tried taking the decimal point away and the query stops working. I > > tried > > susbtituting the hash sign for a '[ at ]' instead and that didn't work either. > > Any > > suggestions? > > > > Thanks, > > Chris > >

Re: Currency Formatting - Word 2000 "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 5/12/2007 4:29:06 PM a. I've missed the closing quotes and field brace: b. It may need quotes around the format, i.e. { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00p" }" } If you still have trouble I suppose I would try the following next { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00" }p" } Peter Jamieson "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in message news:5018B2B2-7B6C-48B0-AE28-5C2CCD3DC091[ at ]microsoft.com... [Quoted Text] > Hello again, > > Thanks for this query. I have placed it into 2 documents. It works in one > place in one document. However it seems that when the query is converting > to > pence, only part of the paragraph it is part of will generate. The pound > formatting works fine. > > However, the other document I have placed it in doesn't work at all if > that > query is placed in it; all I get is 'Field Calculation Error in Record 1' > etc. > > Any ideas? > > Thanks, > Chris > > > "Peter Jamieson" wrote: > >> Try >> >> { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#£,0.00 }" >> "{ ={ MERGEFIELD myamount } * 100 \#00p } >> >> Peter Jamieson >> "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in >> message >> news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... >> >I need to format a currency field to be £0.00 if greater than £1 and 00p >> >if >> > less than £1 in the data source. The data source holds the data as 1.00 >> > or >> > .50. I have created this query: {IF { MERGEFIELD } > 1 >> > "£{MERGEFIELD\#,0.00 } >> > "{ MERGEFIELD\#.00 }p" } >> > >> > The query does work but where there is just .50, it shows in the merge >> > doc >> > with a leading decimal point. My question is this - is there a >> > formatting >> > switch to get rid of the decimal point in the case where it is just >> > pence? >> > I >> > have tried taking the decimal point away and the query stops working. I >> > tried >> > susbtituting the hash sign for a '[ at ]' instead and that didn't work >> > either. >> > Any >> > suggestions? >> > >> > Thanks, >> > Chris >> >>

Re: Currency Formatting - Word 2000 Chris Stammers 5/12/2007 9:12:00 PM Peter, Many thanks. I have tried both of those options with a basic merge using a dummy file and both seemed to work fine. I will try with the offending documents on Monday and see what happens. Regards, Chris "Chris Stammers" wrote: [Quoted Text] > Hello again, > > Thanks for this query. I have placed it into 2 documents. It works in one > place in one document. However it seems that when the query is converting to > pence, only part of the paragraph it is part of will generate. The pound > formatting works fine. > > However, the other document I have placed it in doesn't work at all if that > query is placed in it; all I get is 'Field Calculation Error in Record 1' etc. > > Any ideas? > > Thanks, > Chris > > > "Peter Jamieson" wrote: > > > Try > > > > { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount \#£,0.00 }" > > "{ ={ MERGEFIELD myamount } * 100 \#00p } > > > > Peter Jamieson > > "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in message > > news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... > > >I need to format a currency field to be £0.00 if greater than £1 and 00p if > > > less than £1 in the data source. The data source holds the data as 1.00 or > > > .50. I have created this query: {IF { MERGEFIELD } > 1 > > > "£{MERGEFIELD\#,0.00 } > > > "{ MERGEFIELD\#.00 }p" } > > > > > > The query does work but where there is just .50, it shows in the merge doc > > > with a leading decimal point. My question is this - is there a formatting > > > switch to get rid of the decimal point in the case where it is just pence? > > > I > > > have tried taking the decimal point away and the query stops working. I > > > tried > > > susbtituting the hash sign for a '[ at ]' instead and that didn't work either. > > > Any > > > suggestions? > > > > > > Thanks, > > > Chris > > > >

Re: Currency Formatting - Word 2000 "Graham Mayor" <gmayor[ at ]REMOVETHISmvps.org> 5/13/2007 5:41:57 AM Peter, You may find the macro at http://www.gmayor.com/export_field.htm useful as it exports the fields intact to a text string which can be pasted into a NG message, thus avoiding such typos. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Peter Jamieson wrote: [Quoted Text] > a. I've missed the closing quotes and field brace: > > b. It may need quotes around the format, i.e. > > { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount > \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00p" }" } > > If you still have trouble I suppose I would try the following next > > { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount > \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00" }p" } > > Peter Jamieson > > > "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in > message news:5018B2B2-7B6C-48B0-AE28-5C2CCD3DC091[ at ]microsoft.com... >> Hello again, >> >> Thanks for this query. I have placed it into 2 documents. It works >> in one place in one document. However it seems that when the query >> is converting to >> pence, only part of the paragraph it is part of will generate. The >> pound formatting works fine. >> >> However, the other document I have placed it in doesn't work at all >> if that >> query is placed in it; all I get is 'Field Calculation Error in >> Record 1' etc. >> >> Any ideas? >> >> Thanks, >> Chris >> >> >> "Peter Jamieson" wrote: >> >>> Try >>> >>> { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount >>> \#£,0.00 }" "{ ={ MERGEFIELD myamount } * 100 \#00p } >>> >>> Peter Jamieson >>> "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in >>> message >>> news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... >>>> I need to format a currency field to be £0.00 if greater than £1 >>>> and 00p if >>>> less than £1 in the data source. The data source holds the data as >>>> 1.00 or >>>> .50. I have created this query: {IF { MERGEFIELD } > 1 >>>> "£{MERGEFIELD\#,0.00 } >>>> "{ MERGEFIELD\#.00 }p" } >>>> >>>> The query does work but where there is just .50, it shows in the >>>> merge doc >>>> with a leading decimal point. My question is this - is there a >>>> formatting >>>> switch to get rid of the decimal point in the case where it is just >>>> pence? >>>> I >>>> have tried taking the decimal point away and the query stops >>>> working. I tried >>>> susbtituting the hash sign for a '[ at ]' instead and that didn't work >>>> either. >>>> Any >>>> suggestions? >>>> >>>> Thanks, >>>> Chris

Re: Currency Formatting - Word 2000 "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 5/13/2007 10:02:48 AM Thanks for the reminder Graham. Peter Jamieson "Graham Mayor" <gmayor[ at ]REMOVETHISmvps.org> wrote in message news:erBMLTSlHHA.492[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > Peter, > > You may find the macro at http://www.gmayor.com/export_field.htm useful as > it exports the fields intact to a text string which can be pasted into a > NG message, thus avoiding such typos. > > -- > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Graham Mayor - Word MVP > > My web site www.gmayor.com > Word MVP web site http://word.mvps.org > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > > Peter Jamieson wrote: >> a. I've missed the closing quotes and field brace: >> >> b. It may need quotes around the format, i.e. >> >> { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount >> \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00p" }" } >> >> If you still have trouble I suppose I would try the following next >> >> { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount >> \#"£,0.00" }" "{ ={ MERGEFIELD myamount } * 100 \#"00" }p" } >> >> Peter Jamieson >> >> >> "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in >> message news:5018B2B2-7B6C-48B0-AE28-5C2CCD3DC091[ at ]microsoft.com... >>> Hello again, >>> >>> Thanks for this query. I have placed it into 2 documents. It works >>> in one place in one document. However it seems that when the query >>> is converting to >>> pence, only part of the paragraph it is part of will generate. The >>> pound formatting works fine. >>> >>> However, the other document I have placed it in doesn't work at all >>> if that >>> query is placed in it; all I get is 'Field Calculation Error in >>> Record 1' etc. >>> >>> Any ideas? >>> >>> Thanks, >>> Chris >>> >>> >>> "Peter Jamieson" wrote: >>> >>>> Try >>>> >>>> { IF { MERGEFIELD myamount } > 0.995 "{ MERGEFIELD myamount >>>> \#£,0.00 }" "{ ={ MERGEFIELD myamount } * 100 \#00p } >>>> >>>> Peter Jamieson >>>> "Chris Stammers" <ChrisStammers[ at ]discussions.microsoft.com> wrote in >>>> message >>>> news:1F3C094C-0281-4AEE-82D5-FC3E5351E807[ at ]microsoft.com... >>>>> I need to format a currency field to be £0.00 if greater than £1 >>>>> and 00p if >>>>> less than £1 in the data source. The data source holds the data as >>>>> 1.00 or >>>>> .50. I have created this query: {IF { MERGEFIELD } > 1 >>>>> "£{MERGEFIELD\#,0.00 } >>>>> "{ MERGEFIELD\#.00 }p" } >>>>> >>>>> The query does work but where there is just .50, it shows in the >>>>> merge doc >>>>> with a leading decimal point. My question is this - is there a >>>>> formatting >>>>> switch to get rid of the decimal point in the case where it is just >>>>> pence? >>>>> I >>>>> have tried taking the decimal point away and the query stops >>>>> working. I tried >>>>> susbtituting the hash sign for a '[ at ]' instead and that didn't work >>>>> either. >>>>> Any >>>>> suggestions? >>>>> >>>>> Thanks, >>>>> Chris > >