Weird Behaviour with Date Functions

[oakbridge]

I'm trying to set up a report with a field that will default to last month for a search. I'm using the following logic:

MonthName(Month(Today) - 1 & "/1/" & Year(Today))

and it keeps giving me June as the name of the month (testing with March as current month). When I change my system date to:

January, it gives me November

February gives me August

March gives me June

April gives me March (this month works!)

May gives me January

June gives me October

October gives me December

etc.

I must be missing a step in the conversion from numeric date values. When I pull out the monthname function, the date is still incorrect. For example in the current month of March I get 7/1/2006 as the result.

However if I pull out the -1 from the calculation (i.e. change MonthName(Month(Today) - 1 & "/1/" & Year(Today)) to MonthName(Month(Today) & "/1/" & Year(Today)) I get the correct value of the current month. But I want the name of last month. Yes I know I'll have to add logic when the current month is January.

Any suggestions?

[comment]

To start with, MonthName() takes a date as parameter. Month (Today) returns a number between 1 and 12, so MonthName ( Month ( Today ) ) will always return January (the month of the first 12 dates in FM calendar, Jan 1 - 12, 0001).

However, I am confused regarding your purpose. What is your desired result? If you want to search a DATE field, you should aim for a date, or a range of dates.

[oakbridge]

I have a layout being used for a report. I have two fields on this layout, one is a popup for month (search_req_invoice_month), the other a text field for year (search_req_invoice_year). The idea is that the user will select the month and year to search for records. I have defaulted to the current month by inserting MonthName(Month(Today) & "/1/" & Year(Today)) in my script. and Year(Today) for the year and the logic is working. I'm using the following:

Case(

search_req_invoice_month = "January", "1/1/" & search_req_invoice_year & "...1/31/" & search_req_invoice_year,

search_req_invoice_month = "February", "2/1/" & search_req_invoice_year & "...2/28/" & search_req_invoice_year,

search_req_invoice_month = "March", "3/1/" & search_req_invoice_year & "...3/31/" & search_req_invoice_year,

search_req_invoice_month = "April", "4/1/" & search_req_invoice_year & "...4/30/" & search_req_invoice_year,

search_req_invoice_month = "May", "5/1/" & search_req_invoice_year & "...5/31/" & search_req_invoice_year,

search_req_invoice_month = "June", "6/1/" & search_req_invoice_year & "...6/30/" & search_req_invoice_year,

search_req_invoice_month = "July", "7/1/" & search_req_invoice_year & "...7/31/" & search_req_invoice_year,

search_req_invoice_month = "August", "8/1/" & search_req_invoice_year & "...8/31/" & search_req_invoice_year,

search_req_invoice_month = "September", "9/1/" & search_req_invoice_year & "...9/30/" & search_req_invoice_year,

search_req_invoice_month = "October", "10/1/" & search_req_invoice_year & "...10/31/" & search_req_invoice_year,

search_req_invoice_month = "November", "11/1/" & search_req_invoice_year & "..11/30/" & search_req_invoice_year,

search_req_invoice_month = "December", "12/1/" & search_req_invoice_year & "...12/31/" & search_req_invoice_year

)

for my searching and all is working as long as I leave the default to the current month.

But most of the reporting will be for 'last month'. That's why I was trying to use the MonthName(Month(Today) - 1 & "/1/" & Year(Today)). Based on 'my' logic that should calculate out to the name of the current month minus one month. I can't use logic such as current date minus 30 days because of dates like any 31st of a month, March 1, etc. which wouldn't calculate properly.

I'd love to hear other suggestions.

Thanks,

[comment]

The idea is that the user will select the month and year to search for records.

Well, let's say the user makes these selections in global fields, gMonthName (text) and gYear (number). Then we could run a script that enters Find Mode and inserts the following calculation result into the searched Date field:

DateToText( Date(

Position( "janfebmaraprmayjunjulaugsepoctnovdec" , Left( gMonthName , 3 ) , 1 , 1 ) / 3 + 1 ,

1,

gYear ) )

& ".." &

DateToText( Date(

Position( "janfebmaraprmayjunjulaugsepoctnovdec" , Left( gMonthName , 3 ) , 1 , 1 ) / 3 + 2 ,

0,

gYear ) )

IIRC, in version 6 the searched Date field must be present on the layout - though it doesn't need to be enabled for entry.

[oakbridge]

Okay your logic for the search is more concise than mine, but that's not the problem I'm having. I simply want to provide a popup list for the global field that contains the months of the year, with the defaulted value equal to last month.

As I said, the logic I've used to get the default value in the global field is MonthName(Month(Today) & "/1/" & Year(Today)).

That works.

How can I change that to default to the previous month? We're in March right now, by default I want the popup to be set to February.

Edited March 13, 2006 by Guest

[comment]

Try:

MonthName( Date( Month( Status(CurrentDate) ) - 1 , 1 , Year( Status(CurrentDate) ) ) )

Or:

MonthName( Date( Month( Status(CurrentDate) ) , 0 , Year( Status(CurrentDate) ) ) )

This is the proper way to get a date into the MonthName() function parameter.

The way you have it coerces a text string into a date - thus the result is made to depend on the OS/file date format settings.

[Vaughan]

Firstly, stop using the Today function. It's NLA in FMP 7 and later, and was replaced in FMP 3 or 4 by the Status(CurrentDate)function. So it's WAY past it's use-by date.

You want last month? Start off getting the current month number and subtracting 1...

Month(Status(CurrentDate))-1

Then create a new date with this month (make it the first of the month so it doesn't get confused with different month lengths) and get the monthname from this new date:

MonthName(Date(Month(Status(CurrentDate))-1, 1, Year(Status(CurrentDate))))

Remember to make the function unstored if it is used in a calculation field.

[oakbridge]

Thank you both for that code, it worked perfectly.

And thank you to Vaughan for pointing out my improper use of outdated functions. Is there a reference guide somewhere that shows what is outdated and what the proper replacements are?

Hopefully I'll be moving this project to FM8 within the next few months but in the meantime I'm trying to make the best (and use the most efficient code) of what I've got.

Thanks again,

[Vaughan]

"Is there a reference guide somewhere that shows what is outdated and what the proper replacements are?"

Yes indeed: the migration guides.

http://www.filemaker.com/support/product/documentation.html