[AccessD] weird query def

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>


More information about the AccessD mailing list