![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
plonqA * 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.
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.
