Errors on Report with formatted calculated fields (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 12:28
Joined
Dec 5, 2017
Messages
843
Hi All -

A legacy database that I've been "gifted" with managing is returning an error on two calculated fields when I open it.

Actually when I open the report, "Calibration Due Report," from the Main Menu, I am first presented with an "Enter Parameter Value" "Format$" msg box. I click ok and the report opens but has tow textboxes that read "#Error."

I am told by my co-worker that those two textboxes should state the Month and Year.
The field on the left side of the page has this expresion as its Control Source: =[Format$]([Recalibration Due Date],"mmm yyyy",0,0)
The field on the right side of the page has this as its Control Source: =[Format$]([Recalibration Due Date],"mmm yyyy")

I've attached the db - which has worked fine until recently.

It should open to "Main Menu." Click on "Calibration Due Report" in green at lower left of form.

Any thoughts are appreciated.

Thank you,

Tim
 

Attachments

  • CalibrationDBTEST.zip
    130.5 KB · Views: 285

Ranman256

Well-known member
Local time
Today, 12:28
Joined
Apr 9, 2015
Messages
4,339
the calculations use TEXT box names, not field names, BUT the textbox name usu is the field name. But check this.
Do all your calcs in the query, not the report. (tho some do need to be in the report)

math on a null field = null. Convert all nulls to 0 via NZ().
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:28
Joined
Sep 21, 2011
Messages
14,044
This is almost the same as your other thread??
I would not have thought Format should be surrounded by [] ?
 

Zydeceltico

Registered User.
Local time
Today, 12:28
Joined
Dec 5, 2017
Messages
843
This is almost the same as your other thread??
I would not have thought Format should be surrounded by [] ?
Yeah - it is a report closely related to issue in the other thread. After more conversation with my co-worker who has been many, many moons longer than I have and is much more familiar with what should be returned, I finally learned that wherever "=[Format$]([Recalibration Due Date]+[Frequency Number],"mmm yyyy")" is used as a control source, my co-worker expects to see just the Month and Year.

That means that the orginal developer was taking the Recalibration Due Date and adding a number of days to it (typically 182 for 6 months) and then formatting the result as Month and Year - - and I have no idea why this no longer works.

So - how would I make that calculation AND formatting within a query instead of directly in the textboxes as a control source the way the original was attempted?

With this SQL?:

Code:
SELECT [calibration device table].[frequency number],
       [calibration results table].id,
       [calibration results table].[device number],
       [calibration results table].[calibration date],
       [calibration results table].[recalibration due date],
FROM   [calibration device table]
       LEFT JOIN [calibration results table]
              ON [calibration device table].[device number] =
                 [calibration results table].[device number]
ORDER  BY [calibration results table].[calibration date] DESC;

What is the difference between Format and Format$?
 

Zydeceltico

Registered User.
Local time
Today, 12:28
Joined
Dec 5, 2017
Messages
843
This is almost the same as your other thread??
I would not have thought Format should be surrounded by [] ?
I just removed the brackets from around Format$ on every form and it works exactly as expected - which is great - BUT - I'm telling you that it worked for years just fine until a month or so ago and then glicthed and no one but me would have ever opened this db in design view let alone bracketed that one word.

Very weird.

But THANKS Gasman.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:28
Joined
Sep 21, 2011
Messages
14,044
Yeah - it is a report closely related to issue in the other thread. After more conversation with my co-worker who has been many, many moons longer than I have and is much more familiar with what should be returned, I finally learned that wherever "=[Format$]([Recalibration Due Date]+[Frequency Number],"mmm yyyy")" is used as a control source, my co-worker expects to see just the Month and Year.

That means that the orginal developer was taking the Recalibration Due Date and adding a number of days to it (typically 182 for 6 months) and then formatting the result as Month and Year - - and I have no idea why this no longer works.

So - how would I make that calculation AND formatting within a query instead of directly in the textboxes as a control source the way the original was attempted?

With this SQL?:

Code:
SELECT [calibration device table].[frequency number],
       [calibration results table].id,
       [calibration results table].[device number],
       [calibration results table].[calibration date],
       [calibration results table].[recalibration due date],
FROM   [calibration device table]
       LEFT JOIN [calibration results table]
              ON [calibration device table].[device number] =
                 [calibration results table].[device number]
ORDER  BY [calibration results table].[calibration date] DESC;

What is the difference between Format and Format$?
TBH I have no idea. I *think* the $ was used in the old days. I have only ever used Format() etc.
As to the calculation just use that expression in the query that has both those fields and give it a decent name. Then use that as the source.

I get Undefined Expression in 2007 if I try and use [Format$]. It is fine if I remove the [ & ]
 
Last edited:

Users who are viewing this thread

Top Bottom