Access Find ind in a query 2

[klmorgan]

I have set up a system and have used some Parameter queries but am stumped how to make a query that would find say the first three letters on an occurrance of three letters in a field.

ie. in a Surname field put into a parameter query Mor and it find all the Morgans, Morris etc.
The way the parameter query seems to work you have to enter Morgan to get Morgan as Morg would find no records.
I'm sure I must be missing something simple!

I think it is somthing like LIKE I need but I have not got my head around sequal or VBA yet.

Regards

Keith

 

[lespaul]

SELECT * FROM TableName where LastName LIKE "MOR*"

LIKE works in this manner:

LIKE AC* finds all values in the field that START with AC
Access
Account

LIKE *AC finds all values in the field that END with AC
Lilac
Prozac

LIKE *AC* finds all values in the field that CONTAIN AC
Access
Account
pack
back
Lilac
Prozac

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[klmorgan]

Leslie the sequal for a one query is:

SELECT Customers.ContactID, Customers.FirstName, Customers.LastName, Customers.KeyNum, Customers.[Print Flag], Customers.YearOfManu, Customers.UnitType, Customers.RentalPeriod
FROM Customers
WHERE (((Customers.ContactID)=[Enter Customer ID Number]));

Would I put LIKE in place of equals in the last sentance?

Regards

Keith

P.S. The sequal wrote itself from me using the query wizard I'm not that clever.....Yet!

 

[PHV]

in a Surname field put into a parameter query Mor and it find all the Morgans, Morris etc
Something like this ?
SELECT ContactID, FirstName, LastName, KeyNum, [Print Flag], YearOfManu, UnitType, RentalPeriod
FROM Customers
WHERE Surname Like [Enter Customer Surname] & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[klmorgan]

Brillient PH

Works LIKE a dream!

I think it will be a key for me to start unravelling Sequel too; or perhaps that is only a pipe dream!

Many Thanks

Keith

 

[lespaul]

read the Understanding SQL Join article linked below, it will help in unraveling SQL!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[klmorgan]

Thanks Leslie

I have downloaded the first and bookmarked the second and I will read through them.

I'm retired now so I have some time!

I used to be quiet good in a Dos database called TAS that was used to create the Small Business System TAS Books, it was a reasonably priced database with a lot of power.

Unfortunately the Tas language did not get transferred across to the Windows format and due to my company constraints on software, I became quite smokey at Excel and later its use in producing reporting out of Oracle via Discoverer. I never really got into sequal or Access so I'm trying to cut my teeth now.

Thanks for your help

This is a great community in TekTips and I think with all the problems there are in the world today it is a beacon of how people really help each other.

Kind Regards

Keith