The following is a small example of a field in series of records. I need the number from within the 2nd and 3rd dash. Please help. Thanks.
15-1296-2-468-141315-89440-4-152-779 15-128441-5-468-1462
The following is a small example of a field in series of records. I need the number from within the 2nd and 3rd dash. Please help. Thanks.
15-1296-2-468-141315-89440-4-152-779 15-128441-5-468-1462
Is it always a one digit number? If not, how high can it go?
If it is always one digit, this will work (where "MyField" is the name of your field):
Code:MyNumber: Mid([MyField],InStr(InStr([MyField],"-")+1,[MyField],"-")+1,1)
I might create a custom function using the Split() function. It would take the string as an input parameter, and probably also a number representing which position was desired. That would let you return any of the values within the string, which you may need in other places.
I am personally a fan of UDFs, especially if you are not adverse to having VBA in your database, and it isn't a fairly straightforward/simple calculation. So if your number may be more than one digit, I think that tips the scales even further towards using a UDF, like Paul suggested.
A few advantages to UDFs:
1. I think they are easier to read/maintain, especially if you start getting into some complex functions.
2. If you have to use this in more than one spot, if you ever need to change anything, you only have to change the UDF code, instead of changing all the different places you have the calculation.
It can be a number between 4 and 7 digits.
I obviously agree.
I think a UDF would be the best way to go, but I am so new to using formulas in access. Can you offer a suggestion for this? Thanks.
If it will be 4 to 7 digits, why do examples show only 1 digit after the second dash?
Will there be any leading zeros? Would they have to be retained?
A UDF would be great but for an immediate solution, try:
Val(Mid([MyField],InStr(InStr([MyField],"-")+1,[MyField],"-")+1))
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I'll get out of the way.