I noticed that after I hit the 'send' button. I had a <cfif> to check if there were actual records before running the statement. I didn't think I needed to show all that, so I took it out, but left the stray end code. The code works well. I haven't tested it at a lot of values though. This will not have the same issue once their are like 100 records in the getActiveWorks query. Right? ~David G. Moore, Jr.> Subject: RE: Query Too Complex for Access?> From: [EMAIL PROTECTED]> To: cf-talk@houseoffusion.com> Date: Wed, 20 Aug 2008 15:18:11 -0500> > Acutally, the first part is correct. The listQualify() function actually> just places 'single quotes' around each of the values in your valuelist> since that would be required by the DB.> > List qualify doesn't check any variables.> > I noticed you had a stray </cfif> tag. Were you missing a <cfif> condition> as you only wanted to compare against 'some' of the records in> getActiveWorks? If so, send your CFIF statement as we'll have to modify> what I sent you earlier.> > Dave> > -----Original Message-----> From: David Moore, Jr. [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2008 1:37 PM> To: CF-Talk> Subject: RE: Query Too Complex for Access?> > No I haven't. > > What you are saying is that I should use valueList to build a full list from> all values in the getActiveWorks query and then listQualify to see if any> variable matches.> > Thanks David! I will give it a shot. > > Does anyone else know of any other ways?> > David G. Moore, Jr.> UpstateWeb. LLC> Subject: RE: Query Too Complex for Access?> From:> [EMAIL PROTECTED]> To: cf-talk@houseoffusion.com> Date: Wed,> 20 Aug 2008 13:27:34 -0500> > Have you tried:> > AND Works.ThisInventory not> in> (#listQualify(valueList(getActiveWorks.ThisReference),"'")#)> > ??> >> Dave> -----Original Message-----> From: David Moore> [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2008 1:18 PM>> To: CF-Talk> Subject: Query Too Complex for Access?> > I know I am setting> myself up for another "Query too complex" issue, so> before I start I> thought I would ask for suggestions. I run into this when I> have to> reference two different Access databases that are Client imposed> (don't> ask). Basically, I have to use one for active data and one to show>> available date (minus the active data). This is, of coures, where the>> problem comes in. The queries will help:> > <cfquery name="getActiveWorks"> datasource="#DSN#">> SELECT * > FROM Works > WHERE Works.PageReference => #FORM.ThisPage#> AND Works.TypeReference = '#FORM.ThisType#'> </cfquery>> >> <cfquery name="getWorks" datasource="#DSN2#">> SELECT *> FROM Works, Artists> > WHERE Artists.ArtistNumber = Works.ArtistNumber> AND Works.Type => '#FORM.ThisType#'> <cfloop query="getActiveWorks">> AND Works.ThisInventory> <> '#getActiveWorks.ThisReference#'> </cfloop></cfif>> ORDER BY Works.Title> Asc> </cfquery>> > Where the cfloop is is where the problem is going to come> into play when the> "Active Works" get to a certain level and the Query> becomes "Too Complex".> What is the Best way to handle this? > > I am using> CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector.> > > > > > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311315 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
