Instr function

[jgarnick]

I have a field (displ_sub) that could include the following data:<br><br>Rec 1&nbsp;&nbsp;p/nf<br>Rec 2&nbsp;&nbsp;pw/f<br>Rec 3&nbsp;&nbsp;f/pw<br>Rec 4&nbsp;&nbsp;nf/p<br><br>I need to be able to pull the records where this field equals or includes p, for example, which would be records 1 and 4.&nbsp;&nbsp;But I do not want to include pw.&nbsp;&nbsp;I have the following expression in my query and the criteria is &gt;0.<br><br>InStr([DISPL_SUB],RTrim([forms]![FELLOWS LISTING CHOICES]![TEXT2])<br><br>( [forms]![FELLOWS LISTING CHOICES]![TEXT2]-this where the user would enter p.)<br><br>This expression includes the pw's.&nbsp;&nbsp;Is there a way to make Access pull only the p's?<br><br>Hope I explained it clearly enough!<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[Joshman]

Hello jgarnick<br><br>My first thought, if I understand your dilemma, is to have the query look for &quot;p/*&quot; or &quot;*/p&quot;.&nbsp;&nbsp;But that is only based on your sample data.&nbsp;&nbsp;But this would limit it to just look for &quot;p&quot; by itself on either side of the slash.<br><br>Hope this helps.<br><br>joshman <p> <br><a href=mailto:joshuab@musician.org>joshuab@musician.org</a><br><a href= > </a><br>

 

[jgarnick]

Thanks Joshman, but it does just limit it to my sample data.&nbsp;&nbsp;The user could enter any other letter--<br><br>Anyone else? <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[DreewToo]

If I remember correctly, there is a function that will put string fragments into an array, splitting it with criteria...Could have been Access or VB. This would make life a heck of a lot easier for you though.

drew

 

[jgarnick]

Thanks, Drew---any idea what the function is??

 

[MichaelRed]

jgarnick,

The function is &quot;Split&quot;, but if I understand what you want, this is not going to resolve your problem.

You could just expand the where clause to include &quot;And Not Like *pw*&quot;, but again this looks like just a band-aid for the sample data, not a real soloution.

To really parse all of the varaitions which MIGHT occur, you probably need to set up something much more elaborate, like a table of allowed values (and a flag to use in your query) for the field and do validity checks on the user input to assure that ONLY the values are ever placed in the table. Once this is in place, have the query based on the same 'table' of valid entries, where you set the flag for the sub set of entries you want from the table. Then, the query just joins the two tables on the field values and uses the flag to get just the ones you want. You can set the flags form a form or in code, depending on the details of the process.

MichaelRed
There is never time to do it right but there is always time to do it over

 

[DreewToo]

Thank you for confirming my suspicion, Michael.


Here's what I'd prob do...

dim str as string
dim curchar as string
dim i as integer
dim seperater as string

seperator=&quot;/&quot;

if not isnull([DISP_SUB]) then

for i=0 to (len([DISP_SUB])-1)
curchar=InStr([DISPL_SUB],RTrim([forms]![FELLOWS LISTING CHOICES]![TEXT2],i,1)

if curchar=seperater then
'add str to current space in array
'increment array counter by 1
'move to the next element in the array
str=&quot;&quot;
else
str=str+curchar
end if

next i
end if

That seems like it would toss the seperate elements of the field into an array, which could then be searched for the variable you are looking for.

hth,
Drew