First to Last Day of Any Given Month

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

I want to create a query that will allow a user (who has
access to my database only through a form) to run a set
of records for people who applied ("application date"
field) on the first day of the month through the last day
of the month - for any given month. I would seem that I
will need to use a variable of some sort so that the user
can run it, in any given month, from a "run query"
command button on the form and come up with the records
for that particular month. Any help with this will be
greatly appreciated.
 
Kathy,

create a module called modDateFunctions or whatever

paste the module below and use the function in a query to
call it:

Function DaysInMonth(dteInput As Date) As String

'open debug window type in ?daysinmonth("7-12-03")
'will return
'31 Days in Month
'
' in a query use like: DaysIn: DaysinMonth
([somedate])
'

Dim intDays As Integer, strMo, strYr

intDays = DateSerial(Year(dteInput), Month(dteInput)
+ 1, Day(dteInput)) - DateSerial(Year(dteInput), Month
(dteInput), Day(dteInput))

DaysInMonth = intDays

End Function
 
Under your Application Date column, enter the following criteria:

Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,1)
 
I would have your user input a date in the month desired on the form, so you can
determine the year as well as the month.

The criteria in the query could be

WHERE SomeDateField Between
DateSerial(Year(Forms!FormName!ControlName),Month(Forms!FormName!ControlName),1)
AND
DateSerial(Year(Forms!FormName!ControlName),Month(Forms!FormName!ControlName)+1,0)

Alternatively you could have them use two textboxes and put in a month number
and year number(4 digit) and then use those in the DateSerial function.
 
Thanks so much for timely responses from brentt, John and
Paul. I began with Paul's suggestion and it did the
trick - worked out perfectly.
 
Back
Top