|
 |
access thread: 1st and Last Date of the Month
Message #1 by George Oro <georgeoro@y...> on Tue, 26 Feb 2002 01:01:25 -0800 (PST)
|
|
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #2 by "David Chapman" <luckychap@b...> on Tue, 26 Feb 2002 20:57:21 +1030
|
|
LastDayOfMonth = Day(DateSerial(Year(TheDate), Month(TheDate) + 1, 0))
This will fall over when "TheDate" is not a date, or a string that can not
be converted to a date.
This may suit your further requirement of using a list box to select the
month. Replace "Month(The Date) with listbox.column(1) (where column one
contains the month numbers).
Ok if I let you work out how to get the first day of the month?
David
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Tuesday, February 26, 2002 7:31 PM
To: Access
Subject: [access] 1st and Last Date of the Month
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #3 by "Carnley, Dave" <dcarnley@a...> on Tue, 26 Feb 2002 09:21:00 -0600
|
|
This generates a string that gives the first of the current month in:
(Month(Date)) & "/1/" & IIf(Expression:=Month(Date) = 1,
Truepart:=Year(Date) - 1, Falsepart:=Year(Date)))
This generates a string that is the last day of the current month
(Month(Date)) _
& "/" _
& Day(DateAdd(Interval:="d", Number:=-1, Date:=CDate(Month(Date) & "/1/" &
Year(Date)))) _
& "/"
& IIf(Expression:=Month(Date) = 1, Truepart:=Year(Date) - 1,
Falsepart:=Year(Date)))
You can then convert these strings to dates...
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Tuesday, February 26, 2002 3:01 AM
To: Access
Subject: [access] 1st and Last Date of the Month
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #4 by "Gerald, Rand" <RGerald@u...> on Tue, 26 Feb 2002 12:03:43 -0600
|
|
The following reference has a really simple routine for Last Day Of Month.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
Here is a simple function for First Day of the Month:
Function FirstDayOfMonth(dtIn As Date) As Date
FirstDayOfMonth = DateSerial(Year(dtIn), Month(dtIn), 1)
End Function
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Tuesday, February 26, 2002 3:01 AM
To: Access
Subject: [access] 1st and Last Date of the Month
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #5 by "Ian Ashton" <ian@c...> on Tue, 26 Feb 2002 20:43:14 -0000
|
|
Hi All,
Following on from Rand's post:
Function LastDayOfMonth(dtIn As Date) As Date
LastDayOfMonth=DateSerial(Year(dtIn), Month(dtIn)+1, 0)
End Function
Ian Ashton
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Tuesday, February 26, 2002 6:04 PM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Importance: High
The following reference has a really simple routine for Last Day Of Month.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
Here is a simple function for First Day of the Month:
Function FirstDayOfMonth(dtIn As Date) As Date
FirstDayOfMonth = DateSerial(Year(dtIn), Month(dtIn), 1)
End Function
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Tuesday, February 26, 2002 3:01 AM
To: Access
Subject: [access] 1st and Last Date of the Month
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #6 by "Gerald, Rand" <RGerald@u...> on Tue, 26 Feb 2002 16:09:41 -0600
|
|
Ian,
I like it.
Nice algorithm!!! It is even more elegant than the routine on the Microsoft
support site, which was more elegant than a routine I created a few years
ago.
DateSerial(yy,mm,dd) doesn't error out when you feed it a month greater than
12, or a day less than 1.
I'm always on the lookout for better ways to do things!!!
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Tuesday, February 26, 2002 2:43 PM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Hi All,
Following on from Rand's post:
Function LastDayOfMonth(dtIn As Date) As Date
LastDayOfMonth=DateSerial(Year(dtIn), Month(dtIn)+1, 0)
End Function
Ian Ashton
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Tuesday, February 26, 2002 6:04 PM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Importance: High
The following reference has a really simple routine for Last Day Of Month.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
Here is a simple function for First Day of the Month:
Function FirstDayOfMonth(dtIn As Date) As Date
FirstDayOfMonth = DateSerial(Year(dtIn), Month(dtIn), 1)
End Function
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Tuesday, February 26, 2002 3:01 AM
To: Access
Subject: [access] 1st and Last Date of the Month
Hi Guys,
I have one form which the user will provide the Date
Range to generate Report. How can i assign the
following to my From/To Date Range Criteria as
defualt?
Date Range:
From: 1st Date of the Current Month
To: Last Date of the Current Month
THEN (if possible)
If i will have a Month drop down menu, the Date Range
Criteria will change according to the month I
selected.
Thanks and advance guys...
Cheers,
George
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
Message #7 by George Oro <georgeoro@y...> on Tue, 26 Feb 2002 22:24:05 -0800 (PST)
|
|
GUYS MANY THANKS FOR YOUR HELP... ALL OF YOU ARE
REALLY GREAT !!!!!!!
Cheers,
George
--- "Gerald, Rand" <RGerald@u...> wrote:
> Ian,
>
> I like it.
>
> Nice algorithm!!! It is even more elegant than the
> routine on the Microsoft
> support site, which was more elegant than a routine
> I created a few years
> ago.
>
> DateSerial(yy,mm,dd) doesn't error out when you feed
> it a month greater than
> 12, or a day less than 1.
>
> I'm always on the lookout for better ways to do
> things!!!
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: Ian Ashton [mailto:ian@c...]
> Sent: Tuesday, February 26, 2002 2:43 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
>
> Hi All,
>
> Following on from Rand's post:
>
> Function LastDayOfMonth(dtIn As Date) As Date
> LastDayOfMonth=DateSerial(Year(dtIn),
> Month(dtIn)+1, 0)
> End Function
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Gerald, Rand [mailto:RGerald@u...]
> Sent: Tuesday, February 26, 2002 6:04 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
> Importance: High
>
>
> The following reference has a really simple routine
> for Last Day Of Month.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
>
> Here is a simple function for First Day of the
> Month:
>
> Function FirstDayOfMonth(dtIn As Date) As Date
> FirstDayOfMonth = DateSerial(Year(dtIn),
> Month(dtIn), 1)
> End Function
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: George Oro [mailto:georgeoro@y...]
> Sent: Tuesday, February 26, 2002 3:01 AM
> To: Access
> Subject: [access] 1st and Last Date of the Month
>
> Hi Guys,
>
> I have one form which the user will provide the Date
> Range to generate Report. How can i assign the
> following to my From/To Date Range Criteria as
> defualt?
>
> Date Range:
> From: 1st Date of the Current Month
> To: Last Date of the Current Month
>
> THEN (if possible)
> If i will have a Month drop down menu, the Date
> Range
> Criteria will change according to the month I
> selected.
>
> Thanks and advance guys...
> Cheers,
> George
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
Message #8 by George Oro <georgeoro@y...> on Tue, 26 Feb 2002 23:01:36 -0800 (PST)
|
|
Ian/Rand's,
Guys it's working perfect, but one more question, how
can i get the CURRENT Month and Year as integer?
Thanks in advance,
George
--- Ian Ashton <ian@c...> wrote:
> Hi All,
>
> Following on from Rand's post:
>
> Function LastDayOfMonth(dtIn As Date) As Date
> LastDayOfMonth=DateSerial(Year(dtIn),
> Month(dtIn)+1, 0)
> End Function
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Gerald, Rand [mailto:RGerald@u...]
> Sent: Tuesday, February 26, 2002 6:04 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
> Importance: High
>
>
> The following reference has a really simple routine
> for Last Day Of Month.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
>
> Here is a simple function for First Day of the
> Month:
>
> Function FirstDayOfMonth(dtIn As Date) As Date
> FirstDayOfMonth = DateSerial(Year(dtIn),
> Month(dtIn), 1)
> End Function
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: George Oro [mailto:georgeoro@y...]
> Sent: Tuesday, February 26, 2002 3:01 AM
> To: Access
> Subject: [access] 1st and Last Date of the Month
>
> Hi Guys,
>
> I have one form which the user will provide the Date
> Range to generate Report. How can i assign the
> following to my From/To Date Range Criteria as
> defualt?
>
> Date Range:
> From: 1st Date of the Current Month
> To: Last Date of the Current Month
>
> THEN (if possible)
> If i will have a Month drop down menu, the Date
> Range
> Criteria will change according to the month I
> selected.
>
> Thanks and advance guys...
> Cheers,
> George
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
Message #9 by "Ian Ashton" <ian@c...> on Wed, 27 Feb 2002 11:14:29 -0000
|
|
George,
Current Month as integer: CInt(Format$(Date,"m"))
Current Year as integer: 1) CInt(Format$(Date,"yy"))
OR: 2) CInt(Format$(Date,"yyyy"))
Ian Ashton
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Wednesday, February 27, 2002 7:02 AM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Ian/Rand's,
Guys it's working perfect, but one more question, how
can i get the CURRENT Month and Year as integer?
Thanks in advance,
George
--- Ian Ashton <ian@c...> wrote:
> Hi All,
>
> Following on from Rand's post:
>
> Function LastDayOfMonth(dtIn As Date) As Date
> LastDayOfMonth=DateSerial(Year(dtIn),
> Month(dtIn)+1, 0)
> End Function
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Gerald, Rand [mailto:RGerald@u...]
> Sent: Tuesday, February 26, 2002 6:04 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
> Importance: High
>
>
> The following reference has a really simple routine
> for Last Day Of Month.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
>
> Here is a simple function for First Day of the
> Month:
>
> Function FirstDayOfMonth(dtIn As Date) As Date
> FirstDayOfMonth = DateSerial(Year(dtIn),
> Month(dtIn), 1)
> End Function
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: George Oro [mailto:georgeoro@y...]
> Sent: Tuesday, February 26, 2002 3:01 AM
> To: Access
> Subject: [access] 1st and Last Date of the Month
>
> Hi Guys,
>
> I have one form which the user will provide the Date
> Range to generate Report. How can i assign the
> following to my From/To Date Range Criteria as
> defualt?
>
> Date Range:
> From: 1st Date of the Current Month
> To: Last Date of the Current Month
>
> THEN (if possible)
> If i will have a Month drop down menu, the Date
> Range
> Criteria will change according to the month I
> selected.
>
> Thanks and advance guys...
> Cheers,
> George
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
Message #10 by "Gerald, Rand" <RGerald@u...> on Wed, 27 Feb 2002 09:30:02 -0600
|
|
George,
There are two functions:
Year(datevalue) and Month(datevalue) that take date values and return
integer values.
A third function
Now() returns the current date and time value.
Combining the three as follows will give you what you want.
Year(Now())
Month(Now())
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Wednesday, February 27, 2002 1:02 AM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Ian/Rand's,
Guys it's working perfect, but one more question, how
can i get the CURRENT Month and Year as integer?
Thanks in advance,
George
--- Ian Ashton <ian@c...> wrote:
> Hi All,
>
> Following on from Rand's post:
>
> Function LastDayOfMonth(dtIn As Date) As Date
> LastDayOfMonth=DateSerial(Year(dtIn),
> Month(dtIn)+1, 0)
> End Function
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Gerald, Rand [mailto:RGerald@u...]
> Sent: Tuesday, February 26, 2002 6:04 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
> Importance: High
>
>
> The following reference has a really simple routine
> for Last Day Of Month.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
>
> Here is a simple function for First Day of the
> Month:
>
> Function FirstDayOfMonth(dtIn As Date) As Date
> FirstDayOfMonth = DateSerial(Year(dtIn),
> Month(dtIn), 1)
> End Function
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: George Oro [mailto:georgeoro@y...]
> Sent: Tuesday, February 26, 2002 3:01 AM
> To: Access
> Subject: [access] 1st and Last Date of the Month
>
> Hi Guys,
>
> I have one form which the user will provide the Date
> Range to generate Report. How can i assign the
> following to my From/To Date Range Criteria as
> defualt?
>
> Date Range:
> From: 1st Date of the Current Month
> To: Last Date of the Current Month
>
> THEN (if possible)
> If i will have a Month drop down menu, the Date
> Range
> Criteria will change according to the month I
> selected.
>
> Thanks and advance guys...
> Cheers,
> George
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
Message #11 by "Gerald, Rand" <RGerald@u...> on Wed, 27 Feb 2002 09:43:55 -0600
|
|
George,
An alternate form is to use Date() instead of Now().
Year(Date())
Month(Date())
Ps There is also a Day() function which can be used with Date() or
Now() to yield GUESS WHAT!! The day of the month.
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Wednesday, February 27, 2002 9:30 AM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
George,
There are two functions:
Year(datevalue) and Month(datevalue) that take date values and return
integer values.
A third function
Now() returns the current date and time value.
Combining the three as follows will give you what you want.
Year(Now())
Month(Now())
Rand E Gerald
Information Services / Operations
-----Original Message-----
From: George Oro [mailto:georgeoro@y...]
Sent: Wednesday, February 27, 2002 1:02 AM
To: Access
Subject: [access] RE: 1st and Last Date of the Month
Ian/Rand's,
Guys it's working perfect, but one more question, how
can i get the CURRENT Month and Year as integer?
Thanks in advance,
George
--- Ian Ashton <ian@c...> wrote:
> Hi All,
>
> Following on from Rand's post:
>
> Function LastDayOfMonth(dtIn As Date) As Date
> LastDayOfMonth=DateSerial(Year(dtIn),
> Month(dtIn)+1, 0)
> End Function
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Gerald, Rand [mailto:RGerald@u...]
> Sent: Tuesday, February 26, 2002 6:04 PM
> To: Access
> Subject: [access] RE: 1st and Last Date of the Month
> Importance: High
>
>
> The following reference has a really simple routine
> for Last Day Of Month.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210493
>
> Here is a simple function for First Day of the
> Month:
>
> Function FirstDayOfMonth(dtIn As Date) As Date
> FirstDayOfMonth = DateSerial(Year(dtIn),
> Month(dtIn), 1)
> End Function
>
> Rand E Gerald
> Information Services / Operations
>
> -----Original Message-----
> From: George Oro [mailto:georgeoro@y...]
> Sent: Tuesday, February 26, 2002 3:01 AM
> To: Access
> Subject: [access] 1st and Last Date of the Month
>
> Hi Guys,
>
> I have one form which the user will provide the Date
> Range to generate Report. How can i assign the
> following to my From/To Date Range Criteria as
> defualt?
>
> Date Range:
> From: 1st Date of the Current Month
> To: Last Date of the Current Month
>
> THEN (if possible)
> If i will have a Month drop down menu, the Date
> Range
> Criteria will change according to the month I
> selected.
>
> Thanks and advance guys...
> Cheers,
> George
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> $subst('Email.Unsub').
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
|
|
|
View Cart
