need help with form coding for current month

G

gcapp

Can someone help?

I have this code that I am trying to use in a form to have the for
display the current month from the 1st day to the last - the problem i
that is does not display the current setting correctly.

----------------------------------------------------------------
Private Sub cmdmonth_Click()
'Sets the Date From and Date To text boxes
'to show complete month (from start to end of current month)

Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))
Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))
----------------------------------------------------------------------

When I click the Month button in the form the results displayed are:
Date From: 1/5/2006
Date To: 2/4/2006

When it should display:

Date From: 5/1/2006
Date To: 5/31/2006


Can someone help me adjust the code above?

Thank
 
B

Brendan Reynolds

Private Sub cmdTest_Click()

Me.txtDateFrom = DateSerial(Year(Date), Month(Date), 1)
Me.txtDateTo = DateSerial(Year(Date), Month(Date) + 1, 0)

End Sub

The '0' above may seem strange, but the DateSerial function treats day '0'
of any month as the last day of the previous month, so day '0' of next month
gets us the last day of the current month, without having to worry about how
many days there are in the month.
 
R

Ron2006

The difficulty with the original code comes mostly from the following
(from help on dateadd):

Note The format of the return value for DateAdd is determined by
Control Panel settings, not by the format that is passed in date
argument.

Note For date, if the Calendar property setting is Gregorian, the
supplied date must be Gregorian. If the calendar is Hijri, the supplied
date must be Hijri. If month values are names, the name must be
consistent with the current Calendar property setting. To minimize the
possibility of month names conflicting with the current Calendar
property setting, enter numeric month values (Short Date format).

In general whenever dealing with date functions it is safer to ALWAYS
use mm/dd/yyyy format. If you search on date functions in the group you
will eventually find a white paper that describs the constraints for
date functions in access. If you always use the mdy format you will
almost never have problems.
 
Top