Referencing a Report Field with VBA

[Lorlai]

I am trying to perform some calculations on a report. I can't seem to find the right syntax to reference the fields on the report. Can anyone help me out? Below is my code (with inconsistencies as I have been trying to figure out the correct syntax):

Option Compare Database
Dim ETChours As Integer
Public Sub Report_Open(Cancel As Integer)
userPeriod = Forms!Form1!PeriodTextBox.Value
userDate = CDate(userPeriod)
currentMonth = month(userDate)


Select Case currentMonth
Case 1:
    ETChours = Me![February 2013 Hours] + Me![March 2013 Hours] + Me![April 2013 Hours] + Me![May 2013 Hours] + Me![June 2013 Hours] + Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 2:
    ETChours = Me![March 2013 Hours] + Me![April 2013 Hours] + Me![May 2013 Hours] + Me![June 2013 Hours] + Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 3:
    ETChours = Me![April 2013 Hours] + Me![May 2013 Hours] + Me![June 2013 Hours] + Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 4:
    ETChours = Me![May 2013 Hours] + Me![June 2013 Hours] + Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 5:
    ETChours = Me![June 2013 Hours] + Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 6:
    ETChours = Me![July 2013 Hours] + Me![August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 7:
    ETChours = [August 2013 Hours] + Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 8:
    ETChours = Me![September 2013 Hours] + Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 9:
    ETChours = Me![October 2013 Hours] + Me![November 2013 Hours] + Me![December 2013 Hours]
Case 10:
    ETChours = Me![November 2013 Hours] + Me![December 2013 Hours]
Case 11:
    ETChours = December_2013_Hours
End Select

    ETCHoursLabel.Caption = ETChours
End Sub

Thank you for your help!

[June7]

Is this code in module behind the report?

Data does not appear normalized. Is this how the table is structured - a field for each month/year?

[Lorlai]

Yes, this is the code that appears when the report opens. And yes, the data is not normalized, but it is how the client wants the time/date to be entered. Unless there is a very quick and easy way to normalize the data, this is what I have to work with.

[June7]

So the client doesn't understand relational database design? Do they want to have to modify the table, query, form, report, code every month for new field? There is a limit of 255 fields - they would run out eventually. Or does this db have a limited life?

A UNION query can rearrange data into normalized structure. There is no wizard or designer for UNION, must type in SQL view. There is a limit of 50 lines.

SELECT "201302" AS YrMo, "February 2013" AS MonYear, [February 2013 Hours] AS MonthHours FROM tablename
UNION SELECT "201303", "March 2013", [March 2013 Hours] FROM tablename
UNION SELECT "201304", "April 2013", [April 2013 Hours] FROM tablename
...;

[Lorlai]

Thank you June7, I have run the UNION query and have the report pulling the normalized data. I am still uncertain how to reference the fields through vba (and am having a hard time figuring out the summation logic with the normalized data). Essentially, I am calculating the Estimate To Complete (ETC), which means based on the user input, the calculation will change. If the user enters January, then the ETC will be calculated based on the MonthHours from February to December. If the user enters November, then the ETC will be the MonthHours of December.

Any further help is greatly appreciated!

[June7]

Use the UNION query as RecordSource for the report. In report Footer have a textbox with expression: = Sum([MonthHours]).

Apply a filter to the records to include only those that are => the given criteria. This can be a parameter in the report's RecordSource that references a control on form. Have a combobox that offers choices in a drop list.

Report RecordSource
SELECT * FROM qryALL WHERE [YrMo] >= [Forms]!formname.comboboxname;

Combobox properties
RowSource: SELECT DISTINCT [YrMo], [MonYear] FROM qryALL;
ColumnCount: 2
ColumnWidths: 0";1.5"
BoundColumn: 1

Now no VBA required.

[Lorlai]

Thank you again June7, this is great information for taking in user input. However, the report will need to be generated based on user input on a form that begins multiple processes, including importing spreadsheets, executing queries, and generating multiple other reports. As such, the user should only provide one input: a date in the format of d/m/yyyy. This is why I am having to go about the vba route. Is there a method to conduct this through vba? Or a way to take the one time entered user variable on Form1 for the Report I am trying to create here?

Thank you!

[June7]

I believe all you want can be accomplished through VBA. Complicated but should be doable. Take one step at a time. Break it down and get one feature working before moving to next. Post questions as you encounter specific issues.

However, the d/m/yyyy date will probably be an issue. Access stores dates as m/d/yyyy. Review http://allenbrowne.com/ser-36.html

[Lorlai]

So to circle around back to my original problem: How can I reference a field on a report through vba?

[June7]

Referencing the field is easy. I really can't see anything wrong with your code. Except for the part about setting label caption. If you want this calculation for multiple records then setting a property won't work. The code will run once on open, set the property and that setting will reflect the same result for all records.

You could put all that code you have into a function behind the report like:

Function Calc ETCHours()
...
End Function

Then call the the function from textbox ControlSource:
=ETCHours()

[Lorlai]

All right, I am still having problems. I am now getting a type mis-match error in my function. I have been staring at this thing for days and cannot figure out what the problem is. I am pulling the fields from the non-normalized table (sorry June7, I can't figure out how to calculate what I need to do with the normalized data). The data type in the access field is Number (long Integer), and as you can see, I have declared the variable ETCHours as an Int. Any ideas as to why I am getting this type error?

Option Compare Database

Function ETChours() As Integer


userPeriod = Forms!Form1!PeriodTextBox.Value
userDate = CDate(userPeriod)
currentMonth = month(userDate)




            Select Case currentMonth

                Case 11:
                   'MsgBox "selecting case 11"
                   ETChours = [December_2013_Hours]
             End Select

End Function

[June7]

Try using the Me! qualifier just as you did in all the other expressions.

If you still have issues and want to provide db for analysis, follow instructions at bottom of my post.

[Lorlai]

Thank you again for your continuous help June7. Unfortunately, using the Me! qualifier will not work in a function, as the Me! indicates the current form. The function is an independent unit, and does not know what is being referenced.

I am posting again to try and get specific help with the vba code, with a focus on the type mismatch error that I am getting. The code errors on the ETCHours = [December_2013_Hours] line. I have done some trial and error work and know that everything works up until this point (the correct month is pulled, and the correct case statement is entered). I am not familiar enough with Access' type designations to know why I would get a type error when the variable I am trying to store is declared as an Integer and the table field is set to a Number (long integer) type. Does anyone know of a way around this?

[June7]

The function is not behind the report object?

I thought [December_2013_Hours] is a field in the report RecordSource.

There should be no conflict between the variable and the field. Something else is happening. Would have to analyse the db if you want to provide it. Follow instructions at bottom of my post.