Date formatting exporting to Excel 1

[LimitedTech]

I use the following code to export a Query to Excel 2003

Dim strQryName As String, strXLFile As String, strName As String
strQryName = "QryBillingMailMerge"
strXLFile = "F:\weeklybilling.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strQryName, strXLFile
FollowHyperlink "F:\weeklybilling.xls"

The problem I am having is the exported sheet go to a Canadian company. They format their dates YYYY/MM/DD, Mine are formatted MM/DD/YYYY. I have the format set to YYYY/MM/DD in the QryBillingMailMerge Quesry but when it exports it reverts to MM/DD/YYYY. How can I remedy this?

Thank You

 

[SkipVought]

HI,

A Date/Time value, both in Access and Excel is a NUMBER. Today's Date Value, Oct 23 2016, is 42666. What you see DISPLAYED is a formatted date. Whether the FORMAT is yyyy/mm/dd or mm/dd/yyyy, there is ONE underlying numeric value for any date. You can change the Number Format for your dates. Select the date range, right-click in the selection and select Format Cells... > Number TAB, and select an appropriate Date format.

BTW, if you change the Number Format for any Date to GENERAL, you will see displayed the Numeric Date Value, also called the Date Serial value. In Excel Date Serial ZERO is 1900/01/00. Excel does not allow negative Date Serial values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]