Well in access it is set to text - but it actually only contains numbers... perhaps that is why I often get data mismatch errors... as for single quotes as you suggested - I had those in already - left them out when retyping it - So that does not work.
Also - I feel like I need to be looping the query - I don't know if Right(ItemCode, 4) is actually comparing all the item codes in that table? Is this okay to place in a query ? Or do I have to get all the item codes then loop through the list? I am new to the advanced looping and queries. If it helps to see the URL in action you can review it at http://www.speedpeople.com/ApplicationGuides/WheelAppGuide/WheelAppControl.cfm Thanks for any help again! jay miller Dave Carabetta wrote: > > Hello group - > > hopefully this isn't to novice of a question - but I have been at this for > a while and can't get the logic or the > > syntax right. > > > > I have 2 tables ( in access) - I go through 3 pages of queries narrowing > make, model and year and end up with 1 > > variable - #BoltPattern# - it is just text field in access - but is a > numerical value i.e 4110 or 5108 - (always 4 > > numbers) > > > > I am trying to take that variable BoltPattern and query a long list in a > second table - Items - In this Items table > > - I need to pull ALL ItemCode and ItemID where the BoltPattern matches the > last (right most) 4 characters in the > > ItemCode field. > > THe last set of queries I ran was - > > > > <cfquery name="GetBoltPattern" datasource="speedpeople"> > > SELECT BoltPattern FROM WheelAppGuide > > WHERE Make = '#Make#' AND Model = '#Model#' AND YearStart <= > '#YearLooking#' AND YearEnd >= '#YearLooking#' > > </cfquery> > > > > // previously worked for me and is running on site - but now I am getting > data mismatch erros - 1st problem - > > > > I output the query here so I can use in the next query - > > > > <cfoutput><cfset BoltPattern="#GetBoltPattern.BoltPattern#"></cfoutput> > > > > // then compare to right set of numbers of item code from items --> > > > > <cfquery name="GetWheelCodes" datasource="speedpeople"> > > SELECT ItemID, ItemName, ItemCode > > FROM Items > > WHERE Right(ItemCode, 4) = #BoltPattern# > > </cfquery> > > > > Obviously it is not working. I have a feeling I am missing something > obvious. Again in a nut shell. > > I get variable - BoltPattern from 1 table - through a series of forms. > > > > Then I need to match that BoltPattern with the right 4 chars of ItemCode > ( #RIGHT(ItemCode, 4)# ) from 2nd table > > Have you tried putting single quotes around the GetWheelCodes query, like > so: > > <cfquery name="GetWheelCodes" datasource="speedpeople"> > SELECT ItemID, ItemName, ItemCode > FROM Items > WHERE Right(ItemCode, 4) = '#BoltPattern#' > </cfquery> > > That's a quick answer. Also, are you sure the datatype of the column is > numeric (as opposed to a text field)? If it's text, you have to have the > single quotes. > > Just a thought. > > Regards, > Dave. > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
