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 "ProductNum ber" 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 6 1751
"Bob" <sm*******@hotm ail.com> wrote in message
news:6h******** *************** *********@4ax.c om... 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 "ProductNum ber" 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(ProductNumb er)=10
AND IsNumeric(Produ ctNumber) = 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(you rActualfieldnam ehere)
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 aaa1234567890kk k
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 <rp*****@aol.co m> 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(yo urActualfieldna mehere)
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 aaa1234567890kk k
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
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 <rp*****@aol.co m> 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
<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*******@hotm ail.com> wrote: On 22 Apr 2004 16:07:17 GMT, Rich P <rp*****@aol.co m> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: paii |
last post by:
I have a table that stores job milestone dates. The 2 milestones I am
interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18.
All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that
will return the c date for TypeID 18 if it exist else the date for TypeID 1,
for all jobs.
the table structure is the following
Job
TypeID
|
by: mark |
last post by:
I've been working on an Access 2000 database for a couple of weeks now. I
took a course in access about a year ago, a crash course, and I learned a
ton, but I didn't touch Access for the year since then so I forgot
everything I learned : ( So this little project I envisioned has turned out
to be much harder than I thought. But I think I'm close with it, so I want
to see it through.
What I wanted to create is a database that will track my...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Alicia |
last post by:
Hello everyone based on the data, I created a union query which
produces this.
SELECT ,,, 0 As ClosedCount
FROM
UNION SELECT
,, 0 AS OpenedCount,
FROM
ORDER BY , ;
|
by: Megan |
last post by:
Hi everybody-
I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."
I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.
I'm using an unbound form (frmStatus) to enter data into a query to
| |
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
|
by: John |
last post by:
hi,
i have created a search form, and i want to search for a specific item in a
field.
e.g. i have a field called colour, which has record1 = 'red, blue, green'
and another record2 = 'red'
now, when i do the search, and only type in red, to search for red, it only
brings back record 2, and not record 1 as well.
|
by: mcmahonb |
last post by:
Hey people...
I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side.
I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using...
|
by: jon |
last post by:
Hi there,
I'm brand new to Access and may be trying to do too much too soon, but
I wanted to get some expert advice on how the best way to go about what
I am trying to accomplish would be.
I am trying to modify the Microsoft Template at the following address
(http://office.microsoft.com/en-us/templates/TC012186931033.aspx?CategoryID=CT101426031033)
to work as an issues tracker that imports and updates the issues from a
SQL 2005...
|
by: Jo |
last post by:
Hi there,
I'm Jo and it's the first time I've posted here. I'm in process of creating
a database at work and have come a little unstuck.....I'm a bit of a novice
and wondered if anyone could help. I work in a library and send out dual
language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers ordered
and books sent out to individuals. I am trying to work out a way of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |