UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access 365 Query Not Pulling Exact Data In One Of The Fields In Query And Form, Any Version    
 
   
hollyanneu2
post Aug 21 2019, 04:08 PM
Post#1



Posts: 9
Joined: 21-August 19



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
Go to the top of the page
 
tina t
post Aug 21 2019, 04:20 PM
Post#2



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
hollyanneu2
post Aug 21 2019, 04:44 PM
Post#3



Posts: 9
Joined: 21-August 19



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
Attached File(s)
Attached File  Coding_issue.JPG ( 23.73K )Number of downloads: 8
 
Go to the top of the page
 
hollyanneu2
post Aug 21 2019, 04:51 PM
Post#4



Posts: 9
Joined: 21-August 19



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....






Attached File(s)
Attached File  Status_issue.JPG ( 16.28K )Number of downloads: 1
 
Go to the top of the page
 
orange999
post Aug 21 2019, 05:58 PM
Post#5



Posts: 1,947
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


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.
This post has been edited by orange999: Aug 21 2019, 06:00 PM

--------------------
Good luck with your project!
Go to the top of the page
 
tina t
post Aug 21 2019, 09:02 PM
Post#6



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
dale.fye
post Aug 22 2019, 05:41 AM
Post#7



Posts: 160
Joined: 28-March 18
From: Virginia


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.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
hollyanneu2
post Aug 22 2019, 10:56 AM
Post#8



Posts: 9
Joined: 21-August 19



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!






Attached File(s)
Attached File  Option_1_Step_one.JPG ( 12.29K )Number of downloads: 0
Attached File  Option_1_Step_two.JPG ( 19.77K )Number of downloads: 0
Attached File  option_2_step_one.JPG ( 15.61K )Number of downloads: 0
Attached File  Option_3_step_one.JPG ( 21.64K )Number of downloads: 0
Attached File  Option_2_Step_two.JPG ( 14.86K )Number of downloads: 0
Attached File  Option_3_step_two.JPG ( 59.72K )Number of downloads: 0
 
Go to the top of the page
 
tina t
post Aug 22 2019, 02:57 PM
Post#9



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
hollyanneu2
post Aug 22 2019, 03:39 PM
Post#10



Posts: 9
Joined: 21-August 19



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...





Go to the top of the page
 
tina t
post Aug 22 2019, 04:08 PM
Post#11



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
hollyanneu2
post Aug 23 2019, 12:21 PM
Post#12



Posts: 9
Joined: 21-August 19





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
Go to the top of the page
 
tina t
post Aug 23 2019, 02:13 PM
Post#13



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


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


--------------------
"the wheel never stops turning"
Go to the top of the page
 
JHolm
post Aug 23 2019, 03:48 PM
Post#14



Posts: 138
Joined: 7-July 15
From: BC Canada


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
Go to the top of the page
 
hollyanneu2
post Aug 23 2019, 04:54 PM
Post#15



Posts: 9
Joined: 21-August 19



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!


Go to the top of the page
 
JHolm
post Aug 23 2019, 05:14 PM
Post#16



Posts: 138
Joined: 7-July 15
From: BC Canada


Isn't that creating a Cartesian join and resulting in a lot more records than you should be getting?
Go to the top of the page
 
hollyanneu2
post Aug 27 2019, 05:21 PM
Post#17



Posts: 9
Joined: 21-August 19



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


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 02:01 PM