MS Access SQL Format function not rendering correctly

Question

Hi Folks -

I have the following MS Access SQL query:

SELECT DISTINCT
	'Changeprop' AS [PARAM1]
	,'TREX-WorkingVersion' AS [PARAM2]
	,'Partner' AS [PARAM3]
	,[SFDC_PTRPTP].[PARTNERSHIPID] AS [PARAM4]
	,'Execution_Date' AS [PARAM5]
	,Format([SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE], "ddmmmyyyy") AS [PARAM6]
FROM [SFDC_PTRPTP]
INNER JOIN [MDM_Partner_Detail_Reference] ON [SFDC_PTRPTP].[PARTNERSHIPID] = [MDM_Partner_Detail_Reference].[Name]
	WHERE ( Format([SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE], "ddmmmyyyy")  <> [MDM_Partner_Detail_Reference].[Execution_Date])

It's used to build an import file for a financial system. The strange thing is some of the dates are not formatting correctly. For instance,  [SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE] is a Date/Time column from a MSSQL DB with a value of 12/19/2013.

However, when I run the above query, the result is displayed as:

PARAM1    PARAM2    PARAM3    PARAM4    PARAM5    PARAM6
Changeprop    TREX-WorkingVersion    Partner    PTP-10274    Execution_Date    19Dec0201

Why would this be?

Thank you!

Answer 1

Try changing the date formats to mm/dd/yyyy or remove date formatting completely

Answer 2

Ah, this ended up being a data type issue. Thank you for the suggestion, it helped me nail it down.