CODEWITHSUNDEEP
ms-accessms-access-2010
spizotfl
spizotfl

Reputation: 33

MS Access Not Exporting Left Padded Digits

I have a table with some fields that are char(2). Some fields need to have a leading 0 (zero) if the field is only 1 character long. I have created a query where I have tried a couple of different formatting methods to achieve the left padding. These attempts work fine when looking at the query results but when I examine the text file that is created on export, the fields in question do not contain the leading 0.

Here are two methods I used in the query

`FORMAT(tblMHOutcome.Dayscom, "00") AS Dayscom`
`IIF(LEN(tblMHOutcome.Daysatte)=1, "0"&tblMHOutcome.Daysatte`

Below is an image showing the view in the export window with the fields padded:

Padded field in export window

Below is an image of the resulting text file without the field padding:

After export no pad

Any suggestions on how to get access to export the query as it is formatted?

UPDATE: Added Full Query

`SELECT tblMHOutcome.ContractorID, tblMHOutcome.SiteID, tblMHOutcome.SSN,     
tblMHOutcome.ClientID, tblMHOutcome.Purpeval, tblMHOutcome.Evaldate, 
tblMHOutcome.StaffID, tblMHOutcome.Initevada, tblMHOutcome.Pincosrc,     
tblMHOutcome.Disincom, tblMHOutcome.Prognosis, tblMHOutcome.Depcrims, 
tblMHOutcome.Admitype, FORMAT(tblMHOutcome.Dayscom, "00") AS Dayscom, 
FORMAT(tblMHOutcome.Dayswork, "00") AS Dayswork, tblMHOutcome.Incopay, 
tblMHOutcome.Incogov, tblMHOutcome.Incother, tblMHOutcome.GAF, 
IIF(LEN(tblMHOutcome.Daysavai)=1, "0"&tblMHOutcome.Daysavai, tblMHOutcome.Daysavai) AS 
Daysavai, IIF(LEN(tblMHOutcome.Daysatte)=1, "0"&tblMHOutcome.Daysatte, 
tblMHOutcome.Daysatte) AS Daysatte, IIF(LEN(tblMHOutcome.CGAS)=1, 
"0"&tblMHOutcome.CGAS, tblMHOutcome.CGAS) AS CGAS, tblMHOutcome.DJJComit, 
tblMHOutcome.Riskfact, tblMHOutcome.Residstat, tblMHOutcome.Marital, tblMHOutcome.Empl, 
tblMHOutcome.Residcont, tblMHOutcome.Grade, tblMHOutcome.Rx, tblMHOutcome.Develop, 
tblMHOutcome.Physical, tblMHOutcome.Ambulat, tblMHOutcome.Visual, tblMHOutcome.Hearing, 
tblMHOutcome.English, tblMHOutcome.ADLFC, tblMHOutcome.Provinfo, tblMHOutcome.Zip, 
tblMHOutcome.Tstat, tblMHOutcome.Famsize, tblMHOutcome.MHProb, tblMHOutcome.Faminc, 
tblMHOutcome.Referral, tblMHOutcome.Provid, tblMHOutcome.MHDiagnosis, 
tblMHOutcome.SADiagnosis, tblMHOutcome.Bakeract, tblMHOutcome.Rxidp, 
tblMHOutcome.Rxpap, tblMHOutcome.Contnum1, tblMHOutcome.Contnum2, 
tblMHOutcome.Contnum3, tblMHOutcome.Vetstatus, tblMHOutcome.Social, 
tblMHOutcome.School, tblMHOutcome.Arrest
FROM tblMHOutcome
WHERE (((tblMHOutcome.DeleteRecord)=False))
ORDER BY tblMHOutcome.Evaldate;`

And VBA Code:

`DoCmd.TransferText acExportFixed, "MHOutcomeExport", "qryMHOutcomeExport", "Exports\MHOutcome.txt", False`

Upvotes: 1

Views: 373

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You could use:

SELECT Format(ANumber,"00") AS Expr1 
INTO [Text;FMT=Fixed;DATABASE=Z:\docs].[exp.txt] 
FROM table1;

A schema.ini can help, too. For eample, Handle TransferText Errors

Alternatively, this will create a two digit number:

SELECT Right("00" & Anumber,2)
FROM Table1;

And

DoCmd.TransferText acExportFixed, "expspec", "Query3", "z:\docs\exp.txt"

Re Comment

SELECT IIf(Not IsNull(ANumber),Right("00" & Anumber,2), ANumber)
FROM Table1;

Upvotes: 1

Related Questions