Display billing month based on start date and end date

i have 3 textbox in a form with date type as date.
1- StartDate (bound to table)
2- EndDate (bound to table)
3- BillingMonth (unbound)

i want to display billing month based on start and end date according to
criteria that

if startdate is 1/1/09 (format dd/mm/yy) and enddate is 28/1/09 (format
dd/mm/yy) , billingmonth should display [Jan-2009]

if startdate is 25/11/08 (format dd/mm/yy) and enddate is 28/1/09 (format
dd/mm/yy) , billingmonth should display [Dec-2008 to Jan-2009]

i have not much vb knowledge but tried the following code as control source
of billingmonth and as usual it didnt worked.


=IIf(Not IsNull([StartDate] & [EndDate]) & Datepart("m",[StartDate]) =
Datepart("m",[Enddate] & Datepart("y",[StartDate])= Datepart("y",[Enddate]) -
Format(Datepart("m",[StartDate]),"mmm") & '-' & Format(Datepart("y",
[StartDate]),"yyyy") - Format(Datepart("m",[StartDate]),"mmm") & '-' & Format
(Datepart("y",[StartDate]),"yyyy") & 'to ' & Format(Datepart("m",[EndDate]),
"mmm") & '-' & Format(Datepart("y",[EndDate]),"yyyy")

any better solution.
Thanks in advance.

Try:

=IIf(Format([StartDate], "yyyymm") = Format([EndDate], "yyyymm"), "[" &
Format([StartDate], "mmm-yyyy") & "]", "[" & Format([StartDate], "mmm-yyyy")
& " to " & Format([EndDate], "mmm-yyyy") & "]")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"usm01" u56492@uwe wrote in message news:9fb0e4adc2d56@uwe...
i have 3 textbox in a form with date type as date.
1- StartDate (bound to table)
2- EndDate (bound to table)
3- BillingMonth (unbound)

i want to display billing month based on start and end date according to
criteria that

if startdate is 1/1/09 (format dd/mm/yy) and enddate is 28/1/09 (format
dd/mm/yy) , billingmonth should display [Jan-2009]

if startdate is 25/11/08 (format dd/mm/yy) and enddate is 28/1/09 (format
dd/mm/yy) , billingmonth should display [Dec-2008 to Jan-2009]

i have not much vb knowledge but tried the following code as control
source
of billingmonth and as usual it didnt worked.


=IIf(Not IsNull([StartDate] & [EndDate]) & Datepart("m",[StartDate]) =
Datepart("m",[Enddate] & Datepart("y",[StartDate])=
Datepart("y",[Enddate]) -
Format(Datepart("m",[StartDate]),"mmm") & '-' & Format(Datepart("y",
[StartDate]),"yyyy") - Format(Datepart("m",[StartDate]),"mmm") & '-' &
Format
(Datepart("y",[StartDate]),"yyyy") & 'to ' &
Format(Datepart("m",[EndDate]),
"mmm") & '-' & Format(Datepart("y",[EndDate]),"yyyy")

any better solution.
Thanks in advance.

Douglas J. Steele wrote:
Try:

=IIf(Format([StartDate], "yyyymm") = Format([EndDate], "yyyymm"), "[" &
Format([StartDate], "mmm-yyyy") & "]", "[" & Format([StartDate], "mmm-yyyy")
& " to " & Format([EndDate], "mmm-yyyy") & "]")

i have 3 textbox in a form with date type as date.
1- StartDate (bound to table)
[quoted text clipped - 26 lines]
any better solution.
Thanks in advance.


Thanks. It Worked.
But it ignores Null Field (i.e if Enddate is Null).

--
Message posted via http://www.accessmonster.com

You never mentioned that as a possibility in your original post...

What do you want if EndDate is Null: to use the current month?

=IIf(Format([StartDate], "yyyymm") = Format(Nz([EndDate], Date), "yyyymm"),
"[" & Format([StartDate], "mmm-yyyy") & "]", "[" & Format([StartDate],
"mmm-yyyy") & " to " & Format(Nz([EndDate], Date), "mmm-yyyy") & "]")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"usm01 via AccessMonster.com" u56492@uwe wrote in message
news:9fbc02509ae0c@uwe...
Douglas J. Steele wrote:
Try:

=IIf(Format([StartDate], "yyyymm") = Format([EndDate], "yyyymm"), "[" &
Format([StartDate], "mmm-yyyy") & "]", "[" & Format([StartDate],
"mmm-yyyy")
& " to " & Format([EndDate], "mmm-yyyy") & "]")

i have 3 textbox in a form with date type as date.
1- StartDate (bound to table)
[quoted text clipped - 26 lines]
any better solution.
Thanks in advance.


Thanks. It Worked.
But it ignores Null Field (i.e if Enddate is Null).

--
Message posted via http://www.accessmonster.com

Douglas J. Steele wrote:
You never mentioned that as a possibility in your original post...

What do you want if EndDate is Null: to use the current month?

=IIf(Format([StartDate], "yyyymm") = Format(Nz([EndDate], Date), "yyyymm"),
"[" & Format([StartDate], "mmm-yyyy") & "]", "[" & Format([StartDate],
"mmm-yyyy") & " to " & Format(Nz([EndDate], Date), "mmm-yyyy") & "]")

Try:

[quoted text clipped - 11 lines]
Thanks. It Worked.
But it ignores Null Field (i.e if Enddate is Null).

yes

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

usm01 wrote:
You never mentioned that as a possibility in your original post...

[quoted text clipped - 9 lines]
Thanks. It Worked.
But it ignores Null Field (i.e if Enddate is Null).

yes


i found another solution

=IIF (ISNULL(STARTDATE),NULL ,IIF ( ISNULL(ENDDATE),NULL ,IIF(FORMAT
(STARTDATE,"YYYYMM") = FORMAT(ENDDATE,"YYYYMM"),FORMAT(STARTDATE, "MMM-YY"),
FORMAT(STARTDATE, "MMM-YY to ") & FORMAT(ENDDATE,"MMM-YY"))))

--
Message posted via http://www.accessmonster.com