I have a date field in the table in question. I'm trying to create a query that will only display records where that date field are 120 days old or older.
Solution Attempt 1
The first solution I tried was simply added criteria to my date field. The criteria formula I used was:
This removed a few 'random' records leaving me with 714 of my original 905 records. Unfortunately, a quick look through the dates of remaining records in ascending order, it was obvious that I was still getting records more recent that 120 days old.
Based on my 'random' result above, I double checked the format of my field by changing the field to:
DATE: Format([myDtField], "mm/dd/yyyy")
I ran the query again - this reduced the number of records removed, leaving me with 734 of 905 - but my issue persisted - I still had records with dates more recent than 120 old.
Solution Attempt 2
Based on my issues above, I decided to go a different route. This time I created a unique field for my criteria calculation. For the field value I used:
This resulted in values that were very far off from the correct values (ex. a record with a date of yesterday resulted in 43!).
Solution Attempt 3
This was less of a solution and more just troubleshooting, but based on the results I'm getting, I keep thinking my dates aren't being perceived by the system as the date it's displaying (i.e. DateValue() is off compared to displayed value). I spot checked a few of the dates vs their DateValue() and the randomly selected records all seemed to be correct. So again, no luck.
Solution Attempt 4
@Gustov reminded me about the DateValue() function. I've attempted that on the field as well - the field is imported as a text field, thus needs converted to a date value. Similar to the formula Gustov posted, for my field value I used:
And then in the criteria I simply used the formula:
This results in the following error:
Data type mismatch in criteria expression
This was probably the closest solution I had, simply because WITHOUT criteria it returns the proper values (i.e. a date of yesterday returned "1", while a date of two days ago returned "2"... etc). So you would think simply limiting records where this field is >120 would work, but then it throws up the error above.
- Does anyone have an idea of how to check if a record field is 120
days old or older?
- If either of my above solutions works for question 1, then what might be wrong with my date field that is causing issues if I am going about it the right way?
- @Gustov's DateValue() solution is close (i.e. provides the correct values), but adding criteria causes an error. Any solutions?
I'm at a loss. An extra set of eyes on this problem would be greatly appreciated. Thanks!