Help with "Expression"

[Fluppie]

Hello,

I was wondering if anyone could help me with the following problem.
I have a products table with a column called 'Packaging'
The values in this column are displayed as "box/5pc" or "seal/10pc".
What i want to do is "filter" out the text-part in this so it displays just
"5" or "10".
How do put this together in an expression (if possible). I will use this in
a Query.

Any suggestions or idea's are will be very much appreciated.
Thanks in Advance,
Anthony

 

[Dirk Goldgar]

Hello,

I was wondering if anyone could help me with the following problem.
I have a products table with a column called 'Packaging'
The values in this column are displayed as "box/5pc" or "seal/10pc".
What i want to do is "filter" out the text-part in this so it
displays just "5" or "10".
How do put this together in an expression (if possible). I will use
this in a Query.

Any suggestions or idea's are will be very much appreciated.
Thanks in Advance,
Anthony

You may be in luck. Not long ago I had occasion to write a function
that does this, in response to another newsgroup question. Paste this
function into a standard module:

'----- start of code -----
Function StripAllButDigits(varOldNumber As Variant) As String

'Removes any non-numeric characters in a string

Dim i As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For i = 1 To intLength
strThisCharacter = Mid(strOldNumber, i, 1)
If strThisCharacter >= "0" And strThisCharacter <= "9" Then
strNewNumber = strNewNumber & strThisCharacter
End If
Next i

StripAllButDigits = strNewNumber

End Function
'----- end of code -----

You could call this in a query using a computed field like this:

PkgNum: StripAllButDigits([Packaging])

In SQL View, that would come out like this:

StripAllButDigits([Packaging]) AS PkgNum

See if that gives you what you want.

 

[Anthony]

Thanks Dirk,

Unfortunatly it's not working, i don't know what went wrong but
when i want to run the query it says
" undefined function 'stripallbutdigits' in expression "
Im not to familiar with building modules so im not quiet sure what went
wrong.
I pasted your code in an new module and saved it.
Do you have any idea on how to solve this or what went wrong??

Thanks in advance,
Anthony (Fluppie)

Hello,

I was wondering if anyone could help me with the following problem.
I have a products table with a column called 'Packaging'
The values in this column are displayed as "box/5pc" or "seal/10pc".
What i want to do is "filter" out the text-part in this so it
displays just "5" or "10".
How do put this together in an expression (if possible). I will use
this in a Query.

Any suggestions or idea's are will be very much appreciated.
Thanks in Advance,
Anthony

You may be in luck. Not long ago I had occasion to write a function
that does this, in response to another newsgroup question. Paste this
function into a standard module:

'----- start of code -----
Function StripAllButDigits(varOldNumber As Variant) As String

'Removes any non-numeric characters in a string

Dim i As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For i = 1 To intLength
strThisCharacter = Mid(strOldNumber, i, 1)
If strThisCharacter >= "0" And strThisCharacter <= "9" Then
strNewNumber = strNewNumber & strThisCharacter
End If
Next i

StripAllButDigits = strNewNumber

End Function
'----- end of code -----

You could call this in a query using a computed field like this:

PkgNum: StripAllButDigits([Packaging])

In SQL View, that would come out like this:

StripAllButDigits([Packaging]) AS PkgNum

See if that gives you what you want.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk,

Unfortunatly it's not working, i don't know what went wrong but
when i want to run the query it says
" undefined function 'stripallbutdigits' in expression "
Im not to familiar with building modules so im not quiet sure what
went wrong.
I pasted your code in an new module and saved it.
Do you have any idea on how to solve this or what went wrong??

It's hard to say, Anthony. The function works fine for me, so I don't
think it's the function itself. Please post the SQL of the query that
you are calling it from. You pasted the code into a new standard
module, not a form module, report module, or class module, right?