Help wanted Query Criteria

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

"Bob" <smileyBob@hotmail.com> wrote in message
news:6hbd80hkfq27kava335n8r3r4d2rmj2jqi@4ax.com...[color=blue]
> Hullo Everybody
>
> What is the expression that I need to use to run a query that returns[/color]
only records with exactly 10[color=blue]
> NUMERIC characters CONTAINED in a field called "ProductNumber" There may[/color]
sometimes be other things[color=blue]
> in the field. This does not matter. I am looking for the records with a[/color]
product field that CONTAINS[color=blue]
> somewhere in the text 10 NUMBERs.
>[/color]


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.

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!

On 21 Apr 2004 21:37:29 GMT, Rich P <rpng123@aol.com> wrote:
[color=blue]
>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![/color]

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

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!

On 22 Apr 2004 16:07:17 GMT, Rich P <rpng123@aol.com> wrote:
[color=blue]
>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![/color]

That did it. Thanks

I have learned from this

Regards Smiley 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 <smileyBob@hotmail.com> wrote:
[color=blue]
>On 22 Apr 2004 16:07:17 GMT, Rich P <rpng123@aol.com> wrote:
>[color=green]
>>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![/color]
>
>That did it. Thanks
>
>I have learned from this
>
>Regards Smiley Bob
>[/color]