Searching Access database with two numbers from textbox 1

[thunderain]

This peice of code works somewhat. I recieve two numbers from a textbox in the previous screen such as (AM79C30A, AM79C978). I want to get all the records (from AMD_Part_Number field in filtron table) that match either number.

It depends on what order you put them in. I put two numbers in (AM79C30A, AM79C978) it gives you what you want, 26 records for the first number and 2 records for the second. Great!!! BUT...if I switch the numbers, (AM79C978, AM79C30A) I only pick up the two records for the first number and not the 26 records for the second. This is strange. What would cause this?

(If i only put one number, it crashes, but I assume I need an if statement to handle that, either one number or two)



<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site, Approved
FROM filtran
WHERE

<cfif #form.Filtran_Part_Number# is not &quot;&quot;>
(AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot#%') or
(AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 2)#%')
</cfif>

</cfquery>


Has anyone any ideas?

Thank you

 

[randall2nd]

First you will probably want to put a 0=0 after the WHERE, because if form.Filtran_Part_Number is &quot;&quot; it will through an error.

I noticed that on your second ListGetAt() you did not put the list delimeter. The default is a comma so that should not matter.

You have got a crazy problem here. I have used acccess for a database many times, and never encountered this problem. It should not matter what order the numbers are in. Are you sure that you are entering the numbers in the same?

Please let us know if you find the problem. Randall2nd

 

[thunderain]

I added the 0=0. I narrowed the problem down. It isn't the fact that I am switching the numbers. It is because there is a space in there sometimes, when I was switching (either before or after one of the numbers). What I need is to trim as below. But when I put the trim there, it gives no file returns. I moved it around, other places it just doesn't do anything. Where can I put the trim so it will have the effect of trimming the number the way it is supposed to?


<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site, Approved
FROM filtran
WHERE

<cfif #form.Filtran_Part_Number# is not &quot;&quot;>
(AMD_Part_Number LIKE '%Trim(#ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot#)%') or
(AMD_Part_Number LIKE '%Trim(#ListGetAt(form.Filtran_Part_Number, 2)#)%')
</cfif>

</cfquery>


Thank you

 

[thunderain]

thanx randall, i used a bit of your advice.
I ended up using different code for what i was doing and it works perfectly


<Cfparam name=&quot;form.Filtran_Part_Number&quot; default=&quot;&quot;>

<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>
SELECT Filtran_Part_Number, Description, On_Web_Site, Approved
FROM filtran
WHERE
<Cfif listlen(form.Filtran_Part_Number)>
<Cfloop list=&quot;#form.Filtran_Part_Number#&quot; index=&quot;i_Part&quot;>
(Pulse_Part_Number LIKE '%#Trim(i_Part)#%')

<cfif not (i_Part eq listlast(form.Filtran_Part_Number))>
OR

</cfif>

</cfloop>
</cfif>