# Thread: Calculating numbers within a field

1. Registered User
Join Date
Oct 2006
Posts
110

## Unanswered: Calculating numbers within a field

Hello Everyone, After searching the forum i havent found anything related to my problem..

I am trying to calculate numbers within a field, and show the total in a seperate field. This is a report cover sheet that includes the number of records in the shipment.

Example

text entered in field are tracking numbers: 1570-1575

total records are 6.

I want to be able to show the total of 6 in a seperate field.

Chris

2. Registered User
Join Date
May 2005
Location
Posts
2,888
Not particularly difficult. You'll need to acquaint yourself with the various string functions; Instr, Left, Mid, etc. With those you can peel out the 2 components and then do the math.

3. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by pbaldy
Not particularly difficult. You'll need to acquaint yourself with the various string functions; Instr, Left, Mid, etc. With those you can peel out the 2 components and then do the math.
Paul,

Didn't I see a trick a few years ago on here using Excel to run the calcs????

4. Registered User
Join Date
May 2005
Location
Posts
2,888
I don't recall seeing it, but that doesn't mean anything (and you've been here longer than I). I can see how Excel might recognize that as a formula, where Access does not.

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
You could try something like:
Code:
` Abs(Eval(TheData))+1`
Also - this looks like a possible candidate for splitting a field into two.

HTH

6. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by pootle flump
You could try something like:
Code:
` Abs(Eval(TheData))+1`
Also - this looks like a possible candidate for splitting a field into two.

HTH
Poots ... This one gets into the slippery slope of formula eval which we both know is a lot less fun in Access ... Didn't we have this dicussion a few years ago???

Unfortunately, we probably won't be able to find it ...

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by M Owen
Didn't we have this dicussion a few years ago???
Not me and thee - I can't think of a single production instance I have had need of it so I haven't looked at it all that hard.

What is the gotcha?

8. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by pootle flump
Not me and thee - I can't think of a single production instance I have had need of it so I haven't looked at it all that hard.

What is the gotcha?
Gotcha??? On eval'ing a generic formula in Access? How about precedence? I had to write 1 in VC++ years ago and generally it's done with recursion ... I mean you can write a recursive function in VBA and get it done ... And don't get me started on variables in an expression ...

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ok - defensive programming - makes good sense. Eval IME on the boards is (typically) used as a bodge and so I would certainly avoid it wherever possible.

Personnally I would not implement it here, nor manipulate the string - I would normalise the data. But it does work for the posted text value.

10. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by pootle flump
Ok - defensive programming - makes good sense. Eval IME on the boards is (typically) used as a bodge and so I would certainly avoid it wherever possible.

Personnally I would not implement it here, nor manipulate the string - I would normalise the data. But it does work for the posted text value.
Agreed. In this case, I'd have 2 textboxes: 1st From 2nd Thru ... Just like most everyone else here would. Eliminating this whole discussion all together ...

11. Registered User
Join Date
Oct 2006
Posts
110
Thanks for the suggestions, and I did see a post that did mention Excel.. but it was calculating numbers in a field that has letters included.

I didnt plan on calculating this field originally, but the program was demonstrated at work yesterday, and that suggestion was mentioned. If I did it, it would be with beginning and ending number fields.

Chris

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•