Date comparison puzzle

[FuzMic]

Hi

I try to select from table records where a date.fld eg DTrn is > (date() - 12). I tried varying it with format(dd-mmm-yy) & ## (on the field and the variable) but can't get it fix.

Please help.

 

[FuzMic]

PS:
Just got it fixed using

Format([DTrn], 'dd-mmm-yy') > Format((Date() - 3),'dd-mmm-yy');

Resolved

 

[MarkK]

What are the symptoms of the problem? How did it fail?

 

[Galaxiom]

PS:
Just got it fixed using

Format([DTrn], 'dd-mmm-yy') > Format((Date() - 3),'dd-mmm-yy');

Resolved

That might work accidentally on some dates but could not possibly be reliable.

By using Format you are comparing on the alphabetical order of the dates as strings.

 

[FuzMic]

Galaxiom, you seem to say you have a better way, how?
MarkK, if just failed to select.
Thanks.

 

[Galaxiom]

Is the date field you are testing definitely a Date/Time field type in the table, not text?

 

[FuzMic]

It is a date type field being tested.

 

[Galaxiom]

This condition on the field should show all records with dates within the past 12 days or in the future.

> Date() - 12

Something odd going on. Is the query just in the Query Designer against a linked or local table in Access? Not built as SQL in VBA?

Can you post the cut down database?

 

[FuzMic]

Hi mate

It is a linked table in the FE. It is actually a new ADODB.recordset in a VBA script that failed to pick up correctly.

rs.Open "SELECT Sum(Amt) AS SAmt FROM Trn WHERE [TDate] > Date() - 3;", _
CurrentProject.Connection, 1, 3, 1

Hope this will give you a clue instead of a cut.down .mdb. All these happening in access.03 in win10.64bit OS.