By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Help wanted Query Criteria

P: n/a
Bob
Hullo Everybody

What is the expression that I need to use to run a query that returns only records with exactly 10
NUMERIC characters CONTAINED in a field called "ProductNumber" There may sometimes be other things
in the field. This does not matter. I am looking for the records with a product field that CONTAINS
somewhere in the text 10 NUMBERs.

For Instance:

(1) I want these records (10 NUMERIC)
9876765432
0987665576
5464663557
7845673456

(2) I DO NOTwant these records (10 characters but some are NOT NUMERIC
PE9875632Q
ZR0987576U
JH5463557T
RT7873456V

(3) I DO NOTwant these records EITHER (Only 9 NUMERIC)
876765432
987665576
464663557
784567345

I have tried LIKE "???????????*" but that did not work

Thanks

Smiley Bob

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Bob" <sm*******@hotmail.com> wrote in message
news:6h********************************@4ax.com...
Hullo Everybody

What is the expression that I need to use to run a query that returns only records with exactly 10 NUMERIC characters CONTAINED in a field called "ProductNumber" There may sometimes be other things in the field. This does not matter. I am looking for the records with a product field that CONTAINS somewhere in the text 10 NUMBERs.

Do we eliminate records that cannot be converted to numbers? If so the
solution is easy:

SELECT * FROM products
WHERE Len(ProductNumber)=10
AND IsNumeric(ProductNumber) = True

but if you need to search for the existence of 10 numeric chacters within a
string that may also contain non-numeric charctares it's a bit more
difficult. I think you need a VBA function for that.

Nov 12 '05 #2

P: n/a
Add a temp column to your query grid. This column will be populated
with only a string of 10 numeric chars. The way you will populate this
column is by writing a function as follows which will take values from
your actual column in question. The function will parse the values for
each row, and if there is a string containing 10 consecutive digits that
value will be added to the temp column otherwise the function returns
"".

In the field name add this:

temp:getNum(yourActualfieldnamehere)

Under the criteria for temp add:

<> ""

This will yield a resultset where only rows that contain values from
your actual column that are 10 consecutive numeric chars.

Here is a function that does this:

'******************************************
Function getNum(x As Variant) As Variant
Dim i As Integer, j As Integer, k As Integer, y As Variant
For i = 1 To Len(x)
y = Mid(x, i, 1)
If j = 0 And IsNumeric(y) Then j = i 'beginning of number
If j > 0 And Not IsNumeric(y) Then
k = i - 1
Exit For
End If
If j > 0 And i - j + 1 = 10 Then
k = i
Exit For
End If
Next
If k - j - 9 = 0 Then getNum = Mid(x, j, 10)
End Function
'************************************************* ***

It isn't pretty, but it is functional, and Free! This function will
return values like these

1231231234, 1234567890

from xx1231231234 or 1231231234xxx or aaa1234567890kkk

and will not return values like these

123123123 (only 9 digits)
or 1 231231234 (a space breaks the continuity)
or xxx12312312x3 (not 10 consecutive digits)

based on your specifications. You can place this function in a standard
code module (not a form code module) so that the query can access it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
Bob
On 21 Apr 2004 21:37:29 GMT, Rich P <rp*****@aol.com> wrote:
Add a temp column to your query grid. This column will be populated
with only a string of 10 numeric chars. The way you will populate this
column is by writing a function as follows which will take values from
your actual column in question. The function will parse the values for
each row, and if there is a string containing 10 consecutive digits that
value will be added to the temp column otherwise the function returns
"".

In the field name add this:

temp:getNum(yourActualfieldnamehere)

Under the criteria for temp add:

<> ""

This will yield a resultset where only rows that contain values from
your actual column that are 10 consecutive numeric chars.

Here is a function that does this:

'******************************************
Function getNum(x As Variant) As Variant
Dim i As Integer, j As Integer, k As Integer, y As Variant
For i = 1 To Len(x)
y = Mid(x, i, 1)
If j = 0 And IsNumeric(y) Then j = i 'beginning of number
If j > 0 And Not IsNumeric(y) Then
k = i - 1
Exit For
End If
If j > 0 And i - j + 1 = 10 Then
k = i
Exit For
End If
Next
If k - j - 9 = 0 Then getNum = Mid(x, j, 10)
End Function
'************************************************ ****

It isn't pretty, but it is functional, and Free! This function will
return values like these

1231231234, 1234567890

from xx1231231234 or 1231231234xxx or aaa1234567890kkk

and will not return values like these

123123123 (only 9 digits)
or 1 231231234 (a space breaks the continuity)
or xxx12312312x3 (not 10 consecutive digits)

based on your specifications. You can place this function in a standard
code module (not a form code module) so that the query can access it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


That works spot on. Well almost.

There are a number of records that have Null data, and these throw up a fault that stops the program
running, and opens the code editor, which give the user palpitations.

That code is a bit complicated so if anyone knows the answer I'd be grateful.

Thanks to all that have helped.

Smiley Bob

Nov 12 '05 #4

P: n/a
Ah yes, the old "Invalid use of Null" error. Try this one:

Function getNum(x As Variant) As Variant
Dim i As Integer, j As Integer, k As Integer, y As Variant
If Not IsNull(x) Then
For i = 1 To Len(x)
y = Mid(x, i, 1)
If j = 0 And IsNumeric(y) Then j = i 'beginning of num
If j > 0 And Not IsNumeric(y) Then
k = i - 1
Exit For
End If
If j > 0 And i - j + 1 = 10 Then
k = i
Exit For
End If
Next
If k - j - 9 = 0 Then getNum = Mid(x, j, 10)
End If
End Function

In this version I check to see if the value passed to the function is
null or not. If it is not null, then we process the value. If there is
no value (value is null - note: value will never = equal Null - no such
thing - IsNull means there is no value it does not mean = 0 or = "").
If there is no value, then the function returns no value, Null. This
should do the trick. Good thing this isn't DotNet. DotNet would have
been way less friendly with this function (variants are evil)---:).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Bob
On 22 Apr 2004 16:07:17 GMT, Rich P <rp*****@aol.com> wrote:
Ah yes, the old "Invalid use of Null" error. Try this one:

Function getNum(x As Variant) As Variant
Dim i As Integer, j As Integer, k As Integer, y As Variant
If Not IsNull(x) Then
For i = 1 To Len(x)
y = Mid(x, i, 1)
If j = 0 And IsNumeric(y) Then j = i 'beginning of num
If j > 0 And Not IsNumeric(y) Then
k = i - 1
Exit For
End If
If j > 0 And i - j + 1 = 10 Then
k = i
Exit For
End If
Next
If k - j - 9 = 0 Then getNum = Mid(x, j, 10)
End If
End Function

In this version I check to see if the value passed to the function is
null or not. If it is not null, then we process the value. If there is
no value (value is null - note: value will never = equal Null - no such
thing - IsNull means there is no value it does not mean = 0 or = "").
If there is no value, then the function returns no value, Null. This
should do the trick. Good thing this isn't DotNet. DotNet would have
been way less friendly with this function (variants are evil)---:).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


That did it. Thanks

I have learned from this

Regards Smiley Bob
Nov 12 '05 #6

P: n/a
Bob
<Grin>

Now the client is asking for another query that does the opposite of this
ie finds all the records that DON'T have 10 numeric characters and of course have
no Nulls

I need a drink

Smiley Bob

On Thu, 22 Apr 2004 17:50:43 +0100, Bob <sm*******@hotmail.com> wrote:
On 22 Apr 2004 16:07:17 GMT, Rich P <rp*****@aol.com> wrote:
Ah yes, the old "Invalid use of Null" error. Try this one:

Function getNum(x As Variant) As Variant
Dim i As Integer, j As Integer, k As Integer, y As Variant
If Not IsNull(x) Then
For i = 1 To Len(x)
y = Mid(x, i, 1)
If j = 0 And IsNumeric(y) Then j = i 'beginning of num
If j > 0 And Not IsNumeric(y) Then
k = i - 1
Exit For
End If
If j > 0 And i - j + 1 = 10 Then
k = i
Exit For
End If
Next
If k - j - 9 = 0 Then getNum = Mid(x, j, 10)
End If
End Function

In this version I check to see if the value passed to the function is
null or not. If it is not null, then we process the value. If there is
no value (value is null - note: value will never = equal Null - no such
thing - IsNull means there is no value it does not mean = 0 or = "").
If there is no value, then the function returns no value, Null. This
should do the trick. Good thing this isn't DotNet. DotNet would have
been way less friendly with this function (variants are evil)---:).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


That did it. Thanks

I have learned from this

Regards Smiley Bob


Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.