473,839 Members | 1,468 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I return search results for a field with multiple values?

6 New Member
I'm using Access 2002.

I have a query field set up that references a form, Policy_File_Inv entory, that has an unbound field, BarcodeNumber.

The form has several options related to the BarcodeNumber field. I am interested in what happens when the fourth option is selected. (The first and second selections work fine as is. The third option just displays all records.)

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![Policy_File_Inventory]![Frame4]=1,[BoxBarcode] Is Null,
  2. IIf([Forms]![Policy_File_Inventory]![Frame4]=2,[BoxBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber],
  3. IIf([Forms]![Policy_File_Inventory]![Frame4]=4,[FileBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber] OR [ChildBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber],"")))
The code I currently have here returns the results I want when the FileBarcode or ChildBarcode equals what is input into the BarcodeNumber field. The problem is that, in some cases, the ChildBarcode field has multiple values.

Is there some way I can make the search work when searching for one value listed in the ChildBarcode field for a record that has multiple values in the ChildBarcode field?

Note: I didn't set up this code. I just copied and pasted from another similar query and changed the field names to work with my new query and form. I also added the bit with the "OR [ChildBarcode]..." to make the fourth option search the ChildBarcode field in addition to the FileBarcode field.
I'm trying to figure this out myself and it isn't going very well.
Feb 23 '12
10 1658
32,584 Recognized Expert Moderator MVP
Ha! That makes sense. I didn't realise Access was that clever.

I'll explain why, but many, many people don't get Booleans so don't worry if you don't either.

Any value that is not zero will trigger a true branch. Tests are done in various places and ways and, fundamentally, they all come down to :
Expand|Select|Wrap|Line Numbers
  1. IF <Boolean> THEN <TrueOption> ELSE <FalseOption>
  1. As Booleans are stored as integers, it's possible for any value valid integer value to be treated as a Boolean.
  2. True = -1 (all 1s in binary) and False = 0 (all 0s in binary).
This leaves the question of values which are neither. These are treated as triggering the <TrueOption> when tested, yet are actually equal to neither True nor False. How, then, to test them in SQL if you must compare them with something (as this is all the Query grid allows)? The answer is to ensure they are not False, or :
Expand|Select|Wrap|Line Numbers
  1. <>False
Feb 26 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Gabriel Cooper | last post by:
Usually when I access db search results it's something like this: cursor.execute("select A1,A2,A3,A4 from B where C") for (a1,a2,a3,a4) in cursor.fetchall(): stuff() But sometimes the point at which I use the data returned is not with the search, and so having the ability to access the results as a dictionary is more useful. I know that with MySQLdb I can open the connection and have it return a dictionary, but when only one search...
by: dave | last post by:
I am wondering if the following can be done. I want to setup a search page that utilizes full text searching in sql2000. I want the user to type in say "where is bill" and have the query search across multiple columns in multiple tables and return a single list of results so that i can show unique records that are returned. The problem is that my data for 1 record is split across multiple tables so that the search has to occur in each
by: Craig Keightley | last post by:
I have the following array: function showCPUs(){ //name brandID dualCore dualProcessor var cpuItem=; var cpuItem=; var cpuItem=; var cpuItem=; var cpuItem=; var cpuItem=; var cpuItem=;
by: macyp | last post by:
I have to pass values from one aspx page to another. The controls I have in the first page are: a textbox, 3 drop down lists, and 2 check boxes, and a submit button. It is a search page, and the users need not enter values in all the controls. they can leave the textbox blank, and select values from one drop down, or any other combinations. I am trying to pass values with the help of session variables. But I have multiple if else...
by: Rosanne Rohana | last post by:
I'm trying for return user info (first name, last name, etc.) from a Netscape 4.16 LDAP server using the System.DirectoryServices. I'm able to get authenticated successfully, but when I attempt to return search results nothing ever comes back. For example, "result = searcher.FindOne()" always returns "nothing" and the "searcher.FindAll" collection always return a count of zero. I've tried many different filters and PropertiesToLoad. Some...
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table like so... Table 1 Field1 Field2 Field3 E1 April 2006 AA, BB, CC E2 April 2006 AA, BB, CC,DD, EE E3 April 2006 AA, BB
by: PI | last post by:
Hi guys, I'm struggling with this much longer than I think I need to, I guess you could help me here: How do I make the results of a search display on the same page as the search, sort of beneath the 'submit' button? Thanks for helping out. Regards, Ire
by: cssExp | last post by:
Here in a form i have the following. note: the following is a result of dynamic html element creation. <input type="hidden" name="category" value="science" /> <input type="hidden" name="permission" value="normal" /> <input type="hidden" name="category" value="clubs" /> <input type="hidden" name="permission" value="vip" /> <input type="hidden" name="category" value="cartoon" /> <input type="hidden" name="permission" value="standard" />
by: Jonathan | last post by:
I have a SQL stored procedure for adding a new record in a transactions table. It also has two return values: CounterID and IDKey. I want to create a webservice that accepts the 10 input parameters and returns the two return values. My C# programmer here says that webservice methods can only return 1 value per method. Is that right? Though I haven't ever created a webservice, I would have thought that a method could return a whole lot...
by: darkestknight43 | last post by:
Hello, I've been working on this piece of code for quite a while. Essentially it is a Search Tool which pulls Mobile Home information from a database and displays listings based on the users input. When the results page attempts to acquire variables from the Search Tool, it only displays results based off of a single variable, not all of them, which needless to say doesn't give the correct listings. Here is an example snippet of the code I'm...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.