Know a good guide to query syntax?

[paqis]

I'm new to Access and have been learning how to use it for the better part of a year. I've done all the Microsoft online tutorials relevant to the work I need to do with it, and gotten a few books out of the library besides. In general, I self-teach very well given a good resource.

Here's my problem: the main area in which I need to be proficient is running queries, and I cannot find a good, comprehensive explanation of how to construct expressions or set up calculated fields.

In case I'm not clear (I've had quite a time just figuring out what to call what I need), I'll give you an example. I was able to arrange a short tutorial with someone in another department. As part of a query, she used the following statement in the "Field" section to convert date information stored as mm/dd/yyyy into just the year:

Year: IIf([referraldate]<#1/1/2003#,"2002",IIf([referraldate] Between #12/31/2002# And #1/1/2004#,"2003",IIf([referraldate] Between #12/31/2003# And #1/1/2005#,"2004",IIf([referraldate] Between #12/31/2004# And #1/1/2006#,"2005",IIf([referraldate] Between #12/31/2005# And #1/1/2007#,"2006")))))

This is the kind of thing I want to learn how to do. Unfortunately, the Microsoft tutorials don't do much more that give examples of different expressions and functions; I feel like I've been given a handful of sample sentences, a few nouns, and a few verbs--and then told to go speak English.

What I need is a comprehensive guide that not only gives me the building blocks of expressions, but tells me how to combine them into a syntactically meaningful statement--so I know what order things go in, where commas and parentheses should be, etc. Both online or print materials are fine--I've been looking on my own, but with no luck.

Thanks for your help!

 

[CraigDolphin]

I'm not sure about reference material (self/forum taught myself) but I was curious about the example you showed.
Why not just use:
=Datepart('yyyy',[referraldate])

 

[paqis]

See, that's the kind of thing I need to teach myself to be able to do. I don't know quite why my coworker set up the statement that way, though I did figure out later that there was an easier way to do it.

You said you're self- and forum-taught. Any forums/sites you've found helpful besides this one? At this point, I'm resigning myself to learning through trial, lots of error, and asking specific questions of more knowledgeable people.

 

[CraigDolphin]

Google, Access' help, and this forum are the principle means I've used to teach myself what little I know. There are other forums out there but I usually get what I need hereand haven't really looked into them, sorry.

If there's a good article somewhere on the web, there's usually a link from a post here, or you can find it via google if you can discover the right keyword to use.

Chances are good that whatever question you come up with has already been asked here. Try general searches first and read through the threads that seem most similar to your problem. Usually I discover some function/code either explained or mentioned by one of the posters. I then search both google and this forum for more specific words based on what I found. Even if you don't find the specific anser you want, you usually learn about a bunch of new things along the way!

And as a last resort, if you can't find what you need, then ask your question. Even the more jaded posters will reply if it is a genuinely novel and interesting, or relatively rare problem.

 

[boblarson]

And I am constantly learning, just by finding posts that look interesting and setting a "subscribe" to it so I get the posts emailed to me. So, if I find a topic that I know little about, but find I want to know, then I do that. I've tried a few different sites but this one has been the best for me.

I've been using it since 1998, even though it says 2001 on my username (as they went through a change in software and it wiped out the original sign up date) and I've learned a ton.

There are some very good posters here and there are some, who forget what it's like to not even know what search terms to use, and they may seem a little "put out" by the perception that they may get from some posts that a person hasn't tried to search. That being said, it is quite clear that a lot of posters don't put a lot of effort into searching the forum first, and even the Access help file (which is a good starting point).

So, just keep plugging away. When I started on this forum I didn't know a whole lot about Access either. It just takes time, effort, and working at it.

 

[Pat Hartman]

One of the things that helped me greatly in the beginning was reviewing the VBA functions. You can find lists in help sorted by name and by category. However, it is easier to find these lists in earlier versions of Access than in the newer ones. A2K is especially poor. I posted a spreadsheet with a list of all the functions that I was able to identify with a little description and a category to help you narrow down your search. It is in the samples forum.