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

[m3g4tr0n]

I'm using Access 2002.

I have a query field set up that references a form, Policy_File_Inventory, 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.)

IIf([Forms]![Policy_File_Inventory]![Frame4]=1,[BoxBarcode] Is Null,
IIf([Forms]![Policy_File_Inventory]![Frame4]=2,[BoxBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber],
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.

[NeoPa]

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.

[m3g4tr0n]

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

[m3g4tr0n]

This was suggested as a replacement for the end of my code:

OR InStr([ChildBarcode],[Forms]![Policy_File_Inventory]![BarcodeNumber]),"")))

I am going to give it a try tomorrow morning.

[NeoPa]

M3g4tr0n:
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) :

CHOOSE([Forms]![Policy_File_Inventory]![Frame4],
       ([BoxBarcode] Is Null),
       ([BoxBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber]),
       (True),
       (([FileBarcode]=[Forms]![Policy_File_Inventory]![BarcodeNumber])
    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].

[NeoPa]

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

[m3g4tr0n]

No SQL as far as I know.

The code I posted is in the Field row of the last column of my query.

[NeoPa]

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

[m3g4tr0n]

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.

[m3g4tr0n]

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.

[NeoPa]

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 :

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 :

<>False