Display billing month based on start date and end date

[usm01]

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]

Try:

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

 

[usm01 via AccessMonster.com]

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).

 

[Douglas J. Steele]

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

(no e-mails, please!)

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).

 

[usm01 via 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") & "]")
[quoted text clipped - 11 lines]

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

yes

 

[usm01 via AccessMonster.com]

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

[quoted text clipped - 9 lines]
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"))))