[AccessD] weird query def

Hi John

You could retrieve the .SQL and parse that for field names.
To eliminate functions and concatenations from the parsing routine,
you can "shell" a delicate query in a straight select query which just
retrieves the specified field names from the first query; then
all you need is to look up "SELECT" and pick last word in front of
each comma until "FROM". That should be easy for you to build.

Then - ahemm - don't you ever learn (I'm not to good, that's why I can
ask, see my post today to Erwin):

> .. to generate a comma delimited list of field names for
> the combo to use with a value list row source type. 

That should be a semicolon separated list. Look up the semicolon
discussion 2002-12-19 with Charlotte, Shamil and me under the subject:

  Internationalization ... Again

Now, did I destroy your day with that word?

/gustav


>>Could it be a null concatenation problem?

> The query was filtered such that it returned no data and the querydef still
> says it has no fields.  In the end it doesn't matter why it happens, only
> that it does.  I am trying to build a system where a database administrator
> sets up mail merge documents by selecting a query, a merge doc, and then the
> wizard allows him to select (match) a field from the query and a bookmark
> from the doc.  It is easy to do this manually for a single document but I
> have a system where they will have dozens of documents, and want to be able
> to add tem as they need.  The wizard needs to allow the dba to easily set up
> a new document for mail merge visually, in an Access form.

> It was all working, but it was embedded inside the database where the mail
> merge was taking place.  I am trying to extract the concept into an MDA and
> make it a true wizard, that can be used from any FE by anyone with the
> smarts to understand the concept.  This problem didn't exist before simply
> because the entire thing was performed inside the FE itself.  In fact the
> combo displaying the query field list did so simply by using the field list
> row source type.  Doing that requires the QUERY to be inside the wizard, a
> no-no since the query belongs to the FE OR that the wizard be inside the FE
> which makes it non-portable.  Thus my attempt to generate a comma delimited
> list of field names for the combo to use with a value list row source type.

> This actually works quite well with most queries.  However as soon as you
> build a query with an aliased field where the data returned is run through a
> custom function inside the FE where the query is stored, the querydef field
> list "disappears".  Yep, just disappears.  No fields in the querydef.fields
> collection!

> 8-(

> John W. Colby
> www.colbyconsulting.com

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mike and Doris
> Manning
> Sent: Tuesday, July 01, 2003 9:50 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] weird query def


> Could it be a null concatenation problem?

> Doris Manning
> Database Administrator
> Hargrove Inc.
> www.hargroveinc.com


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
> Sent: Tuesday, July 01, 2003 9:25 AM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] weird query def


> I've discovered what the problem is.  The query in question uses a function
> to append City, State and zip into a single string.  That function is
> defined in code in the FE where the query resides.  Without that field in
> the query its fields show up in the query def.  With that function in the
> query, the qureydef thinks it has no fields.  This seems pretty strange to
> me since I am using the code that Gustav provided, opening the FE as a
> database, setting a qdf from its querydefs collection and manipulating that.
> You'd think it could see the functions inside the database but apparently
> not.

> This is a real show stopper since the wizard needs to be able to grab the
> query field names to match against the bookmarks in the doc being merged.