SQL Command

[Big X]

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.

SELECT *
FROM STD
WHERE STD.Address Like "a*" 
Or (STD.Address) Like "b*" 
Or (STD.Address) Like "c*" 
Or (STD.Address) Like "d*" 
Or (STD.Address) Like "e*" 
Or (STD.Address) Like "f*" 
Or (STD.Address) Like "g*" 
Or (STD.Address) Like "h*" 
Or (STD.Address) Like "i*" 
Or (STD.Address) Like "j*" 
Or (STD.Address) Like "k*" 
Or (STD.Address) Like "l*" 
Or (STD.Address) Like "m*"
Or (STD.Address) Like "n*" 
Or (STD.Address) Like "o*" 
Or (STD.Address) Like "p*" 
Or (STD.Address) Like "q*" 
Or (STD.Address) Like "r*" 
Or (STD.Address) Like "s*" 
Or (STD.Address) Like "t*" 
Or (STD.Address) Like "u*" 
Or (STD.Address) Like "v*" 
Or (STD.Address) Like "w*" 
Or (STD.Address) Like "x*" 
Or (STD.Address) Like "y*" 
Or (STD.Address) Like "z*"

;

[puppydogbuddy]

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.

SELECT *
FROM STD
WHERE STD.Address Like "a*" 
Or (STD.Address) Like "b*" 
Or (STD.Address) Like "c*" 
Or (STD.Address) Like "d*" 
Or (STD.Address) Like "e*" 
Or (STD.Address) Like "f*" 
Or (STD.Address) Like "g*" 
Or (STD.Address) Like "h*" 
Or (STD.Address) Like "i*" 
Or (STD.Address) Like "j*" 
Or (STD.Address) Like "k*" 
Or (STD.Address) Like "l*" 
Or (STD.Address) Like "m*"
Or (STD.Address) Like "n*" 
Or (STD.Address) Like "o*" 
Or (STD.Address) Like "p*" 
Or (STD.Address) Like "q*" 
Or (STD.Address) Like "r*" 
Or (STD.Address) Like "s*" 
Or (STD.Address) Like "t*" 
Or (STD.Address) Like "u*" 
Or (STD.Address) Like "v*" 
Or (STD.Address) Like "w*" 
Or (STD.Address) Like "x*" 
Or (STD.Address) Like "y*" 
Or (STD.Address) Like "z*"

;

Change your code above to:

SELECT *
FROM STD
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.

SELECT *
FROM STD
WHERE STD.Address Like "*" & [Enter any search characters] & "*"

[Big X]

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.

[nico5038]

Guess you're looking for:

      SELECT *
      FROM STD
      WHERE STD.Address Like "[a-z]*"

The [ and ] mark a range of values for one character in the LIKE string.

Nic;o)

[Big X]

Guess you're looking for:

      SELECT *
      FROM STD
      WHERE STD.Address Like "[a-z]*"

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.

[Big X]

I am using access 2003 if that matters.

[Big X]

got it working.

Had a space " a-z" which threw it out

[nico5038]

Glad you got the typo solved and that my query was correct :-)

Success with your application !

Nic;o)

[Big X]

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 :)

SELECT *
FROM STD
WHERE STD.Address Like "[a-z]" & "*" 
And STD.Address Not Like "P O*" 
And STD.Address Not Like "PO*"
And STD.Address Not Like "P.O*" 
And STD.Address Not Like "P.O.*"
And STD.Address Not Like "LEVEL*" 
And STD.Address Not Like "LVL*"
And STD.Address Not Like "UNIT*" 
And STD.Address Not Like "GPO*"
And STD.Address Not Like "LOT*"
And STD.Address Not Like "SUITE*"
And STD.Address Not Like "SHOP*"
And STD.Address Not Like "*" & "[0-9]" & "*"
Or STD.State="VIC" And Postcode Not Like "3???"
Or STD.State="TAS" And Postcode Not Like "7???"
Or STD.State="NSW" And Postcode Not Like "2???"
Or STD.State="WA" And Postcode Not Like "6???"
Or STD.State="SA" And Postcode Not Like "5???"
Or STD.State="QLD" And Postcode Not Like "4???"
Or STD.State="NT" And Postcode Not Like "0???"
Or STD.State="ACT" And Postcode Not Like "2???";

[NeoPa]

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).

SELECT *
FROM STD
WHERE [Address] Like '[a-z]*'
  AND [Address] Not Like 'P O*'
  AND [Address] Not Like 'PO*'
  AND [Address] Not Like 'P.O*'
  AND [Address] Not Like 'LEVEL*'
  AND [Address] Not Like 'LVL*'
  AND [Address] Not Like 'UNIT*'
  AND [Address] Not Like 'GPO*'
  AND [Address] Not Like 'LOT*'
  AND [Address] Not Like 'SUITE*'
  AND [Address] Not Like 'SHOP*'
  AND [Address] Not Like '*[0-9]*'
   OR ([State]='VIC' AND [Postcode] Not Like '3???')
   OR ([State]='TAS' AND [Postcode] Not Like '7???')
   OR ([State]='NSW' AND [Postcode] Not Like '2???')
   OR ([State]='WA' AND [Postcode] Not Like '6???')
   OR ([State]='SA' AND [Postcode] Not Like '5???')
   OR ([State]='QLD' AND [Postcode] Not Like '4???')
   OR ([State]='NT' AND [Postcode] Not Like '0???')
   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.

...
  AND [Address] Not Like 'P.O*'
  AND [Address] Not Like 'P.O.*'
...

[puppydogbuddy]

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]*"

[NeoPa]

Here is list and explanation on how to use Sql wildcards, from the tip page of www.aadconsulting.com.
...

Cheers pDog :)

[NeoPa]

...
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)

[puppydogbuddy]

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

[NeoPa]

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.

Text to     Like         Like        NOT Like
check    '0805[235]'  '0805[!235]'  '0805[235]'
-------  -----------  ------------  -----------
'08050'     FALSE        TRUE         TRUE
'08051'     FALSE        TRUE         TRUE
'08052'     TRUE         FALSE        FALSE
'08053'     TRUE         FALSE        FALSE
'08054'     FALSE        TRUE         TRUE
'08055'     TRUE         FALSE        FALSE
'08056'     FALSE        TRUE         TRUE
'08060'     FALSE        FALSE        TRUE
'FRED'      FALSE        FALSE        TRUE
'08396'     FALSE        FALSE        TRUE
'Almost anything else...'
            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.