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") & "]")

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

"usm01" <u56492@uwe> wrote in message news:9fb0e4adc2d56@uwe...

[usm01 via AccessMonster.com]

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]

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

[usm01 via 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/Forums.aspx/access-forms/200911/1

[usm01 via AccessMonster.com]

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