Cng Text field to Numerical in CrossTab Report 1

[kentwoodjean]

I have a field that is formats as a text field 00:00:00 following Excel import. In Excel it is considered a general number, not particular format. In a previous post in Feb. (703-783067), dhookom assisted me with a cng of the text field to numerical for a basic report. The data I import has changed slightly and I was asked to create a crosstab now, and the info from my previous post doesn't work right.

I have a module like the following:
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

Before, I used the following to format my text box in the footer so I could average my column: =Avg(MakeMinutes(nz([Duration], "00:00:00"))). This does not work with my crosstab. Can I do someting similar? I"ve tried a few different ways and no luck. I want to be able to sum [TheValueuration] fields for each month and row into the [Total Of Duration: Duration] field for each row. Can that be done?

 

[dhookom]

You said "The data I import has changed slightly" but I don't see where you have told us what the current data looks like. Can you enter a variety of sample values for us to look at?

Normally, we shouldn't have to ask for this basic information.

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

 

[MichaelRed]

? format("06:15:30", "Short Time")
06:15

? format("06:15:30", "Long Time")
6:15:30 AM

? format("06:15:30", "Medium Time")
06:15 AM



MichaelRed
mlred@verizon.net

 

[kentwoodjean]

I am sorry for not enough info, or as it appears, I expressed myself incorrectly. Previously I was downloading from Crystal into excel and exporting to Access. I am now importing directly from Text files that have been made available to me. A few of the fields have changed amd a few more have been added, but the primary data field of Duration (00:00:00) remains the same. My table is changing somewhat, but the fact remains that I can only get the duration numbers to format correctly as Text. I will need to adjust some of my reports to incorporate additional information, but the request for the addition of a simple cross tab report has been made. Following are the Entries:

Column Heading: To Date (formatted as Jan, Feb, etc)
Row Heading: LastName
Row Heading: Reason
Value: The Valueuration
Row Heading: Total of Duration

Since it is text, the total of duration will not sum the totals from each month. This is what I would like to do.

 

[kentwoodjean]

Michael,

The time is not AM or PM, but stands for hours, minutes, and seconds. It comes in as text, and no matter how hard I try to make it a number on the table, it will not happen and yet retain the correct numbers.

 

[dhookom]

So, what is the SQL of the Crosstab that you have tried so far? Are any of the duration values Null?

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

 

[kentwoodjean]

Here is the SQL of which I am not too great at interpreting as yet:

TRANSFORM Last([IDW Monthly].Duration) AS [The Value]
SELECT [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, Last([IDW Monthly].Duration) AS [Total Of Duration]
FROM [IDW Monthly]
GROUP BY [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, [IDW Monthly].Team
PIVOT [IDW Monthly].ToDate;


There are about 12 different reasons that can be used each month. Some months a person may have time (duration) in each reason, other months the same person may only have time in some of them. Therefore, as far as getting a ytd total for each reason (Total of Duration) per person, there can be some null duration values. Both the first name and last name are in a header.

 

[MichaelRed]

? Cdate(Format("06:30:15"))
6:30:15 AM
? CDbl(CDate(Format("06:30:15")))
0.271006944444444
? Format(0.271006944444444, "Short Time")
06:30

? Format(0.271006944444444, "hh:mm:ss")
06:30:15



MichaelRed
mlred@verizon.net

 

[dhookom]

WHy are you using "Last([IDW Monthly].Duration)" when I thought you wanted to sum the value. I would have expected something like:
[tt][blue]
TRANSFORM Sum(MakeMinutes(nz([Duration], "00:00:00"))) AS [The Value]
SELECT [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, Sum(MakeMinutes(nz([Duration], "00:00:00"))) AS [Total Of Duration]
FROM [IDW Monthly]
GROUP BY [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, [IDW Monthly].Team
PIVOT [IDW Monthly].ToDate;
[/blue][/tt]

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

 

[kentwoodjean]

You are correct in that I did and do want the sum. Problem is that it was not an option while preparing my crosstab (used the make crosstab query option). I used your SQL and it did provide a different result, although I am not sure it is entirely accurate. Appears that everything is converting to Minutes and seconds. Let me show you what the comparison of results is:

My SQL result:

Reason Total 1/31/2004 2/29/2004
Meeting 01:01:09 00:18:09 01:01:09
No reason 00:16:41 00:11:03 00:11:36
Break Time 00:01:19 00:00:14 00:01:19
End of Work Day 01:09:43 00:09:58 00:14:49

Your SQL result:

Reason Total 1/31/2004 2/29/2004
Meeting 113.5833333 18.15 61.15
No reason 66.16666667 11.05 11.6
Break Time 1.55
End of Work Day 159.2166667 9.966666667 14.81666667

For sake of size, I only included Jan & Feb, although the total represents thru end of May. I still need the end result to appear in the format of 00:00:00. That is the dilima.

 

[dhookom]

MakeMinutes converts your text values to minutes. If you need to get back to a "time format" view, you will need to use a method depending on if your total will be greater than 24 hours. If less than 24 hours, just divide your total by 24 and display as a time.

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

 

[kentwoodjean]

Your suggestions have worked for me, and I understand converting to minutes. I just have one question, and perhaps you can help me understand this conversion process better. Below is how the actual data looks before the change in the conversion to minutes and the 2nd line is after the conversion. As you can see, the minutes and seconds are not exactly the same. I.e. in april, the actual was 13 min and 41 sec. Yet, when it converted, the result was 13 min and 68 sec. That seems to hold true for all of the changes. Is there a reason why this happens? Since these are results employees are held accountable for, I have to be sure of data integrety. Thanks.

Total Jan Feb Mar Apr

01:01:09 00:18:09 01:01:09 00:20:36 00:13:41

113.5833333 18.15 61.15 20.6 13.68333333

 

[dhookom]

You are seeing minutes with decimals. Do you see what happens if you multiply the decimal part times 60? Does it look familiar?

The sum of the function is doing exactly what it needs to do, convert you time to the number of minutes and parts of a minute. 13.5 is the same as 13 minutes and 30 seconds.

If you really must convert back to a time looking value with minutes and seconds, you might want to answer my question about more or less than 24 hours.

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

 

[kentwoodjean]

Thanks for the explanation. I was looking at the result as numbers in some cases rather than minutes. Sometimes you work on one little thing so long, you miss the more obvious points. To answer your question on the 24 hours, 95% of the time it will not exceed 24 hours. There is a small pocket of reps (about 8 of 120) who will exceed 24hrs, just by the nature of their work assignment.

 

[dhookom]

If any of the values will be over 24 hours, you will need either a function or an expression to convert the decimal display back to hours, minutes, and seconds. A simple Format() function call will not work with more than 24 hours.

Have you tried to create (or search google for) a function or do you want some suggestions to get you started?

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

 

[kentwoodjean]

I have been working with a few different functions, and did do a search for them. I think I can get started and will do so tongiht, but I do appreaciate your offer. This post has been helpful in shedding some light on yet another chapter of my Access learnings. I always end up knowing a bit more than when I started.

I do have another report question separate from this, that I will post separately in the event someone else can learn from it. Thanks again for all your help.

 

[dhookom]

Kentwoodjean,
I thought you might like to try some coding on your own. There are no secrets other than to have a good understand of how you would accomplish something manually and then trying to structure this in code.

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