A better way to look for 'A' thru 'M'?

G

Guest

Hello,

I have the following query:

SO CA: IIf([All Programs Quoted crosstab].[State]="CA" And [All Programs
Quoted crosstab].[Zip_Code]<"94000" And ([All Programs Quoted
crosstab].[Broker_Name] Like "a*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "b*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "c*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "d*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "e*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "f*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "g*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "h*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "i*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "j*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "k*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "l*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "m*"),"John McHugh","Michelle Kirk")

It looks for Brokers below a certain Zip Code in California whose last names
begin with A thru M to assign to correct Broker Manager. Is there a more
efficient query statement to search the first letter of the Last Name rather
than using 13 different 'Like' statements?

Thanks.
 
J

John W. Vinson

It looks for Brokers below a certain Zip Code in California whose last names
begin with A thru M to assign to correct Broker Manager. Is there a more
efficient query statement to search the first letter of the Last Name rather
than using 13 different 'Like' statements?

Thanks.
Click to expand...

Yes: LIKE "[A-M]*"

John W. Vinson [MVP]
 
K

Ken Snell \(MVP\)

SO CA: IIf([All Programs Quoted crosstab].[State]="CA" And [All Programs
Quoted crosstab].[Zip_Code]<"94000" And ([All Programs Quoted
crosstab].[Broker_Name] Like "[a-m]*","John McHugh","Michelle Kirk")
 
B

BruceM

See Help for information about the Like operator. In a query, your criteria
for the LastName field would be:
Like "[A-M]*"
The criteria for Zip would be the number of your choosing. You could have
[Enter Zip Code] if you want the user to choose.
If you want to specify the letter range when you run the query, the criteria
could be something like:
Like "[ " & [First Letter] & "-" & [Last Letter] & "]*"
A form or report based on the query will prompt for the criteria.
 
M

Marshall Barton

Pat said:
I have the following query:

SO CA: IIf([All Programs Quoted crosstab].[State]="CA" And [All Programs
Quoted crosstab].[Zip_Code]<"94000" And ([All Programs Quoted
crosstab].[Broker_Name] Like "a*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "b*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "c*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "d*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "e*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "f*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "g*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "h*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "i*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "j*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "k*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "l*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "m*"),"John McHugh","Michelle Kirk")

It looks for Brokers below a certain Zip Code in California whose last names
begin with A thru M to assign to correct Broker Manager. Is there a more
efficient query statement to search the first letter of the Last Name rather
than using 13 different 'Like' statements?
Click to expand...


Jet SQL allows:

Quoted crosstab].[Zip_Code]<"94000"
And ([All Programs Quoted crosstab].[Broker_Name]
Like "[a-m]*"
 
G

Guest

Thanks, Marshall, Bruce, Jeff, Ken, and John!
--
Pat Dools


Marshall Barton said:
Pat said:
I have the following query:

SO CA: IIf([All Programs Quoted crosstab].[State]="CA" And [All Programs
Quoted crosstab].[Zip_Code]<"94000" And ([All Programs Quoted
crosstab].[Broker_Name] Like "a*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "b*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "c*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "d*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "e*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "f*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "g*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "h*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "i*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "j*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "k*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "l*" Or [All Programs Quoted
crosstab].[Broker_Name] Like "m*"),"John McHugh","Michelle Kirk")

It looks for Brokers below a certain Zip Code in California whose last names
begin with A thru M to assign to correct Broker Manager. Is there a more
efficient query statement to search the first letter of the Last Name rather
than using 13 different 'Like' statements?
Click to expand...


Jet SQL allows:

Quoted crosstab].[Zip_Code]<"94000"
And ([All Programs Quoted crosstab].[Broker_Name]
Like "[a-m]*"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AVG Function in a Query 5
Best way to show a crosstab in gridview ( m x n table of checkboxes) 0
Is there a way to close all icon tray programs w/ macro or cmd? 1
Clueless: Creating a "Score card" to track how many research harboring calls are made 3
Is there a way as an administrator in a domain to allow a standard user not to need credentials for 1
product or way to disable xp from modifying file Last Modified Dates for daylight savings time? 1
Best way to switch to single user W/O passwords?? 2
Request for help deleting private data from a new Windows XP computer I have to return 10

Top