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
> Search For Multiple Entries Using Lookup, Access 2016    
 
   
cstuckey
post Aug 22 2019, 01:08 PM
Post#1



Posts: 3
Joined: 22-August 19



Hello

I'm trying to search for two cities, not just one. I made a lookup for qry.cityLookup. I want to search for two cities. How can I do that? I have done this a while back but forgot how I did this.

This what I made.


Lookup
Field = City
Table = Contacts
Criteria:
Like "*" & [Enter City] & "*"

Thanks
C

Go to the top of the page
 
MadPiet
post Aug 22 2019, 01:16 PM
Post#2



Posts: 3,356
Joined: 27-February 09



You can't look for two cities with a single lookup.

Got some context?

What are you trying to accomplish?
Go to the top of the page
 
cstuckey
post Aug 22 2019, 01:28 PM
Post#3



Posts: 3
Joined: 22-August 19



Ok, how can I change the lookup by search one or my cities? I want lookup anything in Highlands and Cashiers. How can I do that without Having to change the query in design view? see attached.
Attached File(s)
Attached File  CityLookup_example.PNG ( 30.9K )Number of downloads: 3
 
Go to the top of the page
 
projecttoday
post Aug 22 2019, 01:34 PM
Post#4


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


You want it to prompt you more than once?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Aug 22 2019, 01:38 PM
Post#5



Posts: 3,356
Joined: 27-February 09



If you're doing an OR search, you can just

SELECT *
FROM Contacts c1
WHERE c1.City = [Enter a city;]
OR c1.City = [Enter Another city:]

If you're trying to do an AND (Where a contact is listed on two cities) then that's a different animal.
Go to the top of the page
 
cstuckey
post Aug 22 2019, 01:39 PM
Post#6



Posts: 3
Joined: 22-August 19



Do that or I can enter two cities. example: Enter City: Cashiers or highlands to pull up all records in those two cities.
Go to the top of the page
 
MadPiet
post Aug 22 2019, 01:42 PM
Post#7



Posts: 3,356
Joined: 27-February 09



You mean enter "Cashiers or Highlands" into the inputbox that you get prompted with and get an answer? I don't think so, because it will evaluate the expression as

SELECT <field list>
FROM <table name>
WHERE City = "Cashiers or Highlands"

Which is not at all the same as
SELECT <field list>
FROM <table name>
WHERE City IN ("Cashiers", "Highlands")

This was the only way I could get the query to allow 2 user prompts for the same query on the same column:
SELECT FixedUserSoftware.Username, FixedUserSoftware.SoftwareTitle
FROM FixedUserSoftware
WHERE (((FixedUserSoftware.Username) Like [First name #1:] & "*")) OR (((FixedUserSoftware.Username) Like [First name #2:] & "*"));

This post has been edited by MadPiet: Aug 22 2019, 01:54 PM
Go to the top of the page
 
projecttoday
post Aug 22 2019, 01:43 PM
Post#8


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


There's a way to do that. I think you use the IN operator but I'm not sure.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Aug 22 2019, 02:06 PM
Post#9



Posts: 3,356
Joined: 27-February 09



SELECT <field list>
FROM <Table>
WHERE City = [Enter first city:]
OR City = [Enter second city:];

works...
Go to the top of the page
 
projecttoday
post Aug 22 2019, 02:49 PM
Post#10


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


There's a more dynamic way. You can enter as many cities as you want. When you're done you just click Enter. Maybe WHERE City IN ([Enter City]).

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Aug 22 2019, 03:08 PM
Post#11



Posts: 3,356
Joined: 27-February 09



That doesn't make sense to me, because isn't the

Item IN('item1','item2','item3') rewritten as
Item = 'item1' OR Item = 'item2' OR Item = 'item3' ?

I thought the parser just wrapped everything in the inputbox in quotes and then submitted it to the engine.
Go to the top of the page
 
projecttoday
post Aug 22 2019, 03:15 PM
Post#12


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


I don't remember the exact syntax but I know it's possible. I did a post here about it in 2004, I think.

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


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 03:59 PM