473,218 Members | 1,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

Problem comparing dates

204 128KB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Oldies_Click()
  2. '
  3. '  Routine to display only records older than a certain age in the subform
  4. '
  5. Dim EarlyDate As Date, MinimumAge As Integer, strSQL As String
  6. On Error GoTo ErrorProc
  7. MinimumAge = 6
  9. EarlyDate = DateSerial(Year(Now) - MinimumAge, Month(Now), Day(Now))
  10. Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < " & EarlyDate
  11. Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.FilterOn = True
  13. ExitSub:
  14.     Exit Sub
  16. ErrorProc:
  17.     MsgBox "Error " & Err.Number & ": " & Err.Description, , "Searching for old records"
  18.     Resume ExitSub
  20. 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?
Aug 10 '21 #1

✓ answered by cactusdata

You can simply use:

Expand|Select|Wrap|Line Numbers
  1. Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < DateAdd('yyyy', " & -MinimumAge & ", Date())"

9 3112
32,554 Expert Mod 16PB
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 ;-)
Aug 10 '21 #2
212 Expert 128KB
You can simply use:

Expand|Select|Wrap|Line Numbers
  1. Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < DateAdd('yyyy', " & -MinimumAge & ", Date())"
Aug 11 '21 #3
32,554 Expert Mod 16PB
Or even simpler :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. Dim MinimumAge As Integer
  4. MinimumAge = 6
  5. strWhere = Replace("([NoteDate]<#%ED#)" _
  6.                  , "ED" _
  7.                  , Format(DateAdd("yyyy", -MinimumAge, Date()),"yyyy\-m\-d")
  8. With Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form
  9.     .Filter = strWhere
  10.     .FilterOn = True
  11. 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().
Aug 11 '21 #4
204 128KB
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
Expand|Select|Wrap|Line Numbers
  1.  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 ... )
Aug 12 '21 #5
32,554 Expert Mod 16PB
Anyway, I guess that means I should use
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "NoteDate < #" & EarlyDate & "#"
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 <=.
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.
(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.
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 :
Expand|Select|Wrap|Line Numbers
  1. strWhere = Format(DateAdd("yyyy", -MinimumAge, Date()),"yyyy\-m\-d")
  2. ...
  3.     .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.
Aug 13 '21 #6
204 128KB
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
Expand|Select|Wrap|Line Numbers
  1. DateAdd('yyyy', " & -MinimumAge & ", Date())
and in yours it's
Expand|Select|Wrap|Line Numbers
  1. "#" & 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 ...
Aug 13 '21 #7
32,554 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. DateAdd('yyyy', " & -MinimumAge & ", Date())
  2. versus
  3. #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.
Aug 14 '21 #8
204 128KB
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!
Aug 14 '21 #9
32,554 Expert Mod 16PB
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.
Aug 14 '21 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Zibi | last post by:
Sometimes I get error : "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. " If I look on SQL there is ok example with error - insert...
by: Robert Dell | last post by:
I have a problem comparing strings in an order form i'm writing. I want to give a running total at the bottom of the page and it appears to be working except it doesn't compare correctly (it...
by: marcokrechting | last post by:
Hi All, I have a rather complex math problem concerning dates. I will try to explain my problem. I have a table with the fields SUBJECT (text), DUE DATE (date) and CHECKED (yes/no). In this...
by: Duppypog | last post by:
I'm trying to compare a date stored in a database with today's date using an If statement, but it's not returning true. Example, value in database is 11/5/2003 with today being 11/6/2003. Can...
by: colincolehour | last post by:
I am new to Python and am working on my first program. I am trying to compare a date I found on a website to todays date. The problem I have is the website only shows 3 letter month name and the...
by: krishnakant Mane | last post by:
hello all. thanks for the help and for pointing me to the proper url for wxpython related issues. I am so happy that I now have a very easy gui library that can do practically every thing with...
by: cheryl | last post by:
I am using the PHP.MYSQL and Apache server application in developing my website. I have problem in comparing dates. Website has room reservation, the user will check first the room availability. The...
by: RiotZamber | last post by:
Hi, I'm new to DB2 so I apologize if this is a dumb question. :) I have this table with 2 columns col_date (varchar(8)) (eg. 20070813) (yyyyMmdd) col_time (varchar(6)) (eg. 1525) (HHmm) ...
by: sabel | last post by:
Hi I am a problem comparing DDC AND "DDC" , the if statement is not working. here is my javascript code var DDC=document.getElementById("<%= hvalue.ClientID %>").value; ...
by: Coolboy55 | last post by:
I have a general date field in a table. When a user opens a particular report, a form opens that asks them to enter a date (short date format) to filter the report by. I want the report to display...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.