{SOLVED} Access: Query Syntax

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Lotek

Thread Starter
Joined
Apr 6, 2001
Messages
32
I'm trying to write a query when you put the first few letters of a name or a loc. it brings up all names or loc that start with the coinciding letters. I can't figure out what to put down in the Criteria to do this
Any Help would be Appreciative.

Thanks
LoTek
 
Joined
Mar 12, 2001
Messages
912
Hi Lotek

You can use wildcards in the expression thus:

For instance, if you enter the expression Lo* it will bring up all entries begining Lo, such as Lotek, Look, Lowly etc.

You can use these wild cards in other ways too.

E.G. *in* would find all words containing 'in'; or 12/*/2000 would bring up all dates in December 2000 (American date format).

Is this what you mean?
 

Lotek

Thread Starter
Joined
Apr 6, 2001
Messages
32
an Example of what I'm trying to do is [enter name] in which when you run the query you receive a box that gives you a choice of a name to enter. but the problem is that you have to enter the full name and not just the first few letters. like LO to get lotek you have to enter the full name not just part. am I expaining this right or am I just babbaling again. I do tend to ramble sometimes.

Thanks
Lotek.
 
Joined
Oct 13, 2000
Messages
941
Right, and you need to use those wildcards (you can search Access help on "wildcard" for all the deets) in conjunction with the Like statement on the criteria line, so to find Lot, Lokeee, and Lod, you put
Code:
Like "Lo*"
Case does not matter.
 
Joined
Mar 12, 2001
Messages
912
Here's a copy of a list I keep on my desk of Access Query Wildcards and Expression Operators

? ..... The question mark is a wildcard that takes the place of a single letter.
* ..... The asterisk is the wildcard that represents a number of characters.
<..... e.g. <100 Value less than 100
>..... e.g. >=1 Value greater than or equal to 1
<> ..... e.g. <>"FL" Not equal to (all states besides Florida)
Between 1 and 10..... Numbers between 1 and 10
Is NullorIs Not Null..... Finds records with no value or all records that have a value
Like "a*" ..... All words beginning with "a"
>0 And <=10 ..... All numbers greater than 0 and less than 10
"Bob" Or "Jane"..... Values are Bob or Jane

LOL
 
Joined
Oct 13, 2000
Messages
941
Good, but I think it's important to point out to a newbie that there is a difference between the way expression operators are used, and the way that wildcard characters are. Your list is a mix of several kinds of criteria.

Expression operators are used all by themselves. In Mike's list those are >, <, and =. So for a number field, if you want all values under 100, you put "< 100" in the criteria line.

If you want simple criteria, you can just use the values themselves, as in the "Bob" or "Jane" example. Between x and y (numbers or dates) works the same way. Dates need # on either side of them, to identify them as date values to Access. e.g.
Code:
Between #1/01/01# and #1/31/01#
Is Null and Not Is Null (contains a value or is blank) are used on the criteria line just as you see them in this paragraph. They are used, obviouly, one at a time.

The wildcard characters ? and * use Like. And there are more wildcard characters you can see in Access help.
 
Joined
Oct 29, 2001
Messages
2,056
have you loaded and looked at the northwoods (northwinds) sample database that comes with access? believe it's also available at the MS site

has those sort of forms, subforms, sort of like the autocomplete feature in IE and other MS products.

i've 'borrowed' code from that dbase many times - it works
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top