How can you search for a specific character from a string.

[Sly600rr]

Hello,
man.. this just keeps getting deeper and deeper..
Okay, heres what I'm trying to do...

I have multiple numbers comma separated in a feild for my table
(EI) 1,2,3,4,5,6

I have a search form building a filter for a query and I'm showing the matching records in a subform

What I want to do is, If I search for "1" it show that record, and if a search for "2" or even "6" it show that record as well..

here's the code now..

      ' search for Non Conforming
    If Me.cmbNCcodeID > 0 Then
        varWhere = varWhere & "[nccodeID] LIKE """ & Me.cmbNCcodeID & "*"" AND "
    End If

Is there an easy way to do this? Instead of using "LIKE" maybe something else?

I'm not very good at VB but I can follow examples.. Maybe a while loop to separate the numbers? but that would probably take a long time.

Please somebody help me!

 

[marleymanner1]

I'm kind of a newbie here myself, but I can tell you that for what you want, the keyword LIKE is your only option, because you have delimited entities in one field. There is no other way to do it. But, it looks as though there is a flaw in your code. I think this:

varWhere = varWhere & "[nccodeID] LIKE """ & Me.cmbNCcodeID & "*"" AND "

maybe should be this:

varWhere = varWhere & "[nccodeID] LIKE '*' " & Me.cmbNCcodeID & " '*'"

That looks about right. I think it would be in SQL anyway, but VB is very similiar.

 

[Sly600rr]

Thanks for the reply marleymanner1 but access really didn't like that syntax,

My code works, but it only returns the first number.

Say If I have my feild "1,2,3"
if I do a search for "1" it will find that record,but not if i do a search for "2"

I need a way for it to find "1","2",and "3" if i search for them.

Thanks anyway though.

 

[Brianwarnock]

I'm not to clear on what you are doing but wondered if the IN function might be a possible avenue.

Brian

 

[Sly600rr]

how would an IN function work?

 

[Brianwarnock]

Having thought about it that's a nogo, sorry.
Will try and think of something else.

Brian

 

[boblarson]

the big question is why are there multiples stored in a single field? This is NOT a good way of storing data and violates normalization rules. What is the purpose? Why are you storing the data this way?

 

[Brianwarnock]

As I said I'm not sure what you are doing but assuming you have a field FIELD1 with your string in it and a search criteria in ComboID then will

If Instr(1,FIELD1,comboID) >0 then
stringfound
Else
String not found
end if


solve your problem?

Brian


Edit of course Bob is correct, I should have asked that question.

 

[Sly600rr]

okay, I have realised I have no idea what i'm doing, but this is what i have managed to make work.

My main table has my [Order] Information
it is linked by [Order] to another table that uses a contious form to input any Non-Conformances([NC]) for that [Order]

Likes this


[Order]
'-----------------[NC1]
'-----------------[NC2]
'-----------------[NC3]

for each [NC] there is a combo feild for the [REASON]

In my search form, I want to be able to search for the [REASON] an order was Non-conforming and return that [Order] and there can be more than one [reason]

The only way I know to search is by my query, and if I try to query my [NC] table I will only get the first result, not all of them.

So... on my main input form, which has the [order] information and the sub-continuous form [NC]
I have a field [REASONS] on my [order] form that records any [REASON] from the continuous form. in the format (1,2,3...)

So I'm storing the [REASON] as seperate records, just trying to put them into one feild to search for them..

I know there is no way that will make since to anyone..

 

[Brianwarnock]

The only way I know to search is by my query, and if I try to query my [NC] table I will only get the first result, not all of them

This is the bit I don't follow, if you query this table with "orderid" as criteria you should return all records and therefore reasons.

Brian

 

[Sly600rr]

if I query for the [REASON] it doesn't just return one record, it will show a record for each [RESON], so instead of only showing 1 record, it shows 3 records. which is not what i need. i just want it to show one record but be able to search for any of the [REASON]'s