Case ( YourDate >= StartDate AND YourDate <= EndDate; " do something" )
Thanks for your prompt reply.
Something is wrong and I don't know what. I've broken the calculation down into smaller bits to try and work out the problem.
Here is a couple of broken down versions of the part of the calculation that doesn't seem to work.
If (Inv Paid > 30/6/2009; "Q1 2009 G1") will return the text string if ANY date (before or after 30/6/2009) is in the Inv Paid field
If (Inv Paid < 30/6/2009; "Q1 2009 G1") will only return the text string if the field is blank.
I'm confused. Any ideas?
Something is wrong and I don't know what.
30/6/2009 is not a date - it is 30 divided by 6, divided by 2009. Use Date ( 6 ; 30 ; 2009 ) to express a date.
Even better, do not hard-code any specific data in a calculation formula at all.
So much to know.
Thanks, that works great.
How would you recommend that I return a code based on the calendar quarter that the date falls in, without hard-coding a specific date into the formula?
Are you using regular calendrical quarters, or do you have a fiscal year that begins on date other than Jan 1?
Also, what is your purpose here: do you need just to display a label, or will you use this for grouping records in a report, or what?
The quarters are july-Sep, Oct-Dec, Jan-Mar, Apr-June (July-Sep being the first quarter).
I'm generating a code based on the date and other information on the form to group records for display in a portal in another table.
I would suggest your "code" be simply the date of the first day of the quarter =
Date ( 3 * Div ( Month ( Inv Paid ) - 1 ; 3 ) + 1 ; 1 ; Year ( Inv Paid ) )
I have almost no idea why that works, but it does.
I'll spend some time with it and try and get my head around it.
Thanks very much!!