VBA from same query returns different date formats

[Mattbro]

Hi all,
I have a database with a module that allows me to generate a multiple paged excel report with data from queries. My issue is, the code for output is more or less identical yet one query results in teh date appearing as dd/mm/yyyy yet the other returns the double numberic value. Odd in that they are coming from the same query.
Code for the first Sheet;

Set xlSheet = xlBook.Worksheets(1)
strQuery = "SELECT Cint (QryFullTATReport.[Week]) as wkn, QryFullTATReport.[WeekStart],QryFullTATReport.Cases, QryFullTATReport.Transport, QryFullTATReport.Reception, QryFullTATReport.Transfer from QryFullTATReport"
On Error GoTo Error_Query
Set rsRecordset = CurrentDb.OpenRecordset(strQuery)
On Error GoTo 0
For lCounter = 0 To rsRecordset.Fields.Count - 1
xlSheet.cells(1, lCounter + 1).Value = rsRecordset.Fields(lCounter).Name
Next
xlSheet.cells(2, 1).CopyFromRecordset rsRecordset
xlSheet.cells.EntireColumn.AutoFit

...gives a date value for QryFullTATReport.[WeekStart] which is exactly what it is, however the similar code for sheet 2;

Set xlSheet = xlBook.Worksheets(2)
strQuery = "SELECT Cint (QryFullTATReport.[Week]) as wkn, QryFullTATReport.[WeekStart] as wkn, QryFullTATReport.[RED], QryFullTATReport.BLUE, QryFullTATReport.GREEN from QryFullTATReport ORDER BY [Week]"
On Error GoTo Error_Query
Set rsRecordset = CurrentDb.OpenRecordset(strQuery)
On Error GoTo 0
For lCounter = 0 To rsRecordset.Fields.Count - 1
xlSheet.cells(1, lCounter + 1).Value = rsRecordset.Fields(lCounter).Name
Next
xlSheet.cells(2, 1).CopyFromRecordset rsRecordset
xlSheet.cells.EntireColumn.AutoFit

Returns the date value as a numeric double e.g. 28/02/2022 becomes 44620.
Any suggestions gratefully recieved.


K/R,
M

[Welshgasman]

You have wkn twice in the second query?

[Mattbro]

You have wkn twice in the second query?

My error-should read;

Set xlSheet = xlBook.Worksheets(2)
strQuery = "SELECT Cint (QryFullTATReport.[Week]) as wkn, QryFullTATReport.[WeekStart], QryFullTATReport.[RED], QryFullTATReport.BLUE, QryFullTATReport.GREEN from QryFullTATReport ORDER BY [Week]"
On Error GoTo Error_Query
Set rsRecordset = CurrentDb.OpenRecordset(strQuery)
On Error GoTo 0
For lCounter = 0 To rsRecordset.Fields.Count - 1
xlSheet.cells(1, lCounter + 1).Value = rsRecordset.Fields(lCounter).Name
Next
xlSheet.cells(2, 1).CopyFromRecordset rsRecordset
xlSheet.cells.EntireColumn.AutoFit

[Welshgasman]

Check the format of the column in sheet 2 compared to sheet 1. Swap the sheets even as a test?

[Mattbro]

Check the format of the column in sheet 2 compared to sheet 1. Swap the sheets even as a test?

Bizarre. If I swap the 'SELECT' statements', the data in Sheet 1 gives the date in the correct format and the data in Sheet 2 is a numeric double, but to my eye, the code outside the 'SELECT' statements, is identical, so why does statement 1 work and statement 2 not?

[Welshgasman]

As I mentioned, check the format of the sheet2.

Easy enough to test also.
Put the select statement in a new query and see what each produces.