How to Sum a Text Field 2

[DaveMac]

How to Sum a text field? We have this existing app that has a SQL DB. I cannot add fields but there are unused ones. The problem is the field I want to use is a text 2 char field. I can put numbers in it but no matter what I try I cannot get access to do any summing.

I have tried Sum(Priority) in query

Adding Sum(Priority) to the group footer on a report

Adding a field that is Formatnum([Priority],0 ) and then doing the above again

Used isnum(Priority) and got #Error

Nothing worked all type mismatches

Anybody have a solution for this?

 

[SkipVought]

Hi,

Why would you SUM a field like Priority, which is a STATUS of sorts? Priority is not like Quantity, is it. You might want to know how many entities exist for a Priority.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

I hate it myself! The problem is it is an open Field. So when I do not "own" the App we are working with I have to try and create work-arounds.

So Priority was probably designed to take letters or numbers and such. I need to enter hours in there. And then Sum the # of hours.

On another note do you know how to not carry valuses in the detail? What is happening is I have a query that looks at say tbl_PO and it is related to tbl_PO_details. When I run a report if one of the PO_details records has say $25 in it and the other records do not have anything all detail records pick up $25.

Thanks for any help!

 

[PHV]

Have you tried Sum(Val((Priority)) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

PHV thanks that looked promising but Data Type Mismatch still

 

[SkipVought]

Exactly What Priority Values do you have in there?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

80
16
8
24
and lots of blanks that do not respond to IsNum and IsText functions even though it is a MS SQL DB and the linked tbl says Text for the property. I just get # error when I try and evaluate it.

 

[SkipVought]

What is your SQL, including the summing?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

And this ?
Sum(Val(Nz(Priority,0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

SELECT qry_assignments_localcpy.CallID, Sum(qry_assignments_localcpy.priority) AS SumOfPriority
FROM qry_assignments_localcpy
GROUP BY qry_assignments_localcpy.CallID
HAVING (((Sum(qry_assignments_localcpy.Priority))>"1"));

 

[DaveMac]

PHV sorry Data Type Mismatch still

 

[SkipVought]

???

HAVING (((Sum(qry_assignments_localcpy.Priority))>[red]"1"[/red]));

Compare a NUMBER to TEXT?

And where is PHV's code? Why are you not using it???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

Not sure if this helps but I did a VarType(Priority) and all records are an 8 whatever 8 is. I would hope Text

 

[PHV]

SELECT CallID, Sum(Val(Nz(priority,0))) AS SumOfPriority
FROM qry_assignments_localcpy
GROUP BY CallID
HAVING Sum(Val(Nz(priority,0)))>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

But you are CONVERTING the text to a NUMBER in order to sum.

So guess what happens when you compare the sum to TEXT???

Crazy!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

SkipVought and PHV you win!

PHV your stuff worked I was just using it wrong. I had it in a report so it was not right could be a million things. When SkipVought pointed out I did not have it in the SQL statement I moved it there and Boom. It did take the NZ to get it though.

Thanks!!!!!!!!!!!