Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Access 365 Query Not Pulling Exact Data In One Of The Fields In Query And Form

Posted by: hollyanneu2 Aug 21 2019, 04:08 PM

Hi I am trying to build a new multi Search form however this code is not helping me move forward...Seems like the last parameter in the where clause is not working right. There are 2 values in Status field - Active and Inactive. It's pulling both no matter what.


SELECT Employees.[First Name], Employees.[Last Name], Employees.Status
FROM Employees
WHERE (((Employees.[First Name]) Like "*" & [Forms]![MultiSearchForm]![FirstName] & "*")
AND ((Employees.[Last Name]) Like "*" & [Forms]![MultiSearchForm]![LastName] & "*")
OR ((Employees.Status)=[Forms]![MultiSearchForm]![Status]));

Thank you,
Holly

Posted by: tina t Aug 21 2019, 04:20 PM

SQL
WHERE (Employees.[First Name] Like "*" & [Forms]![MultiSearchForm]![FirstName] & "*" AND Employees.[Last Name] Like "*" & Forms]![MultiSearchForm]![LastName] & "*") OR Employees.Status=[Forms]![MultiSearchForm]![Status];

the above WHERE clause will return all records where the first name and last name matches the values in the name controls in the form OR where the status matches the value in the status control in the form. any record may meet both "sets" of criteria, or either "set" of criteria. a record that does not meet either "set" of criteria will not be returned.

if the above is what you want, you should be good to go. if you want something different, pls give details.

hth
tina

Posted by: hollyanneu2 Aug 21 2019, 04:44 PM

Yes, this is how I would code in MS SQL but in Access coding, especially with the parentheses invloved, is what drives me bonkers at times :/

I wrote exactly what you wrote and it fired a Syntax error in query expression. So not sure what else?

See attachment.

Thank you smile.gif

 

Posted by: hollyanneu2 Aug 21 2019, 04:51 PM

Actually, I fixed the syntax error....There was a missing bracket for Form...

SELECT Employees.[First Name], Employees.[Last Name], Employees.Status
FROM Employees
WHERE (Employees.[First Name] Like "*" & [Forms]![MultiSearchForm]![FirstName] & "*" AND Employees.[Last Name] Like "*" & [Forms]![MultiSearchForm]![LastName] & "*") OR Employees.Status=[Forms]![MultiSearchForm]![Status];

This is still pulling up both values for Status....







 

Posted by: orange999 Aug 21 2019, 05:58 PM

I think the issue is with you where clause

(Employees.[First Name] Like "*" & [Forms]![MultiSearchForm]![FirstName] & "*" AND Employees.[Last Name] Like "*" & [Forms]![MultiSearchForm]![LastName] & "*") OR Employees.Status=[Forms]![MultiSearchForm]![Status]

Seems like a bracketing problem.

Your clause boils down to ( A) or B and it seems the OR is appropriate in some instances.

Posted by: tina t Aug 21 2019, 09:02 PM

QUOTE
This is still pulling up both values for Status....

well, yes, that's entirely possible. the WHERE clause is treating the two "sets" of criteria as separate. so if you have a record that meets the first and last names criteria, then that record will show, regardless of what the status is. and if you have a record that meets the status criteria, then the record will show regardless of the first/last names criteria. that's what i said before:

QUOTE
the above WHERE clause will return all records where the first name and last name matches the values in the name controls in the form OR where the status matches the value in the status control in the form. any record may meet both "sets" of criteria, or either "set" of criteria. a record that does not meet either "set" of criteria will not be returned.

if the above is what you want, you should be good to go. if you want something different, pls give details.

so exactly what records do you want to see - in plain English, not SQL-speak?

hth
tina

Posted by: dale.fye Aug 22 2019, 05:41 AM

I think you just need to replace the OR with AND, but you will not see those people who match the first and last name search criteria, where the Status does not match your Status search criteria.

Posted by: hollyanneu2 Aug 22 2019, 10:56 AM

Our VB guy was able to code through VB coding to make it happen but we are trying to steer away from VB coding as not very many nowadays has the expertise at that level in case he leaves. This is a simple database.

There has to be a way to code this in Access SQL.

Thank you!







 

 

Posted by: tina t Aug 22 2019, 02:57 PM

well, probably there is a way for you to get what you want. but we can't help you get there if we don't know what you want.

QUOTE
so exactly what records do you want to see - in plain English, not SQL-speak?

this is the third time i've asked this question. if you've answered plainly and i somehow missed it, then my apologies and pls post your answer again.

hth
tina

Posted by: hollyanneu2 Aug 22 2019, 03:39 PM

I am not sure why you are not understanding this in plain english...

I am trying to do a search form using the search query to pull EITHER by firstname, lastname, or status. However the status has 2 values - Active and Inactive hence why I cannot use the LIKE for it.

Hope this clarifies...






Posted by: tina t Aug 22 2019, 04:08 PM

QUOTE
I am trying to do a search form using the search query to pull EITHER by firstname, lastname, or status.

okay. the SQL statement i posted, and you fixed (missing bracket), will do that. so it appears that the problem is not with the SQL statement. two questions:

1) what is the data type of the Status field in the table?

2) are you using a Lookup field for Status in the table?

hth
tina

Posted by: hollyanneu2 Aug 23 2019, 12:21 PM



1) what is the data type of the Status field in the table? Short Text

2) are you using a Lookup field for Status in the table? No

hth
tina

Posted by: tina t Aug 23 2019, 02:13 PM

hmm, well, i don't know about anyone else, but i'm stumped. if you want to make a copy of your db, remove any proprietary info from the copy, compact it, zip it, and upload it, also mention what version of Access it is, then somebody here can take a look and see if the problem can be spotted.

hth
tina


Posted by: JHolm Aug 23 2019, 03:48 PM

Try:

CODE
SELECT E.[First Name], E.[Last Name], E.Status
FROM (SELECT Employees.* FROM Employees WHERE Employees.Status Like [Forms]![MultiSearchForm]![Status] & "*")  AS E
WHERE (((E.[First Name]) Like "*" & [Forms]![MultiSearchForm]![First Name] & "*") And ((E.[Last Name]) Like "*" & [Forms]![MultiSearchForm]![Last Name] & "*"));


The sub query will filter your dataset so that only employees with the correct status need to be considered by the main query. That eliminates the issue you were having when you tried to use just the status. Basically, if both first name and last name were empty, the Like statements would both evaluate to True, so that side of your WHERE statement became True and it would always return all records. It didn't matter what you supplied for a status.

Jeff

Posted by: hollyanneu2 Aug 23 2019, 04:54 PM

we figured it out...

SELECT Employees.[First Name], Employees.[Last Name], Employees.Status
FROM Employees, Employees AS Employees_1
WHERE (((Employees.[First Name]) Like "*" & Forms!MultiSearchForm!FirstName & "*") And ((Employees.[Last Name]) Like "*" & Forms!MultiSearchForm!LastName & "*") And ((Employees.Status) Like Forms!MultiSearchForm!Status & "*"));

Thank you all smile.gif

Will come back for more once I have more questions!



Posted by: JHolm Aug 23 2019, 05:14 PM

Isn't that creating a Cartesian join and resulting in a lot more records than you should be getting?

Posted by: hollyanneu2 Aug 27 2019, 05:21 PM

I had to add "Distinct" to the select statement and that got rid of all the crazy duplicates.