Custom Function

[Gregg Knapp]

Using Microsoft Access 2003.
I'm attempting to write a custom function to strip out some
characters from a field, then total the leftover numbers.
The data in the field could look something like this -

AM20,WH2,LF13

I'd like to extract the numbers, and add them on a report.
I've used VB, but I seem to be having problems calling the
function.

For example -
I have the dataSource field in the Access Report setup
something like so...
=CodeTotal([CapacityCode])

I've written the function in a module like so -
Function CodeTotal(strCode As String) As String
*Do stuff*
CodeTotal = *RESULT*
End Function

However, when I run the report, it prompts me for input,
then processes what I've typed in, rather than the code
data from the field.

Am I doing this wrong?
Thanks for the assistance!

Gregg Knapp
gregg underscore knapp at hotmail dot com

 

[Duane Hookom]

You don't mention what the "prompt" is...
Do you have a field in your report's record source named CapacityCode?

 

[PC Datasheet]

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])

 

[Duane Hookom]

Good comments Steve if the field values were 20AM, 2WH, and 13LF.
Val("AM20")=0
Val("20AM")=20

I had assumed the field value was "AM20,WH2,LF13" rather than three records
with values of:
AM20
WH2
LF13

--
Duane Hookom
MS Access MVP

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 Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com

Using Microsoft Access 2003.
I'm attempting to write a custom function to strip out some
characters from a field, then total the leftover numbers.
The data in the field could look something like this -

AM20,WH2,LF13

I'd like to extract the numbers, and add them on a report.
I've used VB, but I seem to be having problems calling the
function.

For example -
I have the dataSource field in the Access Report setup
something like so...
=CodeTotal([CapacityCode])

I've written the function in a module like so -
Function CodeTotal(strCode As String) As String
*Do stuff*
CodeTotal = *RESULT*
End Function

However, when I run the report, it prompts me for input,
then processes what I've typed in, rather than the code
data from the field.

Am I doing this wrong?
Thanks for the assistance!

Gregg Knapp
gregg underscore knapp at hotmail dot com

 

[fredg]

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.

 

[PC Datasheet]

Score 1 for the good guys!! I was wrong.

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 Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com

Using Microsoft Access 2003.
I'm attempting to write a custom function to strip out some
characters from a field, then total the leftover numbers.
The data in the field could look something like this -

AM20,WH2,LF13

I'd like to extract the numbers, and add them on a report.
I've used VB, but I seem to be having problems calling the
function.

For example -
I have the dataSource field in the Access Report setup
something like so...
=CodeTotal([CapacityCode])

I've written the function in a module like so -
Function CodeTotal(strCode As String) As String
*Do stuff*
CodeTotal = *RESULT*
End Function

However, when I run the report, it prompts me for input,
then processes what I've typed in, rather than the code
data from the field.

Am I doing this wrong?
Thanks for the assistance!

Gregg Knapp
gregg underscore knapp at hotmail dot com

 

[Xed]

Since you've got code that works, and the calling syntax looks correct
first thing to check is as per Duane's first reply, i.e. you've got
field called CapacityCode. (Typo's are usually the cause of such
prompt) Second that you don't have the control named CapacityCode a
well as a field of that name - this can cause #Error results as th
code is being passed the control rather than the string valued field

 

[Gregg Knapp]

Ya - There's a field in the table (Capacities) called CapacityCode.
That's what I was trying to pass over to the function. The "prompt"
is a small window from access which states "CodeTotal" (the name
of the function) with a text box for me to enter something into. It's
asking for information to send to the function, rather than sending
the data from the field.

G

You don't mention what the "prompt" is...
Do you have a field in your report's record source named CapacityCode?

--
Duane Hookom
MS Access MVP

Using Microsoft Access 2003.
I'm attempting to write a custom function to strip out some
characters from a field, then total the leftover numbers.
The data in the field could look something like this -

AM20,WH2,LF13

I'd like to extract the numbers, and add them on a report.
I've used VB, but I seem to be having problems calling the
function.

For example -
I have the dataSource field in the Access Report setup
something like so...
=CodeTotal([CapacityCode])

I've written the function in a module like so -
Function CodeTotal(strCode As String) As String
*Do stuff*
CodeTotal = *RESULT*
End Function

However, when I run the report, it prompts me for input,
then processes what I've typed in, rather than the code
data from the field.

Am I doing this wrong?
Thanks for the assistance!

Gregg Knapp
gregg underscore knapp at hotmail dot com

 

[Gregg Knapp]

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.

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.

 

[Xed]

You didn't make the function "Private" did you? e.g. *Private *Functio
CodeTotal(var) As Long. That could cause the control to interpret th
function as a variable/parameter.

Couple of other things to check... have you got any sorting/grouping o
"CodeTotal"? That commonly catches out my colleague (forgets that he'
put a sort in earlier then removes the field from the report an
recordset but not the sorting). Also, any "hidden fields" (behind othe
fields) on your report that contain the CodeTotal call.

Xe

 

[Gregg Knapp]

I've saved the module w/in Access as TextManipulation and the
first of the functions therein is called CodeTotal. I double checked
the field name, too - Which is why I'm confused. I would have
expected an error re: an incorrect or non-existent field, rather than
a prompt.

Something else that might shed light on it for someone other than
myself - If I enter a string into the prompt, it passes that to the
function and that result is shown for every record processed on
the report. I would also have expected it to prompt me again for
each record in this case - Apparently not.

G

 

[Duane Hookom]

Maybe you should paste your function into a reply?

--
Duane Hookom
MS Access MVP

I've saved the module w/in Access as TextManipulation and the
first of the functions therein is called CodeTotal. I double checked
the field name, too - Which is why I'm confused. I would have
expected an error re: an incorrect or non-existent field, rather than
a prompt.

Something else that might shed light on it for someone other than
myself - If I enter a string into the prompt, it passes that to the
function and that result is shown for every record processed on
the report. I would also have expected it to prompt me again for
each record in this case - Apparently not.

G

 

[Duane Hookom]

Also, did you for sure place "=" in front of your function name?
=CodeTotal([CapacityCode])

--
Duane Hookom
MS Access MVP
--

Maybe you should paste your function into a reply?