Can CInt() be summed?

[Starlitnight]

Hi,
I have a query which pulls the number of people we serve by converting text to a integer via Cint(). The original value was #Served:23. The numbers after the colon were extracted and converted via the Cint() function. Now I am to create a report that contains a sum of that field.
Here is the expression used to create my field. TrueNumberServed: CInt([NumberServed]). When I try to sum this, I encounter a "data type mismatch in criteria expression' error...
Any ideas on how I can sum this?

 

[snyperx3]

Does "NumberServed" contain the entire text "#Served:23"? Because if so you need to use more than just CInt() to get rid of the text. Did you view the query seperately from the report and verify that TrueNumberServed displays correctly?

Im asking this because I've never had a problem using calculations with CInt()

-Pete

 

[Starlitnight]

Thanks for the response Pete..
Good catch! Here is how I've extracted the number portion of #Served:23

NumberServed: Mid([vcResults],InStr([vcResults],":")+1)


I then used the Cint function to convert the remaining text of 23 to an integer like this:

TrueNumberServed: CInt([NumberServed])

I have verified that this function works. I have no trouble with the coversion. The query runs fine without incident. The trouble occurs when I try to sum the TrueNumberServed field in a report UNLESS I use it at the report footer.....I can't seem to use =Sum([TrueNumberServed]) at any other 'grouping' level...

Let me know if I've not explained enough about the situation....

 

[snyperx3]

I'm sorry I cant see anything wrong with that. What section of the report are you trying to put the sum in?

-Pete

 

[Starlitnight]

Thanks again Pete...
Here is the complete query in which my report is based:


SELECT tFolder.vcFolderType, tFolder.vcFirstName, tFolder.vcLastName, tFolder.vcOrganization, tAddress.vcAddress1, tAddress.vcAddress2, tAddress.vcCity, tAddress.vcState, tAddress.vcCounty, tCalls.dtCalled, tCalls.vcResults, tCalls.vcActCaller, tCalls.vcCallCode, Mid([vcResults],InStr([vcResults],":")+1) AS NumberServed, CInt([NumberServed]) AS TrueNumberServed, tCalls.vcDesc
FROM (tFolder LEFT JOIN tAddress ON tFolder.iID = tAddress.nID) INNER JOIN tCalls ON tFolder.iID = tCalls.nID
WHERE (((tCalls.dtCalled) Between #1/1/2006# And #5/8/2006#));

I have created a footer for the tCalls.vcActCaller and a footer for tCalls.vcCallCode. It is within those sections that I wish to have a sume of TrueNumberServed in order to calculate how mnay clients we've served...

Normally, I don't have a problem with such things yet, I'm stumped on this one....

 

[Starlitnight]

GOT IT! There was a flaw in the data itself not the syntax!
Pete, thank you for your time.

 

[Knicks]

Did you try using CLNG?

I've used CLNG in conjunction with NZ to sum...

 

[Knicks]

CLNG(NZ([truenumberserved],0))

You should be able to sum any number with this expression.

 

[Starlitnight]

Hello! I've not tried CLNG or NZ out as yet but am grateful to know other functions that can achieve the sum. I realize that Clng would be for a long integer but what is nz if you don't mind my asking..?

 

[Knicks]

NZ handles NULLS

NZ([numbers],0)

In that situation the NZ would convert any null from your numbers field into zero. You actually don't need to use the zero there as it defaults to zero on nulls but it shows the placement of where any integer could go to represent a null instead as you could decide to go ,5 if you wanted to represent a null as a "5" in the expression.

Sometimes when calculating an expression NULLS can mess things up, other times they don't seem to matter. Its good form to get in the habit of using NZ for calculations...

=NZ([field1]) + NZ([field2])

Here you can see that the ,0 was not added as its the default if left out....

 

[Starlitnight]

Thank you! I looked up nz on the internet and your definitions and examples are much more clear. Thank you again.