Exclude any number in a field? 4

[tyleri]

I have a certain field that just happens to have 2 different datatypes in the same field. There was a mistake on the website and 2 different criteria were sent to the same field (not my fault).

The easiest way to sort these out: - one type has text with numbers, while the other type only has characters from the alphabet, there are NO numbers in the criteria.

So - I would like to know how it would be possible to make a query that only shows a field if it has NO numbers in it. Only characters from the alphabet.

Any ideas?

 

[Databaseguy]

=IIf(Val([YourField]>0,'Something,'SomethingElse)

Using the aboce, any field that has a number in it will return a numeric value. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[tyleri]

Cool that works for some of the data - but let me clarify my situation a little.

Company Names got mixed in with Addresses.

So - what I want this to show is this:

Field 1 Field 2

P.O. Box 2343 Address
1 United Drive Address
Company of Companies, Inc. Company

 

[Databaseguy]

I'm assuming this is in aquery.

Field2- Expr1:=IIf(Val([Field1]>0,"Address","Company&quot)

You can rename expr1 to whatever you want. That will be the field name displayed.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[tyleri]

Yeah that's exactly what I have - it only picks up fields with numbers at the beginning of the phrase for the "Address" selection - for the P.O. Box example it gives me "Company" Becasue it doesn't start with a number, but a letter... This probably can't be done, can it?

 

[tempclerk]

I don't know how, but I'm sure it can be done. Don't give up hope

 

[tlbroadbent]

You can do this to select only records that have no numerics in them.

Select * From table Where Field1 Not Like "*[0-9]*"

Or you can determine if the column is a company or address like this.

In the query designer grid
Field2: IIf([Field1] Like "*[0-9]*","Address","Company&quot

The SQL statement
Select Field1,
IIf([Field1] Like "*[0-9]*", "Address", "Company&quot As Field2
From Table Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Databaseguy]

Simplicity at it's best. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[tempclerk]

I was going to say "I'm sure tlbroadbent can tell you how."