summing a text field

[jeannier1975]

I have a text field called ActualLaborHours. I need to be able to sum the labor hours in a query can someone help me do this or direct me in the right direction?

[NeoPa]

You say your field [ActualLaborHours] is actually a Text field but don't explain why this is so. It seems a spectacularly obvious design error. Obviously, we sometimes have to deal with design problems when we have received them from other people, who themselves may be idiots.

So, let's answer the question. The first and most obvious thing to say is that, if it's within your power, get the design changed. Make it a numeric field and lose the complication.

If that isn't possible then you will need to use Sum() of Val() or similar. With very little, to no, background explanation we can only know you need a numeric result. That would be done with a field in your SQL similar to :

SELECT ...
     , Sum(Val([ActualLaborHours])) AS [SumLaborHours]
FROM   [...]

[jeannier1975]

That didnt work I need to sum ActualLabor hours. When I copy the records in excel i am able to convert the text to a number and i get the correct sum of that fiels but I need to be able to do that in access. I have attached the query.

SELECT [Maximo Report].WorkOrder, ([ActualStartDate]-[ReportedDate]) AS Expr1, [Maximo Report].[Estimated Labor Hours], [Maximo Report].WorkType, [Maximo Report].ActualStartDate, [Maximo Report].ActualLaborHours
FROM [Maximo Report]
WHERE (((([ActualStartDate]-[ReportedDate]))<3.5) AND (([Maximo Report].[Estimated Labor Hours])="00:00") AND (([Maximo Report].WorkType)="CM" Or ([Maximo Report].WorkType)="EM" Or ([Maximo Report].WorkType)="MMNRO" Or ([Maximo Report].WorkType)="MMROI" Or ([Maximo Report].WorkType)="PMCM" Or ([Maximo Report].WorkType)="PMINS" Or ([Maximo Report].WorkType)="PMOR" Or ([Maximo Report].WorkType)="PMPDM" Or ([Maximo Report].WorkType)="PMREG" Or ([Maximo Report].WorkType)="PMRT") AND (([Maximo Report].ActualStartDate)>=DateAdd("h",-1,[Enter the Start Date]) And ([Maximo Report].ActualStartDate)<DateAdd("h",23,[Enter the End Date]))) OR ((([Maximo Report].WorkType)="EM") AND (([Maximo Report].ActualStartDate)>=DateAdd("h",-1,[Enter the Start Date]) And ([Maximo Report].ActualStartDate)<DateAdd("h",23,[Enter the End Date])));

I need to Add the Total ActualLaborHours.

[NeoPa]

Jeannier1975:
That didnt work I need to sum ActualLabor hours.

What didn't? What did you try and where exactly did it go wrong?

If we're to help you then we need a little more involvement from you than "That didn't work".

As I mentioned in my first reply, you share very little information. Basic obvious stuff like what the circumstances are and what you're trying to do - even in general terms.

Summing is a fine concept, but it has very different results depending on what grouping you're using. Across the recordset as a whole is a very different result from just those within a particular group. I've seen nothing as yet to tell me what it is you're trying to achieve.

The solution provided earlier does work, of course, but you have to use it in accordance with whatever it is you expect. Sum() within a single record simply gives you the same value back. Even then you'd need to specify the grouping in the GROUP BY clause in order for it to work for you.

The other major problem is that whenever you do any aggregating within a query then the query expects everything to be aggregated, obviously. It couldn't possibly give you the aggregated results unless there is some understanding of grouping, and if there is such an understanding then individual values make no sense. What would be the name value of a grouped set of records encompassing multiple records each with a different name?

There are techniques that allow us to get around such restrictions. What I said before is still true but we can nevertheless link to separate queries that allow us to find an aggregated total that is linked into each individual record. That would have to be created and made available in a separate section of course.

As I say possible, but we'd need a far clearer explanation of what you're actually after if we're even to make a start in that direction. Hopefully it won't be necessary but from your posted SQL I would guess that's where we're heading. Not enough to be sure but the indications are there.

[PhilOfWalton]

This is just a repeat of https://bytes.com/topic/access/answe...sum-time-field

that I thought I had answered but got no response.

If ActualLaborHours is a text field coming from Excel, try using

CDate(ActualLaborHours)

instead.

Depending if you are using UK dates (DD/MM/YYYY) or US Dates (MM/DD/YYYY) you may get different answers

Phil

[NeoPa]

Technically it's different as the other isn't described as being held in text format. That may simply be the lack of attention and effort on behalf of the OP of course. Signs are not good as they seem unwilling or unable to do put in the basic level of effort to explain their own problem properly. So far at least. Maybe they're just struggling to understand. DB work can seem particularly complicated to a lot of people. We'll see what they come up with and hopefully we'll have a full and clear question we can simply provide an answer to.