|
 |
access thread: sql statement needed
Message #1 by "chris angus" <chris@a...> on Tue, 21 May 2002 20:19:12
|
|
Hi can anyone tell me the sql statement to delete all records where
PartNo ends with a number and not a letter, please.
Sort Sys PartNo PartQty
1 030003 ARG1 4
2 030003 AR21V 1
3 030003 SYA7 1
4 030003 AR6A 1
5 030003 ARR2 3
6 030003 SYA7 1
7 030003 AR7X 1
8 030003 ARR2 2
9 030005 ARG2 2
10 030005 AR8W 1
11 030005 SYA10 1
12 030005 AR9V 1
13 030005 UNR4 2
14 030005 SYA8 1
15 030005 AR10K 1
16 030005 ARR5 2
Message #2 by "Carnley, Dave" <dcarnley@a...> on Tue, 21 May 2002 14:18:07 -0500
|
|
delete from PartsTable where
charindex (right(PartNo,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 3:19 PM
To: Access
Subject: [access] sql statement needed
Hi can anyone tell me the sql statement to delete all records where
PartNo ends with a number and not a letter, please.
Sort Sys PartNo PartQty
1 030003 ARG1 4
2 030003 AR21V 1
3 030003 SYA7 1
4 030003 AR6A 1
5 030003 ARR2 3
6 030003 SYA7 1
7 030003 AR7X 1
8 030003 ARR2 2
9 030005 ARG2 2
10 030005 AR8W 1
11 030005 SYA10 1
12 030005 AR9V 1
13 030005 UNR4 2
14 030005 SYA8 1
15 030005 AR10K 1
16 030005 ARR5 2
Message #3 by "Gregory Serrano" <SerranoG@m...> on Tue, 21 May 2002 20:54:33
|
|
Chris,
<< Hi can anyone tell me the sql statement to delete all records where
PartNo ends with a number and not a letter, please.
Sort Sys PartNo PartQty
1 030003 ARG1 4
2 030003 AR21V 1
3 030003 SYA7 1
4 030003 AR6A 1
5 030003 ARR2 3
6 030003 SYA7 1
7 030003 AR7X 1
8 030003 ARR2 2
9 030005 ARG2 2
10 030005 AR8W 1
11 030005 SYA10 1
12 030005 AR9V 1
13 030005 UNR4 2
14 030005 SYA8 1
15 030005 AR10K 1
16 030005 ARR5 2 >>
Here's another way.
DELETE IsNumeric(Right([PartNo],1)) AS [LastDigit] FROM tblTableName WHERE
(((IsNumeric(Right([PartNo],1)))=-1));
Greg
Message #4 by "chris angus" <chris@a...> on Tue, 21 May 2002 21:40:50
|
|
Thanks lads but I can't get any of these to work, is the (Right) function
valid in VB.
Chris
Message #5 by "Carnley, Dave" <dcarnley@a...> on Tue, 21 May 2002 16:00:05 -0500
|
|
VB? I thought you needed a SQL statement?
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 4:41 PM
To: Access
Subject: [access] Re: sql statement needed
Thanks lads but I can't get any of these to work, is the (Right) function
valid in VB.
Chris
Message #6 by "chris angus" <chris@a...> on Tue, 21 May 2002 22:20:01
|
|
I do, but it's to use in a VB app.
eg.
data1.recordset = "select * from PartsTable where
charindex (right(PartNo,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0"
> VB? I thought you needed a SQL statement?
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 4:41 PM
To: Access
Subject: [access] Re: sql statement needed
Thanks lads but I can't get any of these to work, is the (Right) function
valid in VB.
Chris
Message #7 by "Carnley, Dave" <dcarnley@a...> on Tue, 21 May 2002 16:25:20 -0500
|
|
if the text of the sql string is encoded as a string then VB won;t be
interpreting the sql code, so this is not your problem. What does your
actual code look like and what error do you get?
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 5:20 PM
To: Access
Subject: [access] Re: sql statement needed
I do, but it's to use in a VB app.
eg.
data1.recordset = "select * from PartsTable where
charindex (right(PartNo,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0"
> VB? I thought you needed a SQL statement?
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 4:41 PM
To: Access
Subject: [access] Re: sql statement needed
Thanks lads but I can't get any of these to work, is the (Right) function
valid in VB.
Chris
Message #8 by "John Ruff" <papparuff@c...> on Tue, 21 May 2002 14:25:41 -0700
|
|
DELETE Products.*, IsNumeric(Right([PARTNO],1)) AS Expr1
FROM Products
WHERE (((IsNumeric(Right([PARTNO],1)))=-1));
Products is the table that the PartNo will be deleted from
John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity
xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Tuesday, May 21, 2002 8:19 PM
To: Access
Subject: [access] sql statement needed
Hi can anyone tell me the sql statement to delete all records where
PartNo ends with a number and not a letter, please.
Sort Sys PartNo PartQty
1 030003 ARG1 4
2 030003 AR21V 1
3 030003 SYA7 1
4 030003 AR6A 1
5 030003 ARR2 3
6 030003 SYA7 1
7 030003 AR7X 1
8 030003 ARR2 2
9 030005 ARG2 2
10 030005 AR8W 1
11 030005 SYA10 1
12 030005 AR9V 1
13 030005 UNR4 2
14 030005 SYA8 1
15 030005 AR10K 1
16 030005 ARR5 2
Message #9 by tegrovesjr@r... on Tue, 21 May 2002 22:50:28
|
|
The WHERE Condition provided by Greg works against the suppliers table in
the Northwind.mdb. You may need to add the Trim function depending on the
data. The query is different depend on the database.
Northwind.mdb
SELECT * FROM SUPPLIERS
WHERE IsNumeric(Right(Trim([address]),1)) = -1
Northwind SQL Server 2000
SELECT * FROM SUPPLIERS
WHERE IsNumeric(Right(RTrim([address]),1)) = 1
> I do, but it's to use in a VB app.
e> g.
d> ata1.recordset = "select * from PartsTable where
c> harindex (right(PartNo,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0"
>
>> VB? I thought you needed a SQL statement?
>
> -----Original Message-----
F> rom: chris angus [mailto:chris@a...]
S> ent: Tuesday, May 21, 2002 4:41 PM
T> o: Access
S> ubject: [access] Re: sql statement needed
>
T> hanks lads but I can't get any of these to work, is the (Right)
function
v> alid in VB.
>
C> hris
-> --
C> hange your mail options at
t> o unsubscribe send a blank email to
Message #10 by joe.dunn@c... on Wed, 22 May 2002 09:28:59 +0000
|
|
try:
delete * from YOURTABLE where IsNumber(Last(PARTNO,1)
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
|
|
|
View Cart
