I have a table that store when the last end of day was performed.
It's obviously held as a date format dd/mm/yyy hh:mm:ss
When the next end of day is performed it accesses the table and gets the last date when the end of day was performed. It then uses this in a query to get the sum of goods sold since last end of day
Me.Amountsold = Nz(DSum("net", "OrdersMade", "Salesdate > #" & Me.DateAndTimeLastClosed & "#"))
This worked perfectly on Friday 30/11.
It doesn't work today on the 3/12.
I suspected and have proved the problem is that although the last end of day was stored as 03/12/2018 13:49:23 (i.e. 3rd of December) when it is used in the query it still appears to be the same but now acts as though it's the 12th March. It I change Me.DateAndTimeLastClosed to 12/03/2018 13:49:23 it works as expected.
Can I stop this behaviour? It clearly recognises dd/mm when storing (I use now() as the default on the table design) but doesn't when using it.
Heeeelp!
It's obviously held as a date format dd/mm/yyy hh:mm:ss
When the next end of day is performed it accesses the table and gets the last date when the end of day was performed. It then uses this in a query to get the sum of goods sold since last end of day
Me.Amountsold = Nz(DSum("net", "OrdersMade", "Salesdate > #" & Me.DateAndTimeLastClosed & "#"))
This worked perfectly on Friday 30/11.
It doesn't work today on the 3/12.
I suspected and have proved the problem is that although the last end of day was stored as 03/12/2018 13:49:23 (i.e. 3rd of December) when it is used in the query it still appears to be the same but now acts as though it's the 12th March. It I change Me.DateAndTimeLastClosed to 12/03/2018 13:49:23 it works as expected.
Can I stop this behaviour? It clearly recognises dd/mm when storing (I use now() as the default on the table design) but doesn't when using it.
Heeeelp!