Access 97 Dates - transposing day and year

[TrainEase]

I have an Access 97 front end over SQL Server 7 back end.

A process adds records to a table, using infomation from a process form and a related items file, using an entered date in the process form as the date to write to all records.

It always worked fine up to the end of last year, but now when writing the dates away - it turns them round completely - not just dd/mm to mm/dd - it moves the year element and puts the day in the year!!

Here’s the code (with 22/01/2001 in [DateEffectiveFrom] on the form)

tSQL = "INSERT INTO tbl_Pricing ( ProductID, EffectiveFrom, EffectiveTo, SupplierID, CostPrice, CostCurrency, SellPrice, SellCurrency, Retro, SupRef ) SELECT q_CompareDespecXMAPrices.ProductID, #" & DateValue(Format(Me![DateEffectiveFrom], "mm/dd/yyyy&quot) & "# AS tDate, #" & DateValue("12/31/" & Year(Date)) & "# AS tDate2, 'DESP01' AS tSupp, q_CompareDespecXMAPrices.[Despec Price], q_CompareDespecXMAPrices.CostCurrency, q_CompareDespecXMAPrices.Sell, q_CompareDespecXMAPrices.SellCurrency, q_CompareDespecXMAPrices.Retro, q_CompareDespecXMAPrices.SupRef From q_CompareDespecXMAPrices WHERE (((q_CompareDespecXMAPrices.XMADespec)=1));"

DoCmd.RunSQL tSQL

It puts in 01/01/2022 as EffectiveFrom and 01/12/1931 as EffectiveTo

Or at least that’s what it gives us on screen.

Wrote this script in Query Analyser

Select EffectiveFrom, EffectiveTo from tbl_Pricing
Where ProductID = "2089546UK";

The result is –

EffectiveFrom EffectiveTo
--------------------------- ---------------------------
2000-05-23 00:00:00.000 2000-12-31 00:00:00.000
2000-09-01 00:00:00.000 2000-12-31 00:00:00.000
2000-09-04 00:00:00.000 2000-12-31 00:00:00.000
2000-10-01 00:00:00.000 2000-12-31 00:00:00.000
2000-10-03 00:00:00.000 2000-12-31 00:00:00.000
2000-11-06 00:00:00.000 2000-12-31 00:00:00.000
2000-12-05 00:00:00.000 2001-12-31 00:00:00.000
2000-12-08 00:00:00.000 2001-12-31 00:00:00.000
2022-01-01 00:00:00.000 1931-12-01 00:00:00.000

So it DOES mess up the tables – it’s not just a display problem like the long text thingy in Access 2000

Nothing has changed in this code.

Any clues?

Regards, rob@trainease.com

 

[TrainEase]

Found the problem

Using DateValue() was causing it.

Don't aske me why it was there - I inherited the system and have taken the rule not to mess with the code unless I prove it breaks something.

What is weird is that DateValue(format(DateField, "mm/dd/yyyy&quot) returns dd/mm/yyyy - not mm/dd/yyyy as formatted

Why this should then cause the year to become the day and visa versa is anyone's guess - but with DateValue() taken out - it all works okay now.

Regards, rob@trainease.com