IIF function using Date parameter returning wrong date

[helpaccessguy]

Hello,

I am running the following query.

IIf([PMN Pull1].TYPE='C' And [Outbound Raw].[Date]=[Enter CALL date YYYY-MM-DD],[Outbound Raw].[Settlement Date]=[Enter Settlement Date YYYY-MM-DD:],[Outbound Raw].[Settlement Date]) AS [Settlement Date]

I want to input a date where Type ='C' and change the settlement date according to my input. However, with this method when i input "2019-03-31" as my settlement date it returns "1899-12-29"



Is there a way to force the parameters to return the value as is?

Thanks
D

[June7]

I NEVER use input parameter popup prompts in query because cannot validate input.

I just tested and not an issue, returns date as 3/31/2019.

Post entire query SQL statement.

[helpaccessguy]

Thank you for your quick reply June7. Sorry I left work but will upload the query tomorrow. I was thinking it may be my date format in my original table it is YYYY MM DD.

Would there be a better way to do this? My settlement date value would be the end date of my call date value.

For example my call date would be 03/01/2019 and my settlement date would be 03/31/2019

[June7]

Format property has no bearing on how query handles date/time values stored in a date/time field.
Date/time is actually stored as a double number. The date format is for display purpose. Access default is mm/dd/yyyy.

I don't set formatting in table.

What do you mean by "end date of my call date"? Always last day of month?

[helpaccessguy]

Yes. It will always be the end of the month. If I enter call date 04/01/2019 then the settlement date change will be 04/30/2019

[June7]

Took another look at that expression. That second = clause makes no sense. It would return a True or False, not a date. And True (-1) evaluates to 12/29/1899 as a date.

[ArviLaanemets]

The query like below works. You are asked for parameter Date1, you enter the date in your regional settings date format once, and you get 2 dates

SELECT ..., [Date1] AS YourDate, DateSerial(Year(YourDate),Month(YourDate)+1,0) AS SettlementDate
FROM YourTable;

Btw, don't use Date as field name. Especially in case you refer to this field in queries - Acces may decide you want current date returned.