Months AND Current Date

[Lister]

Here at work they now run on a different month they everyone else in the world.
And it causes me no end of headaches trying to write new criteria for it.

Our months start on the 27th of any month and finish on the 26th of the following month.

Now we come to the IIF/TRUE/FALSE thing, which is sending me up the wall.

I want to test a Date to see if it is true. Date Field is [DateOut], this hold the date when a job was sent to a customer.
I don’t want to see any records that were sent out before the current month.

Day greater than 27 = (>27) But less Than 1 (>27 And < 1) Would need to be tested against, DateSerial(Year(Date()),Month(Date()),26)

[DateOut]> DateSerial(Year(Date()),Month(Date()),26)

But what today’s Date is <1 But >26
[DateOut] > DateSerial(Year(Date()),Month(Date())-1,26)

Then what if todays Date is <1 But > 26 and its January?
[DateOut] > DateSerial(Year(Date())-1,Month(Date())-1,26)

Then what if todays Date is >=27 But <1 and its December?
[DateOut] > DateSerial(Year(Date()),12,26)

I hope you get the idea.
I have been having a crack at this all day. And I think I'm on the verge of a stroke.

In a unbound field in a query I would like to use this [DateOut] > Iif (Day(Date())>27 And Month (Date())=12,…. Etc
Then Set the Criteria to <>False.

But I am getting stumped with all the IIF Statements.

Now am I on the right track?
Any ideas or help links would be great. I know that some of you our there are very adept at IIf statment within a query.

Think I need to step back, just to many trees and not enough forest at the moment I think.

 

[Jon K]

Since both the year and the month need to be adjusted depending on the date on which the query is run, it will be easier to write a public function than to put all the adjustments in one IIF expression.

I have put this function in a Module in the attached database:-

Public Function IsCurrentMonth(DateToTest, _
   Optional RetrievalDate) As Boolean
  
   If IsNull(DateToTest) Then
     IsCurrentMonth = False
     Exit Function
   End If
  
   Dim YY As Integer
   Dim MM As Integer
   Dim DD As Integer
   
   Dim YY1 As Integer
   Dim MM1 As Integer
   
   Dim YY2 As Integer
   Dim MM2 As Integer
   
   ' if no RetrievalDate is supplied,
   ' use today's date as RetrievalDate
   If IsNull(RetrievalDate) Or IsMissing(RetrievalDate) Then
    RetrievalDate = Date
   End If
   
   YY = Year(RetrievalDate)
   MM = Month(RetrievalDate)
   DD = Day(RetrievalDate)
   
   Select Case DD
     Case Is >= 27
       If MM < 12 Then
         MM1 = MM
         YY1 = YY
         
         MM2 = MM + 1
         YY2 = YY
       Else
         MM1 = MM
         YY1 = YY
         
         MM2 = 1
         YY2 = YY + 1
       End If
     Case Else
       If MM = 1 Then
         MM1 = 12
         YY1 = YY - 1
         
         MM2 = MM
         YY2 = YY
       Else
         MM1 = MM - 1
         YY1 = YY
         
         MM2 = MM
         YY2 = YY
       End If
      End Select
    
    IsCurrentMonth = DateToTest >= DateSerial(YY1, MM1, 27) _
             And DateToTest <= DateSerial(YY2, MM2, 26)
End Function

To make the code flexible, I used an optional argument in the function, so that we have the option of using the current date or suppling a retrieval date to the function.

I have included two queries in the database:-

"Query1 - base on the current date":
SELECT [tblData].[RecordNumber], [tblData].[DateOut],
Date() AS [Today Is]
FROM tblData
WHERE IsCurrentMonth([DateOut]);


"Query2 - supply a retrieval date":
SELECT [tblData].[RecordNumber], [tblData].[DateOut],
Nz([Enter a retrieval date],"Left blank, so the current date is used") AS [Retrieval date entered]
FROM tblData
WHERE IsCurrentMonth([DateOut], [Enter a retrieval date]);


The IsCurrentMonth() function is used in the Where Clause of the SQL Statements (i.e. the Criteria of the queries).

The [Today Is] and the [Retrieval date entered] fields in the Select Clauses are for illustration only and can be removed.

Hope it helps.


The database was saved from Access 2000.

 

[neileg]

I think you need a way of determining what is the first day of the current month and use this to test. So here's a way to determine the first day of the month that any date falls in.

If you subtract 27 days from date, then this will give you a date in the correct calendar month.
E.g. 12 Jan 04 is in the month beginning 27 Dec 03. So if you subtract 27 days from 12 Jan 04 you get 16 December 2003.
E.g. 29 Jan 04 is in the month beginning 27 Jan 04. Subtract 27 days and you get 2 Jan 04.
Then take this date and change the day value to 27 and you get the correct month start date.

I think this expression will do the trick but you might have to tweak it:
"27/" & Month(DateAdd("d",-27,[MyDate])) & "/" & Year(DateAdd("d",-27,[MyDate]))

Edit: Jon got there ahead of me, but I still like my approach!

 

[GGib7711]

This should also work, though it lacks the flexibility of Jon's function.

SELECT *
FROM [yourTableName]
WHERE format(DateAdd("d",-26,[DateOut]),"yyyymm")=format(DateAdd("d",-26,Date()),"yyyymm");

 

[Jon K]

GGib7711, your Format expression greatly simplifies my function:

Public Function IsCurrentMonth(DateToTest, _
   Optional RetrievalDate) As Boolean
   
   ' if no RetrievalDate is supplied,
   ' use the current date as RetrievalDate
   If IsNull(RetrievalDate) Or IsMissing(RetrievalDate) Then
     RetrievalDate = Date
   End If
   
   IsCurrentMonth = _
     Format(DateAdd("d", -26, DateToTest), "yyyymm") _
     = Format(DateAdd("d", -26, RetrievalDate), "yyyymm")
     
End Function

 

[Lister]

Holy S***
Thanks for all the help (just loged on)!
It will take me an hour to wade through all this, and see what I can use from it
But once again thanks for all the help

 

[Lister]

Total WOW.
Thanks so much Neil, Ggib and Jon. Just blown away, I never would have thought of that. Just so simple, I will have to tweek here and there as I am still unsure how the final app is going to work but that has to be the biggest hill I had to get over.
With this sorted it should be just formatting.

Thanks again guys, this was such a big help.