Exported table losing leading zero's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've a table with text & number fields. The fields are appended to the table
from the query below (where the Sum fields have been formatted with leading
zeros using the format function).
Query looks good, table looks good, but export to Excel or txt...no good
because leading zeros are missing in the Totaled fieds.

I tried to export from the query but have only 4 options to 'encode' data:
MSDOS, Windows Default, Unicode and Unicode (UTF-8); Changed from append to
select query & have normal export functions..but leading zero's aren't saved.
Please help...I need to keep the leading zeros in my text file. Is there
anyway to create another column in my query to store the sum(data) as a text
field?





INSERT INTO RUNDOWN_txt ( LinePath, OrderDate, VanningDate,
SumOfVanningVolume, ProductionDate, ProductionVolume, CalcWk )
SELECT TEST_LinePath.LinePath AS LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd") AS OrderDate,
Format(DiagramSimMonthly.VanningDate,"yymmdd") AS VanningDate,
Sum(Format(DiagramSimMonthly.VanningVolume,"00000")) AS SumOfVanningVolume,
Format(DiagramSimMonthly.ProductionDate,"yymmdd") AS ProductionDate,
Sum(Format(DiagramSimMonthly.UsedVanningVolume,"00000")) AS ProductionVolume,
Format(TEST_CalcWkConv.CalcWk,"0000") AS CalcWk
FROM TEST_CalcWkConv, (DiagramSimMonthly INNER JOIN SeriesGrouping ON
(DiagramSimMonthly.Series = SeriesGrouping.Series) AND (DiagramSimMonthly.DPV
= SeriesGrouping.DPV) AND (DiagramSimMonthly.DPP = SeriesGrouping.DPP) AND
(DiagramSimMonthly.Variation = SeriesGrouping.Variation)) INNER JOIN
TEST_LinePath ON SeriesGrouping.Series = TEST_LinePath.SeriesName
GROUP BY TEST_LinePath.LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd"),
Format(DiagramSimMonthly.VanningDate,"yymmdd"),
Format(DiagramSimMonthly.ProductionDate,"yymmdd"),
Format(TEST_CalcWkConv.CalcWk,"0000"), IIf((DiagramSimMonthly!Orderdate
Between TEST_CalcWkConv!BegOrderdate And
TEST_CalcWkConv!EndOrderdate),Test_CalcWkConv!CalcWk,"NA"),
DiagramSimMonthly.PorA, SeriesGrouping.PatternName
HAVING (((DiagramSimMonthly.PorA)="a") AND ((SeriesGrouping.PatternName)
Like "Group 5") AND ((IIf(([DiagramSimMonthly]![Orderdate] Between
[TEST_CalcWkConv]![BegOrderdate] And
[TEST_CalcWkConv]![EndOrderdate]),[Test_CalcWkConv]![CalcWk],"NA")) Not Like
"NA"))
ORDER BY TEST_LinePath.LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd"),
Format(DiagramSimMonthly.VanningDate,"yymmdd"),
Format(DiagramSimMonthly.ProductionDate,"yymmdd");
 
With ACCESS, you use a calculated field to "represent" the data with the
leading zeroes. For example, if you wanted to output a field that would have
a total of 12 characters (including leading zeroes), your calculated field
would look like this:

MyOutputField: Format([RealFieldName[, "000000000000")

PKT said:
I've a table with text & number fields. The fields are appended to the
table
from the query below (where the Sum fields have been formatted with
leading
zeros using the format function).
Query looks good, table looks good, but export to Excel or txt...no good
because leading zeros are missing in the Totaled fieds.

I tried to export from the query but have only 4 options to 'encode' data:
MSDOS, Windows Default, Unicode and Unicode (UTF-8); Changed from append
to
select query & have normal export functions..but leading zero's aren't
saved.
Please help...I need to keep the leading zeros in my text file. Is there
anyway to create another column in my query to store the sum(data) as a
text
field?





INSERT INTO RUNDOWN_txt ( LinePath, OrderDate, VanningDate,
SumOfVanningVolume, ProductionDate, ProductionVolume, CalcWk )
SELECT TEST_LinePath.LinePath AS LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd") AS OrderDate,
Format(DiagramSimMonthly.VanningDate,"yymmdd") AS VanningDate,
Sum(Format(DiagramSimMonthly.VanningVolume,"00000")) AS
SumOfVanningVolume,
Format(DiagramSimMonthly.ProductionDate,"yymmdd") AS ProductionDate,
Sum(Format(DiagramSimMonthly.UsedVanningVolume,"00000")) AS
ProductionVolume,
Format(TEST_CalcWkConv.CalcWk,"0000") AS CalcWk
FROM TEST_CalcWkConv, (DiagramSimMonthly INNER JOIN SeriesGrouping ON
(DiagramSimMonthly.Series = SeriesGrouping.Series) AND
(DiagramSimMonthly.DPV
= SeriesGrouping.DPV) AND (DiagramSimMonthly.DPP = SeriesGrouping.DPP) AND
(DiagramSimMonthly.Variation = SeriesGrouping.Variation)) INNER JOIN
TEST_LinePath ON SeriesGrouping.Series = TEST_LinePath.SeriesName
GROUP BY TEST_LinePath.LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd"),
Format(DiagramSimMonthly.VanningDate,"yymmdd"),
Format(DiagramSimMonthly.ProductionDate,"yymmdd"),
Format(TEST_CalcWkConv.CalcWk,"0000"), IIf((DiagramSimMonthly!Orderdate
Between TEST_CalcWkConv!BegOrderdate And
TEST_CalcWkConv!EndOrderdate),Test_CalcWkConv!CalcWk,"NA"),
DiagramSimMonthly.PorA, SeriesGrouping.PatternName
HAVING (((DiagramSimMonthly.PorA)="a") AND ((SeriesGrouping.PatternName)
Like "Group 5") AND ((IIf(([DiagramSimMonthly]![Orderdate] Between
[TEST_CalcWkConv]![BegOrderdate] And
[TEST_CalcWkConv]![EndOrderdate]),[Test_CalcWkConv]![CalcWk],"NA")) Not
Like
"NA"))
ORDER BY TEST_LinePath.LinePath,
Format(DiagramSimMonthly.OrderDate,"yymmdd"),
Format(DiagramSimMonthly.VanningDate,"yymmdd"),
Format(DiagramSimMonthly.ProductionDate,"yymmdd");
Click to expand...
 
Back
Top