Format change from Access to Word

[RobFJ]

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

 

[Peter Jamieson]

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]

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

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

 

[pjjmsn]

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

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

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