Custom Function

  • Thread starter Thread starter Gregg Knapp
  • Start date Start date
G

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
 
You don't mention what the "prompt" is...
Do you have a field in your report's record source named CapacityCode?
 
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])
 
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


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



Gregg Knapp said:
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
Click to expand...
Click to expand...
 
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])
Click to expand...

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.
 
Score 1 for the good guys!! I was wrong.


PC Datasheet said:
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
(e-mail address removed)
www.pcdatasheet.com



Gregg Knapp said:
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
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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


Gregg Knapp said:
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
Click to expand...
Click to expand...
 
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 said:
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])
Click to expand...

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.
Click to expand...
 
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
 
Maybe you should paste your function into a reply?

--
Duane Hookom
MS Access MVP


Gregg Knapp said:
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
Click to expand...
 
Also, did you for sure place "=" in front of your function name?
=CodeTotal([CapacityCode])

--
Duane Hookom
MS Access MVP
--

Duane Hookom said:
Maybe you should paste your function into a reply?
Click to expand...
 
Alright!
I created a new db, imported the tables, pasted in the function,
and recreated the report. Works perfectly. I'm comparing the
two, but I don't see a difference. Don't know what it was, but
it's working now. This is the kind of thing that'll drive you nuts!

Thanks for all of your input, guys! It has been much appreciated.

Gregg

Maybe you should paste your function into a reply?

--
Duane Hookom
MS Access MVP


Gregg Knapp said:
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
Click to expand...
 
Back
Top