<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] weird query def</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Could you use a sub query to build the string instead of a function? I've run into similar problems trying to run queries in a FE from Excel where the criteria for the query was determined by a function. The solution was to use a sub query in place of the function. </FONT></P>
<P><FONT SIZE=2>Jim Hale</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: John Colby [<A HREF="mailto:jcolby@colbyconsulting.com">mailto:jcolby@colbyconsulting.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Tuesday, July 01, 2003 9:06 AM</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] weird query def</FONT>
</P>
<BR>
<P><FONT SIZE=2>>Could it be a null concatenation problem?</FONT>
</P>
<P><FONT SIZE=2>The query was filtered such that it returned no data and the querydef still</FONT>
<BR><FONT SIZE=2>says it has no fields. In the end it doesn't matter why it happens, only</FONT>
<BR><FONT SIZE=2>that it does. I am trying to build a system where a database administrator</FONT>
<BR><FONT SIZE=2>sets up mail merge documents by selecting a query, a merge doc, and then the</FONT>
<BR><FONT SIZE=2>wizard allows him to select (match) a field from the query and a bookmark</FONT>
<BR><FONT SIZE=2>from the doc. It is easy to do this manually for a single document but I</FONT>
<BR><FONT SIZE=2>have a system where they will have dozens of documents, and want to be able</FONT>
<BR><FONT SIZE=2>to add tem as they need. The wizard needs to allow the dba to easily set up</FONT>
<BR><FONT SIZE=2>a new document for mail merge visually, in an Access form.</FONT>
</P>
<P><FONT SIZE=2>It was all working, but it was embedded inside the database where the mail</FONT>
<BR><FONT SIZE=2>merge was taking place. I am trying to extract the concept into an MDA and</FONT>
<BR><FONT SIZE=2>make it a true wizard, that can be used from any FE by anyone with the</FONT>
<BR><FONT SIZE=2>smarts to understand the concept. This problem didn't exist before simply</FONT>
<BR><FONT SIZE=2>because the entire thing was performed inside the FE itself. In fact the</FONT>
<BR><FONT SIZE=2>combo displaying the query field list did so simply by using the field list</FONT>
<BR><FONT SIZE=2>row source type. Doing that requires the QUERY to be inside the wizard, a</FONT>
<BR><FONT SIZE=2>no-no since the query belongs to the FE OR that the wizard be inside the FE</FONT>
<BR><FONT SIZE=2>which makes it non-portable. Thus my attempt to generate a comma delimited</FONT>
<BR><FONT SIZE=2>list of field names for the combo to use with a value list row source type.</FONT>
</P>
<P><FONT SIZE=2>This actually works quite well with most queries. However as soon as you</FONT>
<BR><FONT SIZE=2>build a query with an aliased field where the data returned is run through a</FONT>
<BR><FONT SIZE=2>custom function inside the FE where the query is stored, the querydef field</FONT>
<BR><FONT SIZE=2>list "disappears". Yep, just disappears. No fields in the querydef.fields</FONT>
<BR><FONT SIZE=2>collection!</FONT>
</P>
<P><FONT SIZE=2>8-(</FONT>
</P>
<P><FONT SIZE=2>John W. Colby</FONT>
<BR><FONT SIZE=2>www.colbyconsulting.com</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: accessd-bounces@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>[<A HREF="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>]On Behalf Of Mike and Doris</FONT>
<BR><FONT SIZE=2>Manning</FONT>
<BR><FONT SIZE=2>Sent: Tuesday, July 01, 2003 9:50 AM</FONT>
<BR><FONT SIZE=2>To: 'Access Developers discussion and problem solving'</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] weird query def</FONT>
</P>
<BR>
<P><FONT SIZE=2>Could it be a null concatenation problem?</FONT>
</P>
<P><FONT SIZE=2>Doris Manning</FONT>
<BR><FONT SIZE=2>Database Administrator</FONT>
<BR><FONT SIZE=2>Hargrove Inc.</FONT>
<BR><FONT SIZE=2>www.hargroveinc.com</FONT>
</P>
<BR>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: accessd-bounces@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>[<A HREF="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>] On Behalf Of John Colby</FONT>
<BR><FONT SIZE=2>Sent: Tuesday, July 01, 2003 9:25 AM</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] weird query def</FONT>
</P>
<BR>
<P><FONT SIZE=2>I've discovered what the problem is. The query in question uses a function</FONT>
<BR><FONT SIZE=2>to append City, State and zip into a single string. That function is</FONT>
<BR><FONT SIZE=2>defined in code in the FE where the query resides. Without that field in</FONT>
<BR><FONT SIZE=2>the query its fields show up in the query def. With that function in the</FONT>
<BR><FONT SIZE=2>query, the qureydef thinks it has no fields. This seems pretty strange to</FONT>
<BR><FONT SIZE=2>me since I am using the code that Gustav provided, opening the FE as a</FONT>
<BR><FONT SIZE=2>database, setting a qdf from its querydefs collection and manipulating that.</FONT>
<BR><FONT SIZE=2>You'd think it could see the functions inside the database but apparently</FONT>
<BR><FONT SIZE=2>not.</FONT>
</P>
<P><FONT SIZE=2>This is a real show stopper since the wizard needs to be able to grab the</FONT>
<BR><FONT SIZE=2>query field names to match against the bookmarks in the doc being merged.</FONT>
</P>
<P><FONT SIZE=2>Bummer!</FONT>
</P>
<P><FONT SIZE=2>John W. Colby</FONT>
<BR><FONT SIZE=2>www.colbyconsulting.com</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: accessd-bounces@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>[<A HREF="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>]On Behalf Of John Colby</FONT>
<BR><FONT SIZE=2>Sent: Monday, June 30, 2003 12:57 PM</FONT>
<BR><FONT SIZE=2>To: AccessD</FONT>
<BR><FONT SIZE=2>Subject: [AccessD] weird query def</FONT>
</P>
<BR>
<P><FONT SIZE=2>I have a weird one. I am opening a query def and reading all of the field</FONT>
<BR><FONT SIZE=2>names out into a comma delimited string to use as a row source for a combo.</FONT>
<BR><FONT SIZE=2>All that stuff works. One of the queries in the db I am trying to work on</FONT>
<BR><FONT SIZE=2>says it has NO fields. If I execute the query I get data. If I look at the</FONT>
<BR><FONT SIZE=2>querydef itself, I can see the name of the query, the SQL property, etc. But</FONT>
<BR><FONT SIZE=2>ask it for the .Fields.Count and it comes back 0.</FONT>
</P>
<P><FONT SIZE=2>Very unsettling since I need to use this method for displaying field names</FONT>
<BR><FONT SIZE=2>to the user to select to match against document book marks in a mail merge</FONT>
<BR><FONT SIZE=2>wizard.</FONT>
</P>
<P><FONT SIZE=2>I compacted / repaired both the wizard as well as the FE that actually holds</FONT>
<BR><FONT SIZE=2>the query in question. Two other queries that I am performing this</FONT>
<BR><FONT SIZE=2>operation on work perfectly. One apparently has no fields.</FONT>
</P>
<P><FONT SIZE=2>John W. Colby</FONT>
<BR><FONT SIZE=2>www.colbyconsulting.com</FONT>
</P>
<BR>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>