"PSKelligan" wrote ...

Public Function ReportPeriod() As Variant

'Instantiate (define) all variables
Dim cDay As Integer 'Current day (sys clock).
Dim cMonth As Integer 'Current month (sys clock).
Dim cYear As Integer 'Current year (sys clock).
Dim sDay As Integer 'Reporting period start day of month
(literal).
Dim sMonth As Integer 'Reporting period start month (variant).
Dim eDay As Integer 'Reporting period end day of month (literal).
Dim eMonth As Integer 'Reporting period end month (variant).
Dim sDate, eDate As Date 'Start and end of current reporting period
(variant).
'Initialize all variables
cDay = Format(Date, "dd") 'Sets day of the current date to an
interger.
cMonth = Format(Date, "mm") 'Sets month of the current date to an
interger.
cYear = Format(Date, "yyyy") 'Sets year of the current date to an
interger.
sDay = "16" 'Day of month that reporting period
starts.
eDay = "15" 'Day of month that reporting period ends.
'Generate start date based on the system clock.
If cDay < sDay Then
sMonth = (cMonth - 2)
Else
sMonth = (cMonth - 1)
End If
sDate = (sMonth & "/" & sDay & "/" & cYear)
'Generate end date based on system clock.
If cDay < sDay Then
eMonth = (cMonth - 1)
Else
eMonth = (cMonth)
End If
eDate = (eMonth & "/" & eDay & "/" & cYear)
End Function

I think you must have snipped something because you function as posted
has no return value.

However, I guess you require the following sql:


SELECT
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,2,1),16) AS
start_date,
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,1,0),15) AS
end_date
;

Jamie.

--