Problem comparing dates

[Petrol]

Oh dear, I thought I understood how dates are kept and manipulated in Access, but this one has me beat.
I have a subform based on a table "Team_Selection_Notes" which has three fields:

• PersonID (Number - the link field)
• NoteDate (Date/Time) and
• Comment (Short text)).

In the subform I want to display only those notes for a person that are older than a given age (temporarily set in the code to 6 years for testing purposes, but it will be selected by the user in the main form). The relevant procedure in the main form is as follows:

Private Sub Oldies_Click()
'
'  Routine to display only records older than a certain age in the subform
'
Dim EarlyDate As Date, MinimumAge As Integer, strSQL As String
On Error GoTo ErrorProc
MinimumAge = 6
 
EarlyDate = DateSerial(Year(Now) - MinimumAge, Month(Now), Day(Now))
Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < " & EarlyDate
Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.FilterOn = True
 
ExitSub:
    Exit Sub
 
ErrorProc:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "Searching for old records"
    Resume ExitSub
 
End Sub
 

It is the comparison in the filter that is not working the way I expected it to.
A particular person has 3 notes, dated 22 Aug 2013, 22 Aug 2013 and 18 Feb 2018. With the cut-off age set at 6 years the filter prevents any of the records being displayed. If I change the inequality in the filter from < to >, then all three records are displayed.
I must be missing something obvious, but what is it?

[NeoPa]

Hi Petrol. It's been a while :-)

What you're missing here is the hash (#) characters that tell SQL that the date string should be interpreted as such and not as a simple number (or calculation: 3/4/2021 ==> 3 divided by 4 divided by 2021. See Literal DateTimes and Their Delimiters (#) for the full SP on that.

In the USA you can get away with lazy-printing dates but really they should be formatted in a valid SQL format in order to be recognised correctly even with the hashes. It just so happens that the standard format used in the USA is one of those adopted by SQL as a valid standard so usually you can get away with doing it wrong there. Never fun to find your work fails whenever it goes overseas though ;-)

[cactusdata]

You can simply use:

Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < DateAdd('yyyy', " & -MinimumAge & ", Date())"

[NeoPa]

Or even simpler :

Dim strWhere As String
Dim MinimumAge As Integer
 
MinimumAge = 6
strWhere = Replace("([NoteDate]<#%ED#)" _
                 , "ED" _
                 , Format(DateAdd("yyyy", -MinimumAge, Date()),"yyyy\-m\-d")
With Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form
    .Filter = strWhere
    .FilterOn = True
End With

That ensures the date calculation is done :

1. Before it's passed to SQL so can be examined if required.
2. Once only in VBA rather than every time it's used in SQL.

Obviously consider where the code runs from and replace Forms![18: Team Selection Notes]![Subfrm_TSNotes] or Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form with Me.

CactusData implies two points that are worthwhile to remember :

1. Date() & Now() are both function calls an not simple values. Your code is clearer when you use the parentheses to illustrate this.
2. Date() returns the same date value as Now(), which also includes the Time part. If you are looking for the date only then it's confusing to use Now() instead of Date().

[Petrol]

NeoPa and CactusData, thank you both very much for your enlightening replies!

NeoPa #2: Yes, it has been a while. So I am obviously very rusty!
I knew about the # delimiters, but thought they were only needed if giving a literal text string. Since EarlyDate is Dimensioned as Date and is generated by DateSerial() I thought it would be obvious that it was a date.
Anyway, I guess that means I should use

 Me.Filter = "NoteDate < #" & EarlyDate & "#" 

- Right?
...
Ahh, now I think I see it. I think I was confusing what VBA "knows" with what SQL "knows".

CactusData #3: Yes, that is simpler, and almost as easy to follow once I looked up the parameters for DateAdd!
Thanks also for the suggestion of Date() instead of Now, as NeoPa has pointed out.

NeoPa #4: Hmm, I'm not sure that that is "even simpler", but I admit it's more elegant. And thanks also for the two comments at the end - I will bear those points about Date() and Now() in mind.

(For some reason I didn't receive an email notification of any of your replies. I used to be automatically following any of my posts. I will have to investigate that ... )

[NeoPa]

Petrol:
Anyway, I guess that means I should use

Me.Filter = "NoteDate < #" & EarlyDate & "#"

Right?

A little bit - but no.
The date part of the string should always be formatted when using date literals in SQL. Simply adding hashes doesn't do a good job. According to your explained logic you should be using <=.

Petrol:
Ahh, now I think I see it. I think I was confusing what VBA "knows" with what SQL "knows".

That's the understanding. Right there! Bang on.

Petrol:
(For some reason I didn't receive an email notification of any of your replies. I used to be automatically following any of my posts. I will have to investigate that ... )

I've updated your last post to ensure you get notifications of replies in this thread at least. You'll find your default within your profile settings.

Petrol:
Hmm, I'm not sure that that is "even simpler", but I admit it's more elegant.

That's because you're looking at all of the changes rather than just the bit which does that specific job. It uses a very similar concept to what CactusData was doing but puts it in the VBA code instead of the SQL. If you're more used to clumsy string building where you say X & "..." & Y & "..." etc then maybe this is easier to perceive :

strWhere = Format(DateAdd("yyyy", -MinimumAge, Date()),"yyyy\-m\-d")
...
    .Filter = "([NoteDate]<=#" & strWhere & "#)"

One area it's different is that CactusData's suggestion works directly with dates whereas mine uses a date literal string - which is why the hashes are required in my version but not his.

PS. My code sometimes looks more complicated because I choose to split lines if they exceed 80 chars. I don't trust that people will be able to read it easily if it scrolls off the viewable page so limit it to 80 even within my projects.

[Petrol]

Thanks, NeoPa. I'm appreciating your advice and the time you put into it.
("NeoPa" would appear to mean "new father", which might explain why you were at the computer at 3:30am :-)
- except that you have been using that name for years, and kids do grow up ... )

Anyway, let me attempt to simplify it even further for my addled simplistic brain.
If we agree that the filter in both examples is "[NoteDate] < xxxxx", then in CactusData's version the xxxxx is

DateAdd('yyyy', " & -MinimumAge & ", Date())

and in yours it's

"#" & Format(DateAdd("yyyy", -MinimumAge, Date()),"yyyy\/m\/d") & "#"

If this is correct, can you clarify again for me just why the second version is better?
I used the first version and it seemed to work ...

[NeoPa]

Hi Petrol. Very happy to.

First off - Before I started at Bytes I was the father of a gamer who, naturally enough at the time as he was an impressionable teen and The Matrix was still very much the thing, used the nick Neo (or N30 to be precise). When I joined him as a sort of somewhat elderly (Being past my twenties by then already which to most of his clan mates was ooooooold!) side-kick, and actually got invited into the clan itself because I could configure the server for different games even better than their resident server bod, then I chose the name of NeoPa to indicate that I was his dad. I've used it off & on since. Nowadays I'm a Microsoft MVP so I tend to be less worried about anonymity and I'm happy to share my name (Adrian Bell), but back then it was usual to use nicks of some form or another.

Your post compares the string found in the SQL for one side with the VBA code in the other - so is not a viable comparison. My version is simpler because the VBA does the work and thus the comparison would be more like :

DateAdd('yyyy', " & -MinimumAge & ", Date())
versus
#2015-8-14#

Even that's not really a fair comparison as CactusData could fairly claim that while the SQL string may be less simple in itself - it didn't require pre-working in VBA, so take your pick. They're very close as far as simplicity goes whichever way you see it. I have my preference but I suspect CactusData sees it differently and you're free to make your own mind up once you appreciate the situation fully.

NB. I noticed I was using the wrong date format so changed this and my earlier posts to match the correct format.

[Petrol]

Right. I see now. Thanks for clarifying that.

About the same time that you were configuring games servers I was a "camp parent" on a church camp, and on the free Saturday night I wandered in to a room where they were watching The Matrix. It was about half-way through and I watched for about 20 minutes without having the slightest clue what was going on, and there my experience of The Matrix started and ended. So I've never seen, heard or met N30 - and am happy to remain in my ignorance!

[NeoPa]

At the risk of cluing you up where you don't want to be I would simply add that the actual name of the hero is Neo (Greek for New.) but that a very cool thing to do (at the time for teens especially) was to spell things in non-standard ways. This often included digits to represent similar characters. Very much like using 58008 on a calculator was in earlier decades to represent BOOBS if turned upside down. 0==O; 1==l(ima); 2==Z; 3==E; 4==H; 5==S; 6==b; 7==T; 8==B & 9==q hence his version of Neo.

Another very common one for his time was to refer to themselves (or anything good) as 1337 (LEET) - which is obviously (Their logic.) short for Elite. I say obviously, most of them didn't even realise where it came from - they just knew it was a good thing to be.