Text Field and Report Calculation 1

[kentwoodjean]

I have a table that receives data imported from a text file. One of the fields is shown in hours, minutes and seconds (I.e. 34:22:07). I cannot import it as time (hh:nn:ss) as it will not format correctly and I do not want the hours to roll over to a day. Therefore, it is a general number in CSV and only formats correctly if imported as text.

The report output is all numbers, and there is a line that averages each column. Typically, if my colum were numbers, I would format as:

=Sum([IWR NRwkly.Duration])/Count([IWR NRwkly.Duration])

Because it is a text format, it will not average. Is there anyway I can somehow make this work?

 

[dhookom]

You can convert the text field to the number of minutes using a user-defined function like:

Function MakeMinutes(pstrText As String) As Double
    Dim intHrs As Integer
    Dim intMin As Integer
    Dim intSec As Integer
    intHrs = Val(Left(pstrText, InStr(pstrText, ":") - 1))
    intMin = Val(Mid(pstrText, InStr(pstrText, ":") + 1, 2))
    intSec = Val(Right(pstrText, 2))
    MakeMinutes = intHrs * 60 + intMin + intSec / 60
End Function

This assumes there are no nulls in the field. You can use a control source in a report of:
=Avg(MakeMinutes(Duration))
The result is in minutes. To get hours, divide by 60.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

I had no problem with the function, even though I am not very good at writing code as yet. What I did was open the report in Design mode, hit the Function Icon from the tool bar and entered the code.

What I did for the line that I want to show for the average is create a text box and entered the following in its property control source:

=Avg(MakeMinutes(IWR NRwkly.Duration))

The end result is an error which reads:

"The expression you entered contains an invalid syntax. You may have entered an operand without an operator."

What might I be doing wrong? I do have to use the format IWR NRwkly.Duration since there is another field using the name Duration from another query (IWR Fatwkly.Duration)

 

[EmptyMind]

Try:
=Avg(MakeMinutes([IWR NRwkly].Duration))

You may need to have the field name in square brackets because of the space.

Also, if there are any null values you can convert them using the nz() function since the original format is text.

ie:
nz([IWR Fatwkly].Duration, "00:00:00&quot

which gives:
=Avg(MakeMinutes(nz([IWR Fatwkly].Duration, "00:00:00&quot))

If there is a null value it will get converted to "00:00:00" and then your custom made 'MakeMinutes' function will do the rest.

Try not to use spaces in your field names...

 

[dhookom]

In addition, the table name should not be included in the expression. This assumes there is only one Duration field in your report's record source. There should never be duplicate names in a record source. Also, check the text box name to make sure it isn't the same as a field name.

EmptyMInd has some very good points.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

dhookom and emptymind......

Thanks for your responses. I won't have time to try them but I intend to do so at work tomorrow and will let you know.

 

[kentwoodjean]

Well, I am getting closer. As indicated, I have set up a text box for my average line and inserted:

=Avg(MakeMinutes(nz([IWR Fatwkly].Duration, "00:00:00&quot))

Now I am getting an error that says:

"Undefined function 'Make Minutes' in expression". The help key states: You entered an SQL expression that includes a Function procedure name that can't be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.

Considering my earlier posts on this topic, did I handle entering the expression correctly? If I click on the expression icon on the toolbar, I see my expression. Should it also be showing on the properties page? It is listed on the code page as "General" and labaeled "MakeMinutes".

 

[dhookom]

Where did you create the function? It should be in a standard module and make sure the name of the module is not MakeMinutes. Again, if you are using this in a group or report footer in a report, you should not include the table name. Try:
=Avg(MakeMinutes(nz([Duration], "00:00:00&quot))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

dhookom
Thanks so much for your patience and help. It works perfectly now. As you indicated, I made it a standard mod and I removed my table name out of the group as well as created an expression in the query to correct the problem of duplication of field name as a result of my table import. Its always a learning experience when I come to the forum.

 

[dhookom]

Glad to hear it works.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.