Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Format change from Access to Word
I'm using Word and Access 2007.
The mailmerge fields come from an Access query and one of the fields in the query is formatted as currency with zero decimal places. That particular field has one of ten values in it. If I run the query in Access, all ten show the correct format. However, when I run the mailmerge in word, nine of those values are as I'd expect (eg £12345); the tenth has three decimal places (eg £12345,678). I'm lost as to what to do. Can SKS help TIA Rob |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Format change from Access to Word
1. Can you post the SQL query code being used here?
2. Are the underlying fields currency types, or more general floating point types, e.g. double? (Currency types are known to cause some problems , though I do not know exactly why)) Or... That particular field has one of ten values in it. are these associated with a lookup field? 3. If the query code is using the format function to format the output, does it make any difference if you covert the original amount to a double in the query using cdbl(myamount) before applying formatting? 4. If you know that the values never have any decimal places, are you able to format the data further in Word using e.g. { =int({ MERGEFIELD myfield }) \#£0 } (where all the {} are the special field code brace pairs you can insert using ctrl-F9) ? This assumes that you need truncation rather than rounding for these values. Peter Jamieson http://tips.pjmsn.me.uk RobFJ wrote: I'm using Word and Access 2007. The mailmerge fields come from an Access query and one of the fields in the query is formatted as currency with zero decimal places. That particular field has one of ten values in it. If I run the query in Access, all ten show the correct format. However, when I run the mailmerge in word, nine of those values are as I'd expect (eg £12345); the tenth has three decimal places (eg £12345,678). I'm lost as to what to do. Can SKS help TIA Rob |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Format change from Access to Word
Hi Peter,
Thanks for responding. Responses are as follows 1. Can you post the SQL query code being used here? It is SELECT [Raw Employee Data from Excel Query].FirstName, [Raw Employee Data from Excel Query].[Last Name], [Raw Employee Data from Excel Query].[Dept Name], [Raw Employee Data from Excel Query].[New Grade], [Raw Employee Data from Excel Query].[New Job Title], [Raw Employee Data from Excel Query].[New Scale Min], [Raw Employee Data from Excel Query].[New Scale Max], [Raw Employee Data from Excel Query].[Old FTE Salary], [Raw Employee Data from Excel Query].[New FTE Salary], [Raw Employee Data from Excel Query].[New Act Salary], [Raw Employee Data from Excel Query].[Red Circle Protection Required], [Raw Employee Data from Excel Query].[Salary Progression Protection Req'd], [Raw Employee Data from Excel Query].[Old Scale Max] FROM [Raw Employee Data from Excel Query] WHERE ((([Raw Employee Data from Excel Query].[Last Name])"Vacant") AND (([Raw Employee Data from Excel Query].[Red Circle Protection Required])="No") AND (([Raw Employee Data from Excel Query].[Salary Progression Protection Req'd])="No")); 2. Are the underlying fields currency types, or more general floating point types, e.g. double? The problem field above is [New Scale Max] The data comes from an excel spreadsheet where it is formatted currency with zero decimal places. The field properties on the Access query was originally empty. I then set it the format to currency with 0 decimal places. Either way, it comes into word as 3 decimal places (non currency). That particular field has one of ten values in it. Are these associated with a lookup field? There is a look-up table in the original excel worksheet that fully populates another worksheet. It's the latter that is used as the data-source for the Access query and then the word doc All the values in the access query shows the correct format (ie £0) 3. If the query code is using the format function to format the output, does it make any difference if you covert the original amount to a double in the query using cdbl(myamount) before applying formatting? No format function is used in the initial query code AFAIK 4. If you know that the values never have any decimal places, are you able to format the data further in Word using e.g. { =int({ MERGEFIELD myfield }) \#£0 } (where all the {} are the special field code brace pairs you can insert using ctrl-F9) ? This assumes that you need truncation rather than rounding for these values. Yes G. But if I switch toggle 'Preview Results' on and off in word, only the field value is shown whereas others switch between field name and field value. Hope this helps Rob Peter Jamieson http://tips.pjmsn.me.uk RobFJ wrote: I'm using Word and Access 2007. The mailmerge fields come from an Access query and one of the fields in the query is formatted as currency with zero decimal places. That particular field has one of ten values in it. If I run the query in Access, all ten show the correct format. However, when I run the mailmerge in word, nine of those values are as I'd expect (eg £12345); the tenth has three decimal places (eg £12345,678). I'm lost as to what to do. Can SKS help TIA Rob |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Format change from Access to Word
OK, I cannot test any of this right now, but if the data ii being
imported from Excel then however you are doing it (importing or linking) the chances are that you are running into a problem with the Jet IISAM used to do the import - there is further info. at http://tips.pjmsn.me.uk/t0003.htm that describes some of the problems, but that probably won't lead to a solution in this case. I'd try wrapping [New Scale Max] inside a cdbl anyway, and if necessary wrap that inside a format statement. The "lookup" thing is probably a red herring. Yes G. But if I switch toggle 'Preview Results' on and off in word, only the field value is shown whereas others switch between field name and field value. If it's the only option I guess that will have to be lived with :-) Peter Jamieson On Apr 9, 4:28 pm, "RobFJ" wrote: Hi Peter, Thanks for responding. Responses are as follows 1. Can you post the SQL query code being used here? It is SELECT [Raw Employee Data from Excel Query].FirstName, [Raw Employee Data from Excel Query].[Last Name], [Raw Employee Data from Excel Query].[Dept Name], [Raw Employee Data from Excel Query].[New Grade], [Raw Employee Data from Excel Query].[New Job Title], [Raw Employee Data from Excel Query].[New Scale Min], [Raw Employee Data from Excel Query].[New Scale Max], [Raw Employee Data from Excel Query].[Old FTE Salary], [Raw Employee Data from Excel Query].[New FTE Salary], [Raw Employee Data from Excel Query].[New Act Salary], [Raw Employee Data from Excel Query].[Red Circle Protection Required], [Raw Employee Data from Excel Query].[Salary Progression Protection Req'd], [Raw Employee Data from Excel Query].[Old Scale Max] FROM [Raw Employee Data from Excel Query] WHERE ((([Raw Employee Data from Excel Query].[Last Name])"Vacant") AND (([Raw Employee Data from Excel Query].[Red Circle Protection Required])="No") AND (([Raw Employee Data from Excel Query].[Salary Progression Protection Req'd])="No")); 2. Are the underlying fields currency types, or more general floating point types, e.g. double? The problem field above is [New Scale Max] The data comes from an excel spreadsheet where it is formatted currency with zero decimal places. The field properties on the Access query was originally empty. I then set it the format to currency with 0 decimal places. Either way, it comes into word as 3 decimal places (non currency). That particular field has one of ten values in it. Are these associated with a lookup field? There is a look-up table in the original excel worksheet that fully populates another worksheet. It's the latter that is used as the data-source for the Access query and then the word doc All the values in the access query shows the correct format (ie 0) 3. If the query code is using the format function to format the output, does it make any difference if you covert the original amount to a double in the query using cdbl(myamount) before applying formatting? No format function is used in the initial query code AFAIK 4. If you know that the values never have any decimal places, are you able to format the data further in Word using e.g. { =int({ MERGEFIELD myfield }) \# 0 } (where all the {} are the special field code brace pairs you can insert using ctrl-F9) ? This assumes that you need truncation rather than rounding for these values. Yes G. But if I switch toggle 'Preview Results' on and off in word, only the field value is shown whereas others switch between field name and field value. Hope this helps Rob Peter Jamieson http://tips.pjmsn.me.uk RobFJ wrote: I'm using Word and Access 2007. The mailmerge fields come from an Access query and one of the fields in the query is formatted as currency with zero decimal places. That particular field has one of ten values in it. If I run the query in Access, all ten show the correct format. However, when I run the mailmerge in word, nine of those values are as I'd expect (eg 12345); the tenth has three decimal places (eg 12345,678). I'm lost as to what to do. Can SKS help TIA Rob |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format of Yes/No mergefields from Access merged to Word | Mailmerge | |||
date format access to word | Mailmerge | |||
WORD 97, how to change 3 col'm return label format to list format | Microsoft Word Help | |||
How do I get UK date format merging into word from Access? | Mailmerge | |||
Why does the date format change when I mail merge from Access? | Mailmerge |