Export truncate double precision number field

[frank]

I'm using Access 2002 on WXP.

A double-precision number, 7 digits before the decimal and 3-8 digits after
the decimal (typical value - 2547855.6511) is truncated to the 7 digits
before the decimal and only two digits after. It is not simply dropping
zeros, it is truncating non-zero values. The problem is the same on two
different machines (on two different networks).

I first noticed the problem when I successfully set up some VBA code to do
it:

varSQL = "SELECT DESIG, NAME, DATE, MAGMOD, MAG, CODES, COMPS, CHART, OBS,
REMARKS FROM tblObservations WHERE Submit = " & "True ORDER BY DATE DESC"

Set db = CurrentDb()
Set rsobs = db.OpenRecordset(varSQL)

rsobs.MoveFirst
rsobs.MoveLast
varnumrecords = rsobs.RecordCount
MsgBox "There are " & varnumrecords & " records to submit"

For Each qdf In db.QueryDefs

If qdf.NAME = "qryObservationsSubmit" Then
db.QueryDefs.Delete qdf.NAME
End If

Next qdf

Set qdf = db.CreateQueryDef("qryObservationsSubmit", varSQL)

vardate = vardate & ".txt"

DoCmd.TransferText acExportFixed, "SubmitObsExport",
"qryObservationsSubmit", "c:\" & vardate

qdf.Close

This exports fine, but the DATE field is truncated to two digits to the
right of the decimal. In the underlying table, tblobservations, DATE is a
number, double, no format, Auto (it isn't a date, as such). The export spec,
SubmitObsExport, has the DATE field also a double-precision number.

I've been fooling with this all day. I abandoned the VBA and am just
exporting in a variety of different formats from the File menu on the
original table, changing the data type, changing from fixed to delimited,
changing the number of digits beyond the decimal in the data type format,
using a number of different targeted file types, and many other permutations
always produces some truncation of the data, except when I change the DATE
field type to text. This is not a good solution however, because it needs to
be a number for other purposes.

I've also created new databases and new tables, always with the same result.

I searched MS Knowledgebase, but there are many many articles on exporting
data from Access and none of them mention my problem as near as I can tell.

I'm missing something obvious, but what is it?

Frank

 

[John Nurick]

Hi Frank,

Search this newsgroup and you'll find the answer. At Google Groups, try
something like this search string:

group:microsoft.public.access.externaldata export more decimal places
text

BTW, I don't think you need to delete and re-create the QueryDef: you
can just assign the new SQL statement to its SQL property.

 

[Frank Reichenbacher]

Okay, well that's what I was afraid of.

I've never had any luck at all correctly formatting a format function
embedded in a SQL statement.

Here's the original SQL:
varSQL = "SELECT DESIG, NAME, DATE, MAGMOD, MAG, CODES, COMPS, CHART, OBS,
REMARKS FROM tblObservations WHERE Submit = " & "True ORDER BY DATE DESC"

Here's my attempt to include a format statement which doesn't work:
varSQL = "SELECT DESIG, NAME, (format([DATE], '0000000.0000')) as Data,
MAGMOD, MAG, CODES, COMPS, CHART, OBS, REMARKS FROM tblObservations WHERE
Submit = " & "True ORDER BY Data DESC"

Thanks for the reminder about searching Google Groups I always forget that a
Google seach doesn't include groups.

Frank

 

[Frank Reichenbacher]

I should clarify that post.

The query works with the format function in the SQL statement shown below.
It's trying to build the query in VBA that's tricky. The comma after
(format([DATE] is the problem. I always have problems with those issues in
VBA.

Okay, well that's what I was afraid of.

I've never had any luck at all correctly formatting a format function
embedded in a SQL statement.

Here's the original SQL:
varSQL = "SELECT DESIG, NAME, DATE, MAGMOD, MAG, CODES, COMPS, CHART, OBS,
REMARKS FROM tblObservations WHERE Submit = " & "True ORDER BY DATE DESC"

Here's my attempt to include a format statement which doesn't work:
varSQL = "SELECT DESIG, NAME, (format([DATE], '0000000.0000')) as Data,
MAGMOD, MAG, CODES, COMPS, CHART, OBS, REMARKS FROM tblObservations WHERE
Submit = " & "True ORDER BY Data DESC"

Thanks for the reminder about searching Google Groups I always forget that
a Google seach doesn't include groups.

Frank

 

[Frank Reichenbacher]

Thanks!

Ignore my two follow-ups.