Using Calculated Data in a Report

[MNM]

Gretings,

My employer is using Windows XP Pro and Office 2003 (a few machines have Office 2010, but not mine). Furthermore, the machines are running the Japanese language OS, which has caused some compatability issues with my English XP/Office 2003 at home.

I have a form containing an unbound textbox, with the name MIS.
The form's Current event has the following code:

If IsNull([[ResignationDate]) Then
  MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
ElseIf [ResignationDate] > Date Then
  MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
Else
  If Month([ResignationDate]) = Month([NichiiGakkanStart]) Then
    If Day([NichiiGakkanStart]) < Day([ResignationDate]) Then
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate]) + Int(Format([ResignationDate], "dd") < Format

([NichiiGakkanStart], "dd"))
    Else
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
    End If
  Else
    MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
  End If
End If

The calculates (correctly) the Months in Service of the employee who's information is being viewed.

Now, I am trying to create a report which lists the employees by work locations. The above , and other calculated information, is to be displayed in the report.
I used the wizard to create the report, using data from two different tables (employee & location).
I need to display the calculated information above for every employee at every location.
Example:

"Work Location"
"Employee Number" "Given Name" "Family Name" "Months in Service"

The report, as it is now, displays all work locations and the above employee info except the calculated data.

I've read up on using calculated fields in tables, and I'd prefer not to take this approach.
How can I use the above code with this report?

TIA,
MNM

 

[JHB]

Can't you put the calculation into the report's format event?

 

[MNM]

Can't you put the calculation into the report's format event?

JHB,
Thanks for the suggestion.
The original report was built with the wizard which set up the SQL coding with the fields from both tables.

To follow the route you laid out, I scrapped this and created a Query with all relevant fields from both tables.

I pointed to this Query in the Report wizard and inserted the code in the header. Looking at the screen shot, Header Format Event.jpg, the first line of code turns red.

The relevant control, [ResignationDate], is set to hide, see the lower left in Report in Design View.jpg.

When the report is displayed in Print Preview, the code errors out, see Header Format Event A.jpg.

Removing the code from the event allows the report to generate, see Report in Print Preview.jpg

Is it at all possible to accomplish what I'm attempting?

Ant guidance is appreciated.
MNM

 

[JHB]

JHB,
...
I pointed to this Query in the Report wizard and inserted the code in the header. Looking at the screen shot, Header Format Event.jpg, the first line of code turns red.
...

Yes because it is a error in it, take a closer look!
Do you see two [[ it is one to much.

 

[MNM]

Yes because it is a error in it, take a closer look!
Do you see two [[ it is one to much.

JHB,
Thanks for the catch.
I'll take a look at it on Monday; it's a 3-day weekend here.
I'll keep you posted.
MNM

 

[MNM]

Yes because it is a error in it, take a closer look!
Do you see two [[ it is one to much.

JHB,
That's it!
Thanks!
MNM