John Colby
jcolby at colbyconsulting.com
Tue Jul 1 09:35:16 CDT 2003
RE: [AccessD] weird query defSure, 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. John W. Colby www.colbyconsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim Sent: Tuesday, July 01, 2003 10:18 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] weird query def 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. Jim Hale -----Original Message----- From: John Colby [mailto:jcolby at colbyconsulting.com] Sent: Tuesday, July 01, 2003 9:06 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] weird query def >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. Bummer! John W. Colby www.colbyconsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby Sent: Monday, June 30, 2003 12:57 PM To: AccessD Subject: [AccessD] weird query def I have a weird one. I am opening a query def and reading all of the field names out into a comma delimited string to use as a row source for a combo. All that stuff works. One of the queries in the db I am trying to work on says it has NO fields. If I execute the query I get data. If I look at the querydef itself, I can see the name of the query, the SQL property, etc. But ask it for the .Fields.Count and it comes back 0. Very unsettling since I need to use this method for displaying field names to the user to select to match against document book marks in a mail merge wizard. I compacted / repaired both the wizard as well as the FE that actually holds the query in question. Two other queries that I am performing this operation on work perfectly. One apparently has no fields. John W. Colby www.colbyconsulting.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030701/9f556981/attachment-0001.html>