Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
truncated results!!!!
- Thread starter jgeneie
- Start date
Hi
Do not know if this applies (I was having a tough time following all the dialog in your post).
In the Query designer, Access will usually hose a mixture of and/or conditions. As the other folk explained, you have to go to the SQL statement (being generated by the designer) to correct this. All you have to do is EDIT the WHERE clause, not re-write the entire statement. (By the way, if you intend to work professionally with databases, you better start learning about them, and knowing SQL is an absolute requirement).
OK. So, in almost every instance, you need to evaluate all your OR conditions first, and then your AND's.
Col1 = "Ann" or Col1 = "Mary" and Col2 = "Johnson".
This returns all Ann/Mary Johnsons
Col1 = "Ann" and Col2 = "Johnson" or Col1 = "Mary".
This returns all "Ann Johnsons" and every "Mary" (regardless of last name). This is what the query designer produces most of the time (not what you intended).
The way to fix it is to go directly to the SQL statement and edit it.
Note: Access will add a bunch of unnecessary (())) stuff to the where clause. I usually delete it for simplicity (although Access adds it back in).
Do not know if this applies (I was having a tough time following all the dialog in your post).
In the Query designer, Access will usually hose a mixture of and/or conditions. As the other folk explained, you have to go to the SQL statement (being generated by the designer) to correct this. All you have to do is EDIT the WHERE clause, not re-write the entire statement. (By the way, if you intend to work professionally with databases, you better start learning about them, and knowing SQL is an absolute requirement).
OK. So, in almost every instance, you need to evaluate all your OR conditions first, and then your AND's.
Col1 = "Ann" or Col1 = "Mary" and Col2 = "Johnson".
This returns all Ann/Mary Johnsons
Col1 = "Ann" and Col2 = "Johnson" or Col1 = "Mary".
This returns all "Ann Johnsons" and every "Mary" (regardless of last name). This is what the query designer produces most of the time (not what you intended).
The way to fix it is to go directly to the SQL statement and edit it.
Note: Access will add a bunch of unnecessary (())) stuff to the where clause. I usually delete it for simplicity (although Access adds it back in).
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
