Dates Confusion in VBA (1 Viewer)

Thicko

Registered User.
Local time
Today, 14:23
Joined
Oct 21, 2011
Messages
61
Hi All,

I had a bit of code that ran in the run up to Christmas.

Code:
If Date >= Format("01/12/" & Year(Date), "dd/mm/yyyy") And Date < Format("25/12/" & Year(Date), "dd/mm/yyyy") Then
This didn't work after the 1st December passed, the solution was

Code:
Format(Date, "dd/mm/yyyy") >= Format("01/12/" & Year(Date), "dd/mm/yyyy") And Format(Date, "dd/mm/yyyy") < Format("25/12/" & Year(Date), "dd/mm/yyyy") Then
Now we're in Jan this new bit of code doesn't work and the original does, I can't explain using the immediate window.

Code:
?Format(Date, "dd/mm/yyyy")
02/01/2020
?Date()
02/01/2020 
?Format(Date, "dd/mm/yyyy")>= Format("01/12/" & Year(Date), "dd/mm/yyyy")
True
?Date >= Format("01/12/" & Year(Date), "dd/mm/yyyy")
False
It's only so it changes some pictures to a Christmas theme so not major important but I'd like to get it working properly.

Many Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
As Grumm possibly was hinting, in VBA dates must be explicitly in mm/dd/yyyy format
As you want this in the run up to each Xmas, you can ignore the year.

Here's one approach
Code:
If DatePart("m", Date) = 12 And DatePart("d", Date) >= 1 And DatePart("d", Date) <= 25 Then

or possibly convert to strings
Code:
If Format(Date, "m/d") >= "12/1" And Format(Date, "m/d") <= "12/25" Then

I'd advise using the DatePart approach

NOTE: It appears that using Between doesn't work in either of the above
 

Thicko

Registered User.
Local time
Today, 14:23
Joined
Oct 21, 2011
Messages
61
Hi Grumm, That post solved the issue so many thanks.
 

Thicko

Registered User.
Local time
Today, 14:23
Joined
Oct 21, 2011
Messages
61
Hi isladogs,

That datepart seemed a neater solution and works well so thankyou. Can you give your thoughts on my next issue which is looking up if it's someones birthday where I only want to look up the day & month and ignore the year.

The sum of my failed attempts has so far been
Code:
If DLookup("FirstName","tblOperatorCode","[Birthday] Like 'Format(Day(Date()),"00") & "/" & Format(Month(Date()),"00") & "/*"' And [CurrentlyEmployed]=1") Then
This gives a compile error that I can't work out.

Many Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
You're welcome.

Actually the following is probably better than either of my previous versions:
Code:
If Month(Date) = 12 And Day(Date) >= 1 And Day(Date) <= 25 Then

Using the same idea and assuming the code is on a bound form with fields Birthday & CurrentlyEmployed then something like:
Code:
If Month(Birthday)=Month(Date()) And Day(Birthday)=Day(Date()) And CurrentlyEmployed = 1 Then
 

Thicko

Registered User.
Local time
Today, 14:23
Joined
Oct 21, 2011
Messages
61
All staff birthdays are in a table tblOperatorCode and not on the form so I need to lookup and see if their birthday day & month matches the current day & month.

Your solution with the DatePart got me thinking and I've now resolved it with:

Code:
If DLookup("FirstName", "tblOperatorCode", "DatePart('d',[Birthday])='" & DatePart("d", Date) & "' And DatePart('m',[Birthday])='" & DatePart("m", Date) & "' And [CurrentlyEmployed]=1") Then
Really appreciate the help.
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
Are you sure that code works as it seems to have several errors.

Even if it does work, its nor a good idea to use first name in this type of expression. What if more than one person have the same name?

I would suggest you add Birthday to the form record source. You don't need to display it on the form. But if you do that my original suggestion will work.
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:23
Joined
Aug 11, 2003
Messages
11,696
why format strings when you want to compare dates?

Mixing strings and dates makes for implicit conversion, which leads to problems.

instead of this mixed stuff
Code:
 If Date >= Format("01/12/" & Year(Date), "dd/mm/yyyy")
Worse yet, here you are building a string "01/12/" and then assuming it is a date to be formatted into "dd/mm/yyyy"


Try using a proper date on either side using Dateserial
Code:
 If Date >= Dateserial( year(date),12,1)

that should fix any issues you are having.
 

Thicko

Registered User.
Local time
Today, 14:23
Joined
Oct 21, 2011
Messages
61
I left out the If Not Isnull(

Code:
If Not IsNull(DLookup("FirstName", "tblOperatorCode", "DatePart('d',[Birthday])='" & DatePart("d", Date) & "' And DatePart('m',[Birthday])='" & DatePart("m", Date) & "' And [CurrentlyEmployed]=1")) Then

I admit it's not great code but it does seem to work and it's only to bring up a picture saying happy birthday other than anything mission critical.

Many Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
If it works for you that's great but I tested the earlier version and got multiple errors which is why I made the earlier comment

FWIW here's how I did something similar many years ago in one of my schools apps.
It uses a function CheckBirthday and another to get the first name of the logged in user

Code:
 If CheckBirthday = True Then
        MsgBox "Happy birthday " & GetLoggedOnTeacherForename() & ".     ", vbExclamation + vbSystemModal, "Birthday greetings"
    End If

Public Function CheckBirthday() As Boolean 
    CheckBirthday = IIf(Format(DLookup("DOB", "Teachers", "TeacherID = '" & GetLoggedOnTeacher() & "'"), "dd/mm") = Format(Date, "dd/mm"), True, False)
End Function

If I was writing it today, I would use DatePart or Month & Day rather than Format .... as in my earlier answers

Good luck with the rest of your project
 

Users who are viewing this thread

Top Bottom