Re: Custom Function

That would certainly solve the need for the custom function
if the values were in separate fields... But they are indeed
tossed all together. (Don't you love inheriting this stuff?)
I'll make a mental note of that VAL() function for later.
Thanks!

So an example of the data in a field I'm working with is -
AM20,WH2,LF13

Separated by commas. What I need to do is pull out the
individual numbers and add them up for the report. In this case -
20 + 2 + 13 = 35

I've got a function started - It's calling it that seems to be a problem.
I'll double check that I've used the correct field name in the
call from the report : =CodeTotal([CapacityCode])

My understanding of this is that it should take the value from the
field here and pass it to the function. Perhaps I haven't put the
function in the proper place - I went under 'Modules' and created
it there. Shows up in the object browser ok.

Gregg

P.S. - Thanks much for all the input, by the way!
These lists are the best.


"fredg" <fgutkind@xxxxxxxxxxxxxxx> wrote in message
news:xvdlxdlsbkko.19zt5fgzstm7x.dlg@xxxxxxxxxxxxx
> On Thu, 09 Jun 2005 03:19:36 GMT, PC Data*** wrote:
>
>> You don't need a custom function! You need to use the built-in Val
>> function.
>> The Val function returns the first set of consecutive numbers in an
>> alphanumeric string. Ex., Val("AM20") = 20.
>>
>> Create a calculated field in your query by placing the following
>> expression
>> in the first empty field:
>> NumbersForTotal:IIF(IsNull([MyField]),0,Val([MyField]))
>>
>> Add a hidden textbox in the detail section of your report and set the
>> control source property to:
>> =NumbersForTotal
>>
>> In the textbox where you want the total, put this expression in the
>> control
>> source:
>> =Sum([NumbersForTotal])
>
> PC,
> Regarding >The Val function returns the first set of consecutive
> numbers in an alphanumeric string. Ex., Val("AM20") = 20. <
>
> Sorry, not on my Access 2002.
> Val("AM20") = 0
>
> Try it.
>
> Val() returns the value of the first numeric string only if the
> numbers are first in the string, not anywhere in the string.
> "ABC235" returns 0
> "235ABC" returns 235
>
> The OP needs to cycle through the string and extract the numbers, then
> total them.
> It's not clear from his message whether he wants to total
> 20 + 2 + 13 = 35
> or 2+0+2+1+3 = 8
> so I'll stop here.
>
> --
> Fred
> Please only reply to this newsgroup.
> I do not reply to personal email.


.

Quantcast