Export to Excel truncates text field (1 Viewer)

[majhl]

I use the code below to loop through a recordset and then export every record with a matching practice id to an Excel spreadsheet. The code works fine as it is, except for a minor problem with spreadsheet itself.

I've noticed that one of the fields in the Excel file has been truncated to 30 characters. The corresponding field in the db is of type text with 100 characters.

Does anyone know why this should be and if there's a way around it?

TIA

(Office 2010 64-bit)

On Error GoTo ErrExportAttachment

'declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim rst As DAO.Recordset
Dim strDT As String

'initialise db variable
Set db = CurrentDb
'initialise querydef object
Set qdf = db.QueryDefs("qryMailingList")

'Set up control loop
strSQL = "SELECT Practiceid FROM tblChildEmail "

'initialise recordset object
Set rst = db.OpenRecordset(strSQL)

'do until the last record is reached
While Not rst.EOF

'initialise strDT variable
strDT = Year(Date) & Month(Date) & Day(Date)

'set up the SQL string
strSQL = "SELECT StudyNo, DoB, "
strSQL = strSQL & "ScreeningResult, DNAResult, ActionRequired "
strSQL = strSQL & "FROM tblChildEmail "
strSQL = strSQL & "WHERE practiceid = '" & rst![PracticeID] & "' "

'Debug.Print strSQL

'set SQL statement that defines the query executed by the QueryDef
qdf.SQL = strSQL
     
'switch off warnings
DoCmd.SetWarnings False

'export excel spreadsheet using defined path and file name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMailingList", "C:\EmailFromAccess\" & rst![PracticeID] & strDT & ".xls", True

'move to the next record
rst.MoveNext
  
'end while statement
Wend

MsgBox ("Spreadsheets exported!"), vbOKOnly, "Export"

'kill and close variables
rst.Close
Set rst = Nothing
Set db = Nothing
strSQL = ""

'set warnings to rtue
DoCmd.SetWarnings True

ErrExportAttachment:
Exit Sub

MsgBox Err.Description
Resume ErrExportAttachment

 

[spikepl]

A stab in the dark: Does the field contain carriage returns/line feeds?

 

[majhl]

A stab in the dark: Does the field contain carriage returns/line feeds?

Thanks for the replay. No, nothing like that.

 

[majhl]

Thanks for the replay. No, nothing like that.

Some extra information. If I export the data to Excel manually, the text field is not truncated.