CSV file - Date

[darinmc]

Hi, I have this code to create a csv file

Private Sub cmdTestCSV_Click()
Dim StrFile
StrFile = "\\Lynxserver\lynx\test" & ".csv"
DoCmd.TransferText acExportDelim, , "SAGE EmployeeDetailsTemplate", StrFile, False
End Sub

[red]Below is the query in Access... The field in BOLD/RED is a bank sort code, it is a text field in the employee table, HOWEVER, in the csv file, its changed to a date!!!
How do I make that stay as general and display as per table e.g. 10-10-10???[/red]

[blue]The other question, a telephone number, e.g displays as 079.. BUT the leading 0 is omitted, is it possible to keep this???[/blue]

SELECT tblEmployee.EmpNo, tblEmployee.EmpNo AS WkRef, tblEmployeeLookupTitle.EmpTitle, tblEmployee.EmpInitials, tblEmployee.EmpForename, tblEmployee.EmpSurname, tblEmployee.EmpAddress1, tblEmployee.EmpAddress2, tblEmployee.EmpAddress3, tblEmployee.EmpAddressArea, tblEmployee.EmpAddressTown, tblEmployee.EmpAddressPostCode, Null AS Email, [blue]tblEmployee.EmpMobileNo,[/blue] tblEmployee.EmpGender, tblEmployee.EmpMaritalStatus, tblEmployee.EmpDateOfBirth, tblEmployee.EmpCommencementDate, Null AS EndDt, tblEmployee.EmpNationalInsuranceNo, tblEmployee.EmpNIcategory, tblEmployee.EmpTaxCode, 0 AS Wk1, Null AS Pen1, Null AS Pen2, Null AS Pen3, Null AS Pen4, Null AS Pen5, "BACS" AS PayMent, tblEmployee.EmpPaymentFrequency, Null AS GS, Null AS CH, Null AS AL, Null AS DS, Null AS Ddt, Null AS Notes, Null AS Cont, Null AS Crel, Null AS Ctel, Null AS Null1, Null AS Null2, [red]tblEmployee.EmpBankSortCode,[/red] tblEmployee.EmpBankAccountNo, tblEmployee.EmpAccountHolderName, Null AS BaccT, Null AS BldSocNo, tblEmployee.EmpBuildingSocietyRef, tblEmployee.EmpBankName
FROM tblEmployeeLookupTitle RIGHT JOIN tblEmployee ON tblEmployeeLookupTitle.EmpTitleID = tblEmployee.EmpTitle
WHERE (((tblEmployee.EmpRegNo)=3617))
ORDER BY tblEmployee.EmpNo;


Thx
Darin

 

[JBinQLD]

You can either set up an export profile, or perhaps try this:

cstr(tblEmployee.EmpBankSortCode)

(bit in red)

Hope that helps,

JB

 

[darinmc]

Thx

I tried the above but it didnt seem to do anything different...

I'm trying to get the data into a Sage Payroll CSV file, one of the problems is that the field name is:

Contact Telephone No.

in the query below, part of the code is:

Null AS [Contact Telephone No],

I have tried quite a few different things but it just WONT allow me to use the . (dot)
Is there away to insert it???

The Sage template has to have the headings else it wont import...

[red]I was thinking of another way round this[/red]
I could do the export from access d/base straight into a txt file without the headings, then not sure how to do it or write a macro, BUT, if it was possible, OPEN up the template, run a macro which would insert ALL the txt files in a particular directory and insert them into the template AFTER 1st row containing headings

Thx
Darin