<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [AccessD] weird query def</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=988153214-01072003><FONT face=Arial color=#0000ff size=2>Sure,
there are other options. I use functions because you can pass data into
vars and check for nulls, append the data differently if the dat coming in is
null etc. The point here is that it is not good to build a wizard that
forces the developer to modify their way of doing business to adapt to the
wizard. Especially when it could impact dozens of queries in something
like this. I have standard City / state / zip functions (and other
similar) that I use to extract this data from anywhere and get it all in place
for labels and the likes. </FONT></SPAN></DIV>
<P><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Hale,
Jim<BR><B>Sent:</B> Tuesday, July 01, 2003 10:18 AM<BR><B>To:</B> 'Access
Developers discussion and problem solving'<BR><B>Subject:</B> RE: [AccessD]
weird query def<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>