%*

[plonq]

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%
(etc)

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%
(etc)

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.

Comments

[pfcottontail.livejournal.com]

http://xkcd.com/327/

[hantamouse.livejournal.com]

I thought I'd already found all the Stupid that Access has to offer.
I've got to try this tomorrow.

[ducktapeddonkey.livejournal.com]

I have yet to work with any MS or (M)SQL database that isn't completely f'd at some level. There's always at least one thing (typically the thing that specifically prevents me from doing what it is I have to do) that is so far screwed, that it has warped the universe around it and all the other people that don't have to do what you're doing have convinced themselves that there is simply nothing wrong with the database.

Then there's usually a bright flash and I find myself just getting home at 9 o'clock at night without really being sure what I spent my last 4 hours at work doing.

...but that's just me.