John Colby
jcolby at colbyconsulting.com
Tue Jul 1 11:16:04 CDT 2003
> Internationalization ... Again Now, did I destroy your day with that word? lol. Nope. In fact I am using semicolons. "coma delimited list" is one of those phrases that just rolls off the tongue even when not exactly what is needed. And yep, I guess a bit of parsing is in order here. John W. Colby www.colbyconsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock Sent: Tuesday, July 01, 2003 10:35 AM To: Access Developers discussion and problem solving Subject: Re: [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. _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com