Display billing month based on start date and end date (1 Viewer)

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

 

[usm01]

Plz Help.

 

[namliam]

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

The probable reason you didnt get a response yet is because you are just splashing code on the forum to which you have obviously done
1) No research
2) No attempt at making it readable

If you would research IIF you sill fine it needs 3 parts:
IIF (SomeField = "SomeValue", Do something if true, do something if false)
You are not using this syntax, thus either dont use IIF or do use it but properly.

If you were to research DatePart and or Format you would find that you are totaly misusing the combination of the functions. Datepart("Y"...) will fetch the DAY OF THE YEAR (330 for today) instead of the year (2009), which Datepart ("YYYY"....) Year(...) or format(...,"YYYY") will do. Doubling them up, IMHO dont make any sense.

What is more you are mixing up "-" and "'-'" (double quotes are mine) and not even attempting to close brackets properly!

Normaly I would leave it at this to let you go RTFH... But you caught me in a good mood... this will ( i think, but untested) do what you want it to or at the very least be much closer....

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

Good luck !

 

[usm01]

Thanks for the hint but i found more appropriate solution

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

 

[namliam]

Thanks for the hint but i found more appropriate solution

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

How is that "more appropriate" ??? NONSENSE

1) It doesnt explicitely deal with Null values... which could cause problems
2) IT IS EXACTLY THE SAME, but without 1)
3) IT IS EXACTLY THE SAME

I am glad you found the solution, to bad though that you appearently still dont fully understand the IIF or 'your' solution or my suggestions

 

[usm01]

I know that.
According to your hint, i tried the following code

If Not IsNull([StartDate]) Then
If Not IsNull([EndDate]) Then
If (Format(StartDate, "YYMM") = Format(EndDate, "YYMM")) Then
Me.BillingMonth = Format(StartDate, "MMM-YY")
Else
Me.BillingMonth = Format(StartDate, "MMM-YY to ") & Format(EndDate, "MMM-YY")
End If
End If
End If


It Worked.
but i had to use it Afterupdate Event of startDate and EndDate and did not suite my requirement. Rather it gave me problems such as

1)- As billingmonth was unbound textbox so i had to use the above code on Form Load
event also which did not showed previous value when i clicked new record button
(no custom new button).
2)- since i was using calender button to input StartDate and Enddate,i also had to use
the above code on Lost Focus event also.
3)- As i was not Storing the billingmonth value and wanted to print in report also, i could
not make the code work as expression in query.

I was requiring simple solution as CONTROl SOURCE of billingmonth not one which i had to use again and again at different events.

I dont have much Vb Knowledge.
If you have a better solution then i will be thankfull if u can help me.
(criticizing is easy but helping others is difficult).

 

[namliam]

I dont have much Vb Knowledge.
If you have a better solution then i will be thankfull if u can help me.
(criticizing is easy but helping others is difficult).

Dude, I help others on this site/forum and elsewhere on A DAILY BASIS!
Perhaps what is difficult is READING A REACTION....

Where in my "hint" does it say "hint" ? No wheres,
Where in my "hint" do I use If... then ... else?? no wheres....
Where in my "hint" do I use VBA??? NO WHERES!

Now that I have established that the problem is your understanding of my post / "hint" I suggest you go back and read it AGAIN to find I used IIF, exactly like you did! for your "more appropriate solution" ...
The IIF to be used in a "CONTROl SOURCE" for your "requiring simple solution as CONTROl SOURCE of billingmonth"

If you want to critique my posts, sure I aint perfect
If you want to critique my helping others, sure I can make mistakes
If you want to critique my post on helping YOU, forget it!

And no this isnt about getting credit, I could care less... But dude, be serious here!

 

[usm01]

OK i tried your SUGGESTION
Bingo it handles Nulls.
You are great.


Sorry my mistake, i didnt mean to hurt your feelings.
thanks for helping and making me realize.

have a Nice day.

 

[smig]

USM you did exactly what namliam suggested but you only forgot to make sure both startdate and enddate are filled.
in your form make sure these boxes are filed as legal dates.