Solved

Include bracket inside sql text string

Posted on 2004-04-29
8
657 Views
Last Modified: 2008-03-03
To screen out mobile phone numbers,
In a WHERE clause I want say :
AND phone_number not like "(04)*"
How should this be coded so the brackets are not confused with other brackets in the statement?
0
Comment
Question by:andrewcurtis
8 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10946738
Hmm, this LIKE will work the same as :
WHERE LEFT([phone_number],4) <> "(04)"

And will require that the (04) is in the first four positions, is that what you need ?

Nic;o)
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10946745
Hmm, into an SQL string all needed would be to use single quotes like:
strSQL = "select * from tblX where phone_number not like '(04)*';"

Nic;o)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10946749
Hi andrewcurtis,

I think you have done what you asked in your example.

Presumably your phone_number field has a data type of Text or else the entries would not be able to include brackets.


The extraneous brackets inserted by the MS-Access Query By Design builder are not needed:
SELECT * FROM Table1
WHERE (((Table1.Field1)="something") AND ((Table1.phone_number) Not Like "(04)*"));


Your clearer SELECT statement would be along the lines of:
SELECT * FROM Table WHERE Field1 = "something" AND phone_number Not Like "(04)*"


PS. Are you in the UK [I'm guessing so as you didn't say 'Parenthesis' :)]?

Mobile numbers begin with 07...



BFN,

fp.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 27

Expert Comment

by:jjafferr
ID: 10946788
Hi andrewcurtis,

I am not good in SQL, but worth giving it a try,
why not define it with a variable, then put this variable in the SQL.
iNumber="(04)*"
strSQL = "select * from tblX where phone_number not like iNumber";

I hope this helps,

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10947009
phone_number not like "(04)*"

this will records where the phone number does not start with (04), is that what you are after?

perhaps, because you got brackets, its a string field, you put single quotes in (like Nico has done)
for string comparisons, you got to surround it with quotes

 phone_number not like '(04)*'

to do a match on anything with (04) in, put an asterix in front as well ie  *(04)*





0
 
LVL 4

Expert Comment

by:AndyAmess
ID: 10947338
Andrew Curtis,

This is a common problem.

 "......AND phone_number not like " &   Chr$(34) & "(04)*" &  Chr$(34)

Because the brackets will now be in quotes they will not be confused with non-text brackets.

I usually have the procedure below and call it like

"......AND phone_number not like " &  Quotes("(04)*")

=====================================
Public Function Quotes(aText As String)
    Quotes = Chr$(34) & aText & Chr$(34)
End Function


A. ;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now