Better and Simpler Way?? (1 Viewer)

gmatriix · Jul 3, 2018

Hey Guys,

I have a query the takes the current month and filters the criteria to give me the last day of that month and the last day of that same month one year ago.

So it looks like (for instance)
give me everything Between 20170731 and 20180731

So it looks like this in the query criteria

Code:

Between Format(DateAdd("yyyy",-1,DateSerial(Year(Date()),Month(Date())+1,0)),"yyyymmdd") And Format(DateSerial(Year(Date()),Month(Date())+1,0),"yyyymmdd")

Is there a cleaner and faster way to write this? It works but it seems like a mess. (and slower as well)

Any Ideas?

CJ_London · Jul 3, 2018

what is the field type you are applying the criteria to? if a date type, you don't need the format - and you can use this to build the date

between dateadd("d",-day(date()),dateadd("m",-11,date())) and dateadd("d",-day(date()),dateadd("m",1,date()))

MajP · Jul 3, 2018

If you want to make it cleaner and reuseable you can build UDFs.

Code:

Public Function LastDay(dtmDate As Date) As Date
    LastDay = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function
Public Function LastDayPrevious(dtmDate As Date) As Date
    LastDayPrevious = DateSerial(Year(dtmDate) + 1, Month(dtmDate) + 1, 0)
End Function

Then you sql is easier to write
Between lastDay(Date()) And LastDayPrevious(Date())

This is unlikely to be faster. UDFs may be cleaner but usually inline functions are faster.

gmatriix · Jul 3, 2018

Well the date is goofy....instead of 07/31/17 it is 20170731....I was trying to compensate for that

oh...btw it is a short text field...

MajP · Jul 3, 2018

I am sure everyone is going to tell you dates should always be stored as dates. If you had to you could put wrappers on the functions

Code:

Public Function FormatLastDay(dtmDate As Date, Optional TheFormat As String = "YYYYMMDD") As String
  FormatLastDay = Format(LastDay(dtmDate), TheFormat)
End Function
Public Function FormatLastDayPrevious(dtmDate As Date, Optional TheFormat As String = "YYYYMMDD") As String
  FormatLastDayPrevious = Format(LastDayPrevious(dtmDate), TheFormat)
End Function

MajP · Jul 3, 2018

Then your sql would be
Between FormatlastDay(Date()) And FormatLastDayPrevious(Date())

gmatriix · Jul 3, 2018

Yeah I agree....I don't know why they did it that way.....I know I could change it on my end without changing the format at the source but having had time to do that yet....

When I get some downtime (yeah right) I planned on making thing easier for myself (new job,thrown in the deep in, swimming to the top..without drowning..lol)

Thanks Everyone!!!!

MajP · Jul 3, 2018

If you cannot change the source. The easier approach is in the query change the string to a date then you can do date like queries and calculations.

Code:

Public Function CDate2(strDate As Variant) As Variant
  If Not IsNull(strDate) Then
    CDate2 = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
  End If
End Function

Then you could do something like this

Code:

SELECT StrDate, cdate2([strDate]) AS realDate
FROM tblDates
WHERE cdate2([strDate]))=Date()

That way you are working with dates instead of strings.

Pat Hartman · Jul 4, 2018

If the date were a datetime data type as it should be, the expression would be:

Between yourdate and dateadd("yyyy", -1, yourdate)

The only month that changes end dates is February and when you use proper date functions, this is correctly handled.

However, rather than do this in the query, I usually have a from and to date on the form that runs the report, query, etc and that allows complete flexibility regarding the range.

MajP · Jul 4, 2018

If the date were a datetime data type as it should be, the expression would be:
Between yourdate and dateadd("yyyy", -1, yourdate)
)

No, it would not. The OP said the last day of the month, not the current date.

Pat Hartman · Jul 7, 2018

"yourdate" = some variable.

Better and Simpler Way?? (1 Viewer)

gmatriix

Registered User.

CJ_London

Super Moderator

MajP

You've got your good things, and you've got mine.

gmatriix

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

gmatriix

Registered User.

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread