Removing the first character using VBA

[Nelmo]

I posted an earlier question about an Access query export to Excel putting leading apostophe (') before all data.
To remove them I thought I would use Find/Replace but the Apostophe is not recognised!

Is there a way (programmatically) to remove the first character of each data entry in each cell in a column?

thanks in advance

nel

 

[grnzbra]

Are you trying to remove it in Access or in Excel?

 

[KeithG]

in a query use Right([FieldName],(Len([FieldName])-1))

FieldName=Name of your Field

 

[bvan]

Remove first char

This should do the trick..

Mid([Fieldname],2)

 

[Kenny]

I know this was a while ago but I have just been having the same issue. The problem was fixed by using the 'OutputTo' command instead of 'TransferSpreadsheet', no apostophes appear in the text cells.

Hope this helps someone as I have been looking at this for 2 or 3 days with much frustration.

CODE:

Private Sub comExport_Click()
Dim file As String

file = "j:\everyone\Vad\Bloomberg\" & Form_fAuditSelect.ReconciliationID & "FIA.xls"

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tBloomberg", file, True

DoCmd.OutputTo acOutputTable, "tBloomberg", acSpreadsheetTypeExcel9, file, False

End Sub