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
> Using Textbox As Query Criteria, Access 2016    
 
   
tubadave17
post Nov 6 2019, 02:23 AM
Post#1



Posts: 5
Joined: 21-April 19



Hi all,

I'm trying to use a textbox in a form as criteria for a query and it does not work when I used the "Or" command between items. If i enter one value, it works fine. I'm using this textbox to select multiple first names from a database of employees.

If I enter David in the textbox, the query runs fine. But if I enter David Or Paula, "David" Or "Paula", etc. it does not work.

Can anyone send me in the right direction?

Many thanks in advance.
Go to the top of the page
 
June7
post Nov 6 2019, 03:37 AM
Post#2



Posts: 1,035
Joined: 25-January 16



Anything entered into textbox is just a single string of characters, including quote marks and the word OR. Query object could use LIKE and wildcards:

SELECT * FROM tablename WHERE Forms!formname!textboxname LIKE "*" & [fieldname] & "*";

or InStr()

SELECT * FROM tablename WHERE InStr(Forms!formname!textboxname, [fieldname])>0;

Review http://allenbrowne.com/ser-62.html, especially the link to additional guidance on using multi-select listbox
This post has been edited by June7: Nov 6 2019, 04:33 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Nov 6 2019, 04:28 AM
Post#3


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


WHERE personname = "Daniel" OR "Paula"
is not valid in Access SQL. You have to use
WHERE personname = "Daniel" OR personname = "Paula"
Another possibility is
WHERE personname IN ("Daniel", "Paula")
Still another possibility, and a good way, especially if you have a lot of names that aren't related in any way, is to populate a temp table with the names and inner join this table into your query. You could use a subform for this.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Nov 6 2019, 04:30 AM
Post#4


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


If Daniel and Paula are related in some way, say they're in the same department, have you considered selecting by department instead of name?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Nov 6 2019, 09:15 AM
Post#5


UA Admin
Posts: 36,203
Joined: 20-June 02
From: Newcastle, WA


When you have a filtering task that requires ONE or MORE choices, a text box is not usually the best control to use. As a matter of fact, I seldom use a text box for such tasks anyway, if I know the filtering is to be done against a closed domain.

A Multi-Select Listbox is a better option for such filtering.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tubadave17
post Nov 6 2019, 11:21 AM
Post#6



Posts: 5
Joined: 21-April 19



Thanks everyone for the quick replies. I configured an WHERE Instr() statement and my query is working perfectly now.
Go to the top of the page
 
projecttoday
post Nov 6 2019, 11:39 AM
Post#7


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 10:17 AM