473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Command

42
Hello,

I am just looking for the short command so I can bring up addresses that start with a letter A-Z and not a number. This is just so I can do a visual look over these address that don't start with a number and mark them of as bad and exclude them from mail merges etcI can then chase them up for next time. I presume there is something where in SQL I can go where like = "something * ". I have already got this working the long way by doing like "a*" or like "b*" through the whole alpahabet ;). Work ok I can easily identify the bad addresses it bring up PO Box also doing it that way.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE STD.Address Like "a*" 
  4. Or (STD.Address) Like "b*" 
  5. Or (STD.Address) Like "c*" 
  6. Or (STD.Address) Like "d*" 
  7. Or (STD.Address) Like "e*" 
  8. Or (STD.Address) Like "f*" 
  9. Or (STD.Address) Like "g*" 
  10. Or (STD.Address) Like "h*" 
  11. Or (STD.Address) Like "i*" 
  12. Or (STD.Address) Like "j*" 
  13. Or (STD.Address) Like "k*" 
  14. Or (STD.Address) Like "l*" 
  15. Or (STD.Address) Like "m*"
  16. Or (STD.Address) Like "n*" 
  17. Or (STD.Address) Like "o*" 
  18. Or (STD.Address) Like "p*" 
  19. Or (STD.Address) Like "q*" 
  20. Or (STD.Address) Like "r*" 
  21. Or (STD.Address) Like "s*" 
  22. Or (STD.Address) Like "t*" 
  23. Or (STD.Address) Like "u*" 
  24. Or (STD.Address) Like "v*" 
  25. Or (STD.Address) Like "w*" 
  26. Or (STD.Address) Like "x*" 
  27. Or (STD.Address) Like "y*" 
  28. Or (STD.Address) Like "z*"
;
Jun 19 '08 #1
14 6705
puppydogbuddy
1,923 Expert 1GB
Hello,

I am just looking for the short command so I can bring up addresses that start with a letter A-Z and not a number. This is just so I can do a visual look over these address that don't start with a number and mark them of as bad and exclude them from mail merges etcI can then chase them up for next time. I presume there is something where in SQL I can go where like = "something * ". I have already got this working the long way by doing like "a*" or like "b*" through the whole alpahabet ;). Work ok I can easily identify the bad addresses it bring up PO Box also doing it that way.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE STD.Address Like "a*" 
  4. Or (STD.Address) Like "b*" 
  5. Or (STD.Address) Like "c*" 
  6. Or (STD.Address) Like "d*" 
  7. Or (STD.Address) Like "e*" 
  8. Or (STD.Address) Like "f*" 
  9. Or (STD.Address) Like "g*" 
  10. Or (STD.Address) Like "h*" 
  11. Or (STD.Address) Like "i*" 
  12. Or (STD.Address) Like "j*" 
  13. Or (STD.Address) Like "k*" 
  14. Or (STD.Address) Like "l*" 
  15. Or (STD.Address) Like "m*"
  16. Or (STD.Address) Like "n*" 
  17. Or (STD.Address) Like "o*" 
  18. Or (STD.Address) Like "p*" 
  19. Or (STD.Address) Like "q*" 
  20. Or (STD.Address) Like "r*" 
  21. Or (STD.Address) Like "s*" 
  22. Or (STD.Address) Like "t*" 
  23. Or (STD.Address) Like "u*" 
  24. Or (STD.Address) Like "v*" 
  25. Or (STD.Address) Like "w*" 
  26. Or (STD.Address) Like "x*" 
  27. Or (STD.Address) Like "y*" 
  28. Or (STD.Address) Like "z*"
;

Change your code above to:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE STD.Address Like [Enter any search characters] & "*"  
the above will find all addresses begining with characters typed in the prompt box.

below will find all addresses that has the characters typed in the prompt box somewhere in the address.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE STD.Address Like "*" & [Enter any search characters] & "*"  
Jun 19 '08 #2
Big X
42
should the character be entered like this

[a,b,c,d,e, etc] seem to be getting an error atm still playing with it.

its saying enter a parameter value atm.
Jun 19 '08 #3
nico5038
3,080 Expert 2GB
Guess you're looking for:
Expand|Select|Wrap|Line Numbers
  1.       SELECT *
  2.       FROM STD
  3.       WHERE STD.Address Like "[a-z]*"
  4.  
The [ and ] mark a range of values for one character in the LIKE string.

Nic;o)
Jun 19 '08 #4
Big X
42
Guess you're looking for:
Expand|Select|Wrap|Line Numbers
  1.       SELECT *
  2.       FROM STD
  3.       WHERE STD.Address Like "[a-z]*"
  4.  
The [ and ] mark a range of values for one character in the LIKE string.

Nic;o)

I get no results using that code. It does not seem to be working.
Jun 19 '08 #5
Big X
42
I am using access 2003 if that matters.
Jun 19 '08 #6
Big X
42
got it working.

Had a space " a-z" which threw it out
Jun 19 '08 #7
nico5038
3,080 Expert 2GB
Glad you got the typo solved and that my query was correct :-)

Success with your application !

Nic;o)
Jun 19 '08 #8
Big X
42
This is my new full query which seems to be able to give me a list of about 20 out of 1818 records that has bad address details. I'm sure there is a better way of doing it I'm just messing around in between jobs :). Once I run the query I mark a column value with a 1 that I deem no good then run a query to get those and email them to the customer saying these customers have not been mailed. Any tips to improve would be appreciated :)

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE STD.Address Like "[a-z]" & "*" 
  4. And STD.Address Not Like "P O*" 
  5. And STD.Address Not Like "PO*"
  6. And STD.Address Not Like "P.O*" 
  7. And STD.Address Not Like "P.O.*"
  8. And STD.Address Not Like "LEVEL*" 
  9. And STD.Address Not Like "LVL*"
  10. And STD.Address Not Like "UNIT*" 
  11. And STD.Address Not Like "GPO*"
  12. And STD.Address Not Like "LOT*"
  13. And STD.Address Not Like "SUITE*"
  14. And STD.Address Not Like "SHOP*"
  15. And STD.Address Not Like "*" & "[0-9]" & "*"
  16. Or STD.State="VIC" And Postcode Not Like "3???"
  17. Or STD.State="TAS" And Postcode Not Like "7???"
  18. Or STD.State="NSW" And Postcode Not Like "2???"
  19. Or STD.State="WA" And Postcode Not Like "6???"
  20. Or STD.State="SA" And Postcode Not Like "5???"
  21. Or STD.State="QLD" And Postcode Not Like "4???"
  22. Or STD.State="NT" And Postcode Not Like "0???"
  23. Or STD.State="ACT" And Postcode Not Like "2???";
Jun 20 '08 #9
NeoPa
32,556 Expert Mod 16PB
I'm not sure if OR and AND in a SQL WHERE clause are applied in the same order or not. Whether or not they are, I would always recommend using parentheses to indicate this clearly to anyone reading the SQL. Similarly, for the table specification, if there is only the one, I would use the fieldnames alone (consistently) to make the SQL easier to read (as you have done with the [Postcode] field).
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM STD
  3. WHERE [Address] Like '[a-z]*'
  4.   AND [Address] Not Like 'P O*'
  5.   AND [Address] Not Like 'PO*'
  6.   AND [Address] Not Like 'P.O*'
  7.   AND [Address] Not Like 'LEVEL*'
  8.   AND [Address] Not Like 'LVL*'
  9.   AND [Address] Not Like 'UNIT*'
  10.   AND [Address] Not Like 'GPO*'
  11.   AND [Address] Not Like 'LOT*'
  12.   AND [Address] Not Like 'SUITE*'
  13.   AND [Address] Not Like 'SHOP*'
  14.   AND [Address] Not Like '*[0-9]*'
  15.    OR ([State]='VIC' AND [Postcode] Not Like '3???')
  16.    OR ([State]='TAS' AND [Postcode] Not Like '7???')
  17.    OR ([State]='NSW' AND [Postcode] Not Like '2???')
  18.    OR ([State]='WA' AND [Postcode] Not Like '6???')
  19.    OR ([State]='SA' AND [Postcode] Not Like '5???')
  20.    OR ([State]='QLD' AND [Postcode] Not Like '4???')
  21.    OR ([State]='NT' AND [Postcode] Not Like '0???')
  22.    OR ([State]='ACT' AND [Postcode] Not Like '2???')
I did try to get more information from Help on how Regular Expressions work in Jet SQL but I couldn't find it I'm afraid. Just the range ([A-D] or even [ABCD]) formats to indicate a single character matching any one of the listed items.

PS. I also removed line #3 from the following as it was in your original. It is covered by line #2 anyway.
Expand|Select|Wrap|Line Numbers
  1. ...
  2.   AND [Address] Not Like 'P.O*'
  3.   AND [Address] Not Like 'P.O.*'
  4. ...
Jun 20 '08 #10
puppydogbuddy
1,923 Expert 1GB
Here is list and explanation on how to use Sql wildcards, from the tip page of www.aadconsulting.com .

Access SQL Tip

Search for two or more single characters in a field. You can use the [ ] wildcard with the Like operator in your queries to search for two or more single characters in a field.

For example, suppose you want to find all customers with the following ZIP codes: 08052, 08053, or 08055. To use the [ ] wildcard, enter the following in your query's Criteria row under the ZIP Code field:

Like "0805[235]"

This expression searches for all field entries whose last character matches one of the characters specified between the brackets. Conversely, to search for all customers that don't live within these three ZIP code areas, place an exclamation point before the list, as shown below:

Like "0805[!235]"

The exclamation point inside the brackets stands for Not in the list. The query results will include all entries whose characters do not match any character in the list within the brackets.

You can combine the [ ] wildcard with any other wildcard character. For example, you can combine the * wildcard character with [ ] to search for any ZIP codes that begin with 0805, 0807, or 0808:

Like "080[578]*"
Jun 20 '08 #11
NeoPa
32,556 Expert Mod 16PB
Here is list and explanation on how to use Sql wildcards, from the tip page of www.aadconsulting.com.
...
Cheers pDog :)
Jun 23 '08 #12
NeoPa
32,556 Expert Mod 16PB
...
Conversely, to search for all customers that don't live within these three ZIP code areas, place an exclamation point before the list, as shown below:

Like "0805[!235]"
...
That would actually find all zip codes beginning "0805" EXCEPT "08052", "08053" or "08055" (I appreciate this came directly from AAD).

To search for all customers that don't live within these three ZIP code areas :
Not Like "0805[235]"

(Before you tell me I'm nit-picking - remember, that's my job :D)
Jun 23 '08 #13
puppydogbuddy
1,923 Expert 1GB
That would actually find all zip codes beginning "0805" EXCEPT "08052", "08053" or "08055" (I appreciate this came directly from AAD).

To search for all customers that don't live within these three ZIP code areas :
Not Like "0805[235]"

(Before you tell me I'm nit-picking - remember, that's my job :D)
Hi Adrian,
According to AAD, using the bang operator [! ] in the enclosed brackets makes the expression equivalent to "Not Like". Are you saying that is not true, or did you miss the part about the bang operator?

pDog
Jun 23 '08 #14
NeoPa
32,556 Expert Mod 16PB
I would say neither of those exactly pDog.

This is more of a nitty-gritty logic issue. The bang in this case means not - but only locally to the fifth character. The best way to illustrate this is probably with a Truth Table.
Expand|Select|Wrap|Line Numbers
  1. Text to     Like         Like        NOT Like
  2. check    '0805[235]'  '0805[!235]'  '0805[235]'
  3. -------  -----------  ------------  -----------
  4. '08050'     FALSE        TRUE         TRUE
  5. '08051'     FALSE        TRUE         TRUE
  6. '08052'     TRUE         FALSE        FALSE
  7. '08053'     TRUE         FALSE        FALSE
  8. '08054'     FALSE        TRUE         TRUE
  9. '08055'     TRUE         FALSE        FALSE
  10. '08056'     FALSE        TRUE         TRUE
  11. '08060'     FALSE        FALSE        TRUE
  12. 'FRED'      FALSE        FALSE        TRUE
  13. '08396'     FALSE        FALSE        TRUE
  14. 'Almost anything else...'
  15.             FALSE        FALSE        TRUE
PS. I notice I don't ACTUALLY give a direct answer to your question. I hope this is clear anyway. If not, just let me know and I will respond more directly.
Jun 24 '08 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: TEK | last post by:
Hello I'm wondering if anyone out there might give some input/suggestions/viewpoints around the Command pattern. In my case, the number one priority for using the pattern is undo support. Some...
8
by: Siemel Naran | last post by:
Hi. I'm writing a command shell that reads commands from standard input. At this point I have the command in a std::string. Now I want to execute this command in the shell. From the Borland...
2
by: Chris Bolus | last post by:
I'm a teacher using MS Access on an RMConnect 2.4 network. On some workstations both I and my students sometimes get an error message when attempting to insert a command button on a form which...
2
by: micahstrasser | last post by:
I have been trying for days to send a command to the command prompt through the shell() function in vb.net. For some reason it is not working. Here is the code: Private Sub Button1_Click(ByVal...
34
by: Roman Mashak | last post by:
Hello, All! I'm implementing simple CLI (flat model, no tree-style menu etc.). Command line looks like this: <command> <param1> <param2> ... <paramN> (where N=1..4) And idea is pretty simple: ...
13
by: Chris Carlen | last post by:
Hi: Having completed enough serial driver code for a TMS320F2812 microcontroller to talk to a terminal, I am now trying different approaches to command interpretation. I have a very simple...
0
by: czerwww | last post by:
Can someone please help me? I have class for database connection and I need set command.commandTimeout. How can I do that? Code: Imports System.Data.SqlClient Imports System.Data Public Class...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
51
by: Ojas | last post by:
Hi!, I just out of curiosity want to know how top detect the client side application under which the script is getting run. I mean to ask the how to know whether the script is running under...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.