My database contains the following field:
D_Stmt Month with formula monthmath(month1st(Date),1)
The title for my main report on this database contains
upper("accounting “)+” - "+upper(datepattern(«D_Stmt Month»,“MONTH YYYY”))
The part upper(datepattern(«D_Stmt Month»,"MONTH YYYY prints as expected when the database does not contain any calculations but when I apply the routine below it does print.
How can it be fixed?
Many thanks.
Why do you have a separate D_Stmt Month field? The problem is that this field will not be filled in for the summary records generated by your analysis.
I think the easiest fix would be to change the formula on the title to:
upper("accounting “)+” - "+upper(datepattern(monthmath(month1st(Date),1),“MONTH YYYY”))
If this is the only thing you are using the D_Stmt Month field for, you can just get rid of it.
Thanks for the prompt answer.
When I copy/paste your formula, I get the error message “Missing Right Parenthesis”.
When I add a right parenthesis at the end, I get ACCOUNTING “)+” – FEBRUARY 0 (the intended result is MARCH 2022 because the statement month is always one month later than the Date field.
I use the field D_Stmt Month for other reporting purposes.
Check for smart quotes as Jim’s reply had some smart quotes and some straight quotes. It matters.
Prior to the Outline Levels, you will need to propagate those dates into the summary records. After the Outline Levels, the summary records are the only visible records, and one of them will be active.
Instead of copy/paste, sneak up on this with baby steps. Just start with upper("accounting “)+” - " and get that far. Better yet - I’m all for simplicity where it can be had - why use upper() at all there? You know it’s going to be ACCOUNTING so why not have "ACCOUNTING - " + … and minimize potential syntax errors.
Next, just show month1st(Date) and make sure that it returns what you expect.
If that is good, then show your monthmath(month1st(Date),1) just to be sure it’s happy with the parameters. Finally, if all the parts look good, put the monthmath construction in the date pattern and check that. Note that once again, you don’t need UPPER. Your Pattern MONTH already does that for you. That’s one more set of parenthesis you can remove - one less opportunity to miscount (misconduct).
In playing with this in Formula Workshop (Last choice in Help Menu), I see this “Date” number can be confusing. What exactly is going in there. Do you mean Today(). Also, if it is a variable or field, what is supposed to be in it - a text date, a number representing a date? I’d pick a different name. You don’t want to get close to the names used for functions - there are too many opportunities for confusion.
I followed the steps I suggested above using Today() instead of Date, building the equation a little at a time in Formula Workshop, I got ACCOUNTING - APRIL 2022 without using any Upper functions.
My final formula was …
"ACCOUNTING - " +datepattern(monthmath(month1st(today()),1),“MONTH YYYY”)
One little hill to climb is … though the Result part of Formula Workshop can be enlarged, the formula window itself can’t be - at least not for me. So that long formula doesn’t show itself completely in the formula field. I think the formula field should be expandable. It’s too cumbersome to select/copy the formula after it produces the desired result.
For example, with the formula above, I can only see "ACCOUNTING - " in the window. I triple-click on that in an act of faith that the triple-click will select the rest that I can’t see. It works. But I’d feel better I could expand the formula field to see the whole shebang.
If you expand the window to the left or right, the formula field will get wider.
Thank you, Dave. Got it.
I still wish it could grow vertically - but these days we have pretty wide monitors. Once I moved the workshop window from the right side, where it appears by default, of my monitor to the left there was room to drag the window wider. And yes, now I see I could have moved the left edge and kept the window where it was. But I like the ability to sometimes separate the parts of the equation by a carriage return.
FYI, you can do that with any macOS window in any application. You can also drag on the top to expand/shrink vertically.
Sorry about that. Acutally, I think those various quotes were in the original formula pasted in by @fmfmptopanx . I just copied it an edited it, assuming it was correct.
Oooh, good point, I didn’t notice that he wasn’t grouping by the date.
Have been here before.
This is due to the fact that I am using a non-US keyboard.
Let this be a warning/reminder to those in the same situation.
I find this to be a useful comment for users on the novice side.
It has been helpful for me.
This has been the clincher.
Once I applied this and solved the smart quotes issue, Jim’s formula worked like a charm.
Much appreciated, as always, Dave.
I hope my comments to the help I have received in solving this matter are of value to people reading this post, especially non-advanced users, such as myself.
This forum continues to be an invaluable tool in using Panorama X.
