Get Fortnight intervals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that has to be generated every 15th and 30/31st (last day) of
every month. Instead of entering dates each time unless the user insists on,
I want to fix the period of report based on today's date, i.e. if today is
between 16 and 30, the report period will be 1st to 15th (last fortnight) and
if today is between 31 and 15, then it is 16th to 31st (again, last
fortnight). Hope I am clear in stating my problem.

Presently, I'm using the following Link Criteria on the Preview/Print button.

Dim stReportSpan As String
stReportSpan = "MyDate Between Forms!MyForm!txtStartDate And
Forms!MyForm!txtEndDate

Please help.
 
try

If IsNull(Me!txtStartDate) Then
If Day(Date) > 15 Then
stReportSpan = "MyDate Between #" _
& DateSerial(Year(Date), Month(Date), 1) _
& "# And #" & DateSerial(Year(Date), Month(Date), 15) _
& "#"
Else
stReportSpan = "MyDate Between #" _
& DateSerial(Year(Date - 16), Month(Date - 16), 16) _
& "# And #" & DateSerial(Year(Date), Month(Date), 1) - 1 _
& "#"
End If
Else
stReportSpan = "MyDate Between #" & Me!txtStartDate _
& "# And #" & Me!txtEndDate & "#"
End If

the above syntax assumes that the date textboxes are on the same form where
the code is running, hence replacing the full form reference with the Me
keyword.

hth
 
That apparently seems to be working, though I have to test the results in my
application.

Thanks a lot.
 
Hi Sreedhar,

I'm confused. Do these reports cover

*fortnights (i.e. periods of two weeks, fourteen days)

*periods of 15 days (e.g. from 1st to 15th and 16th to 30th), or

*periods of 13 to 16 days (i.e. 1st to 15th and 16th to the last day of
the month, which can be the 28th, 29th, 30th, or 31st)?
 
you're welcome. note that i didn't address the possibility that a user might
fill in the start date, but leave the end date blank. you might want to
tweak the opening If statement to

If IsNull(Me!txtStartDate) or IsNull(Me!txtEndDate) Then

hth
 
Sorry for catching late on this string - If you are still following this
one, then:

Yes, the reports cover

*periods of 13 to 16 days (i.e. 1st to 15th and 16th to the last day of
the month, which can be the 28th, 29th, 30th, or 31st)
 
Back
Top