Hi Martin,
I've been meaning to reply for a week, but have been snowed under.
You say:[color=blue]
> I gather that Access, when doing comparisons such as
> the <> below, goes through all possible comparisons
> first before filtering out records according to other
> Where statements. I thought that filtering out null records
> (which I've even done with 'Where [Incipit] Is Not Null'
> criteria) should avoid the problem.[/color]
It's history now for your present application, but for future
reference, one way to avoid these problems might be to write two
queries. The first query would have a criterion to eliminate the
nulls (eg 'Where [Incipit] Is Not Null'). The second query would use
the first query as its data source and refine the criteria further.
You can avoid these sort of issues by refining the records in stages.
You say:[color=blue]
> Guess not. What I don't fully understand is why
> this didn't seem clear in any of the help files I read through.
> Perhaps I misunderstood (or underestimated) the
> power/effect of null records![/color]
You have my sympathy. I reckon it took me three years to get my head
round the help files - and I still have to think hard as to what's
really meant by help. You often don't know until you fall in a hole!
However, after a while, I think you develop a sixth sense about what
database or programming features must exist even though you've never
encountered them before. It seems our ignorance gets smarter as you
go along. So don't give up! The online help at Microsoft has pretty
good search facilities. You've no doubt discovered the Microsoft
KnowledgeBase and MSDN already.
You say:[color=blue]
> I am still waffling as to whether queries or vba will best
> accomplish the required tasks.[/color]
I meant to answer this before. There is no straight answer. You just
have to use the best tool for the job. Queries are definitely the
best and fastest way to extract data from tables or from other
queries. The trick is knowing when to write a multiple chain of
queries (one based on another), when to incorporate functions (to ease
the pain or bolster functionality), etc., etc.
I see the other respondent to your post says you're already using VBA
by calling functions like Len(), etc. It's certainly interesting to
know what's going on under the hood.
I think, however, the purpose of your question was to find out why you
should invest time learning VBA, where you should start, and what you
should concentrate on.
If you want my view, I'd suggest you start by learning how to write
functions. They're not difficult and they could add power and
flexibility to your queries.
As to more advanced VBA, when I read your first post, I vaguely
thought you might need some advanced programming for a solution. (I
thought you might want to extract a criterion from the first record of
the Specialized Spellings table, pump that criterion into a parameter
query, do some business with that query, then cycle through all the
records of the Specialized Spellings table one at a time doing
likewise, ie processing one criterion at a time.)
In general terms, you'd use VBA to automate complex processes you want
to hide from your users. For example, you could use VBA to create
queries, tables, recordsets, etc. You may want to create queries
(using VBA) to avoid putting 250 queries in a database - ie you'd
rather create temporary queries on the fly when the user hits a
command button. Alternatively, you might want to jazz up your
data-entry forms so users can find records easily, etc.
You say:[color=blue]
> The one above seems to be solved -
> I changed the null fields to zero-length
> strings (""), and voila, error be gone.[/color]
Neat - there's always more than one way to get a result. (If
relevant, you might want to put "" as the Incipit field's default
value in table-design view to avoid possible Nulls in future new
records.)
You say:[color=blue]
> Thanks too for the book recommendation - I was
> about to post a query along the same lines - I have
> seen mention of this same book online, so I will
> most definitely check it out. I do want to acquire
> some small measure of fluency with writing vba.[/color]
You know about the "Step-By-Step" book now. If and when you're
ready for more (not just on VBA but on Access too), amongst the books
that Microsoft buy their own developers (so I understand) are by Getz,
Litwin & Gilbert ("The Access Developer's Handbook" - two volumes) -
quite expensive but authoritative on all aspects of Access. You can
buy those two Handbooks with the "Visual Basic Language Developer's
Handbook" by Getz and Gilbert at a saving. That lot will make a hole
in your wallet and stretch your arms as you carry them home! For a
smaller book of solutions try the "Access Cookbook" by Getz, Litwin
and Baron. Their (dare I say, somewhat unimpressive, but don't let
that fool you) website is at:
http://www.developershandbook.com/
If you're done on this post, then over and out.
Good luck for the future.
Geoff
