473,838 Members | 1,706 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 #1
10 1657
32,584 Recognized Expert Moderator MVP
I'd love to help, but you don't explain the data structure of any tables you are probably using (You haven't explained that either).

Clearly you've put some effort into the question, but unfortunately left out some critical info, without which it's really hard to understand what you're trying to ask for.
Feb 24 '12 #2
6 New Member
I was trying not to write a book, and I'm not sure what exactly you need to know, but here's everything I know:

There's only one table in use here.
There's an AutoID, BoxBarcode, and FileBarcode for each record. Some records have a ChildBarcode. A few records have multiple barcodes separated by a comma and space in the ChildBarcode field.
I'm pretty sure the fields are text.

There's a query with the AutoID, BoxBarcode, FileBarcode, and ChildBarcode fields. The last field in the query has the code above.

There's a subform that has the BoxBarcode, FileBarcode, and ChildBarcode fields.

There's a form, Policy_File_Inv entory, that has the [Frame 4] that has 4 options. The form also has the [BarcodeNumber] unbound field that users input data in. And the form displays the subform.

The first option is to enter new records. When this option is selected, no records initially appear in the subform. The user enters a BoxBarcode into the BarcodeNumber field in the form and the cursor moves to the FileBarcode field in the subform. As FileBarcodes are entered, new records are created, each with the same BoxBarcode.

The second option is to search the BoxBarcode field for any records matching the string entered in the BarcodeNumber field in the form.

The third option is to display all the records in the table.

The fourth option is to search the FileBarcode field or ChildBarcode field for any records matching the string entered in the BarcodeNumber field in the form.

As it is, if the ChildBarcode field for a record is "12345" and I enter "12345", it returns the record I want.
But if the ChildBarcode field for a record is "12345, 54321", I want to be able to pull up that record when I search for "12345" and when I search for "54321"... and I have no idea how to do that.
Feb 24 '12 #3
6 New Member
This was suggested as a replacement for the end of my code:
Expand|Select|Wrap|Line Numbers
  1. OR InStr([ChildBarcode],[Forms]![Policy_File_Inventory]![BarcodeNumber]),"")))
I am going to give it a try tomorrow morning.
Feb 24 '12 #4
32,584 Recognized Expert Moderator MVP
I was trying not to write a book, and I'm not sure what exactly you need to know
I sympathise with the idea, but your latest post (** Edit - Referring to post #3 **) is a pretty concise version of the basic data required. It seems you have a pretty good idea of what's needed after all, even if you didn't necessarily appreciate that at the time :-)

Your posted code is well displayed, but I assume it's in the WHERE clause of your SQL (as that bit's still not absolutely clear). On that assumption I'll suggest a replacement to handle your need (which is now very clear) :
Expand|Select|Wrap|Line Numbers
  1. CHOOSE([Forms]![Policy_File_Inventory]![Frame4],
  2.        ([BoxBarcode] Is Null),
  3.        ([BoxBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber]),
  4.        (True),
  5.        (([FileBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber])
  6.     OR ([ChildBarcode] Like '*' & [Forms]![Policy_File_Inventory]![BarcodeNumber] & '*')))
I've simplified your original somewhat, by using CHOOSE(), and used Like with wildcards for matching the [ChildBarcode].
Feb 24 '12 #5
32,584 Recognized Expert Moderator MVP
This was suggested as a replacement for the end of my code:
That could work, but is not a SQL-based approach. I wouldn't recommend it, especially as there is a more appropriate approach available (See post #5).

PS. We cross-posted so I didn't see yours until after I'd submitted mine.
Feb 24 '12 #6
6 New Member
No SQL as far as I know.

The code I posted is in the Field row of the last column of my query.
Feb 24 '12 #7
32,584 Recognized Expert Moderator MVP
No SQL as far as I know.
Understandable, but it is all based on SQL. See Extracting/Updating SQL from a QueryDef.

You can put the SQL I posted in post #5 in that field in your query and it should work. I guess you simply have True in the Criteria row. That makes sense.
Feb 24 '12 #8
6 New Member
I tried the non-SQL yesterday morning and it worked.

I didn't have the chance to try the SQL. I will give that a try Monday morning.
This won't be going into production until Tuesday, so I still have a chance to see what works best.

I will report back then and choose the best answer.
Feb 25 '12 #9
6 New Member
I guess you simply have True in the Criteria row.
The criteria is actually <>False which I don't quite understand, but it works like that. And I suppose now is a good time to figure out what it means.
Feb 25 '12 #10

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: 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: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
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: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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.