Searching for rug sizes

[kpierson]

Ok, back again. MS Access rug sizes are input as 10.1 x 13.5<br>etc. 45 rugs right now and 44 are different sizes (why the customer has some as 10.1 and 10.11 is a mystery). I got it to where it would search for the width of a rug, such as searching for 10 wide turns up all rugs that begin with 10 <br><br>&nbsp;&lt;cfquery Datasource=&quot;donoghue1&quot; Name=&quot;rugsize&quot;&gt;<br>Select rug.itemno, rug.type, rug.description, rug.size, rug.price, rug.id, photo1, photo2<br>From rug<br>&nbsp;Where rug.size LIKE '#rugsize#%'<br>&lt;/cfquery&gt;<br><br>but I would like it to search for 10 x 13 regardless what is after the decimal point in the database.<br>Since there are only 45 rugs at this time I can go in and eliminate the spaces before and after the x if that is a problem.<br>Any suggestions?<br>Kim

 

[TamHonks]

Kim -<br><br>If I were you I'd change the design of my database, and have separate (numerical) fields for rug length and rug width...<br><br>The method you propose treats numbers as if they were strings... This seems to me a problem, because won't the snippet of code you suggested pick up '1000 x 13' as well as '10 x 13'?<br><br>If the two were separate fields, I think you could use the Int function in ColdFusion, and/or the Int1 function in SQL, to jettison what comes after the decimal point...<br><br>I'm no SQL expert, so here's the sort of expression I have in mind in 'pseudo code':<br><br>if (user_rugwidth = db_rugwidth) or ( int(user_rugwidth)=int(db_rugwidth) ) then match found!<br><br>This would pick up either an exact or an approximate user input...<br><br>Hope this helps... Can anyone else shed more light?<br><br><br>Tam<br>

 

[dbarnhart]

I agree with TamHonks.&nbsp;&nbsp;You really need to store the rug size as two separate numeric fields instead of a single string field.&nbsp;&nbsp;Then you could do:<br><br>&lt;CFQUERY DATASOURCE=&quot;Test&quot; NAME=&quot;Rugs&quot;&gt;<br>SELECT *<br>FROM Rugs<br>WHERE (int(length) = 10 AND int(width) = 13)<br>&nbsp;&nbsp;&nbsp;&nbsp;OR (int(width) = 10 AND int(length) = 13)<br>&lt;/cfquery&gt;<br><br>This takes into account that the rug might be 10 X 13&nbsp;&nbsp;OR&nbsp;&nbsp;13 X 10<br><br>This query works with MS-Access and ODBC.&nbsp;&nbsp;<br>

 

[kpierson]

Thanks to both of you. Since it was only 45 I went ahead and did add two more fields, width and length. The search for size works good now.<br>Thank you!