Scientific format

[GCNY]

I have an Access 2003 database where some of the number fields are in
scientific format.

In Word 2003 when I insert the scientific formatted fields, they appear in
decimal format.

How can I handle this formatting problem?

 

[Doug Robbins - Word MVP]

The following was posted by fellow MVP Macropod:

Quote

The following compound field will return the scientific notation of any +ve
value between 10^-9 and 10^9:
{QUOTE
{SET a {SourceVal}}
{SET
b{=9-(a<10^9)-(a<10^8)-(a<10^7)-(a<10^6)-(a<10^5)-(a<10^4)-(a<10^3)-(a<10^2)
-(a<10^1)-(a<10^0)-(a<10^-1)-(a<10^-2)-(a<10^-3)-(a<10^-4)-(a<10^-5)-(a<10^-
6)-(a<10^-7)-(a<10^-8)}}
{SET c{=int(a/10^b)+mod(a,10^b)/10^b}}
{c \# 0.00}E{b \# +00;-00}}
where 'SourceVal' is the input, which could be a formula taking its input
from two formfields. You can extend the range easily enough by adding more
terms.

All the field braces (i.e. { }) are created in pairs via Ctrl-F9. I've laid
the coding out with line breaks to separate the key portions of the coding,
but you don't really need them.

Unquote

I am not sure if this will work with mailmerge, but I think that the thing
to do would be to replace

{SourceVal}

with your Merge Field that contains the value.
--
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

 

[GCNY]

Thank you for the response. I will try it.

In a QBE form do I set up an expression and enter this code after the colon?

 

[GCNY]

Dear Doug:

In an Access QBE form I set up an Expression: followed by the {QUOTE......}}
below.

I got an error message saying: Replication ID is invalid. Replication ID
must contain digits, letters, or hexadecimal numbers and must of the right
length and be in canonicalSQL form.

I have no idea what any ot that means, except perhaps using {, (, #, etc.
characters might be a problem.

Do you have any further suggestions?

Thank you.

Steve Barker
------------------------

 

[Graham Mayor]

In your duplicate thread in another forum (please don't multipost!) I
replied - From the Tools menu in Word, select Options and then go to the
General tab and check the box against the "Confirm conversions at open"
item. Then when you attach the data source to the mail merge main document,
you will be given the option of using the DDE method of connection which
should read the data as you have it formatted in the table.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins - Word MVP]

No, you would put the expression in the mail merge main document.

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

 

[GCNY]

Excuse me for multiposting; but this problem involves both Word & Access. I
don't know where the answer will come from.

The Tools/Options/General/Confirm conversions at open was checked. So that
is not the solution.

 

[GCNY]

Where? As a macro for each of the 8 Scientific format merge fileds or just
once?

 

[Peter Jamieson]

You would have to repeat the fields for each number you want to present.
i.e., the only mechanism in the field language for code re-use is copying
the code.

Another approach would be to create a query in Access that returns
format(myfield,'Scientific') for each number in scientific format, then use
that as the data source for your merge - e.g.

SELECT *, format(myfield1,'Scientific') as [myfield1s],
format(myfield2,'Scientific') as [myfield2s] FROM mytable

You might need to provide specific formats rather than 'Scientific' to alter
the precision and so on.

Peter Jamieson

 

[macropod]

Hi Doug & GCNY,

A similar query about mailmerge with scientific notations appeared today in the docmanagement NG also. My suggested coding is:

{QUOTE
{SET z{MERGEFIELD dbValue}}
{SET a{=ABS(z)}}
{SET
b{=9-(a<10^9)-(a<10^8)-(a<10^7)-(a<10^6)-(a<10^5)-(a<10^4)-(a<10^3)-(a<10^2)-(a<10^1)-(a<10^0)-(a<10^-1)-(a<10^-2)-(a<10^-3)-(a<10^-4)-(a<10^-5)-(a<10^-6)-(a<10^-7)-(a<10^-8)}}
{SET c{=int(a/10^b)+mod(a,10^b)/10^b}}
{c \# 0.00}E{b \# +00;-00}}

where 'dbValue' is the mergefield's name.

If the same value is to appear in multiple places, then bookmarking the above field and using cross-references to it would do the
job. Otherwise, as Doug says, you'll need one copy of the Word field for each db field - a simple copy, paste & edit process once
you've got the first field coded.

The core of the above field is included in my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

Cheers