the Sweet Smell of Burning Fur (plonq) wrote,
the Sweet Smell of Burning Fur

  • Location:
  • Mood:


A * is in the eye of the beholder.

I am attempting to do something fairly straight-forward in MS Access. I have built a cross-tab query that links together 3-4 tables and summarizes them based on fairly simple criteria.

That is, give me everything from table A, and then give me all of the matching items from table B that are flagged with an Industry code (versus a Customer code). Tables C and D serve as a bridge between A and B, and are also used to establish the scope of the query. The criteria for table B are very simple:

Like 'R.*' Or Is Null

In other words give me every entry from table B that stats with 'R.', and every entry from table A even if there is no entry in B.

I then call this query with a second query that substitutes 0 for the nulls, and generates an inverse percentage of B to A (that is, a Null in B equates 100%).

When I run the second query, the output looks something like this:

Area of Interest, Base, Exception, Accuracy

FOO, 100, 10, 90%
BAR, 100, 0, 100%
CAT, 200, 15, 7.5%

Now comes the fun part. I have built a VBA script in a second Access database that opens a connection to that query and retrieves the data into a record set. When I dump the records to a temporary file, this is what I get:

FOO, 100, Null, 100%
BAR, 100, Null, 100%
CAT, 200, Null, 100%

I won't go into the grief and hair-pulling I went through to arrive here, but ultimately I learned that where Access wants a *, VBA in Access wants a % -- even if the query using the wildcard is further upstream from the one that I am directly calling in VBA. What this means is that when I am designing this job, I first create the queries in Access using the * so that I can test them, then I need to purposely break them in Access by replacing the * with a % (which is what it should be anyway) so that I can set up the automation in VBA.

It's all so obvious in 20/20.
Tags: microsoft sucks
  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.