Expressions and how i can write them

[not a code fan]

As we all know Access is an amazing programme with endless possibilies. Trouble is one of the things that stops me from doing a lot more than i can already is expressions. I just don't get it.

I know there is an expressions builder and when i first saw it i thought, ok you just choose the pieces and shove them together, but as i found out it's not quite as simple as that. Is there something i am overlooking here? How do people learn what expressions actually mean? It would revolutionise the way i use Access if i could crack how to write expressions properly. The examples in the help files are quite limited and i usually don't find what i need. Anyway i'd rather write an expression than copy it.

Has anyone any good suggestions for help on this matter

 

[Mile-O]

What I did was find a function such as Left(), look it up in the help files, and then look for other related functions i.e. Right(), Mid(), Trim(), etc.

With Date(), I found DateValue(), TimeValue(), DateSerial(), DateAdd(), TImeSerial(), Month(), Year(), Day(), etc

I think knowing the many functions - and their arguments - is something that's good to learn. I must admit though, I don't think I've ever used the expression builder. Knowing the functions offhand means I can type my expressions in off the top of my head.

 

[The_Doc_Man]

First, SJ is spot-on. To know how to build an expression, you must know the many possible parts you can put together. Functions are BIG part of that parts list.

The problem with expressions, though, is that not all parts are available in the same way at the same time. And therein lies the problem.

For example, in a query, you must remember that the query design grid will be translated to SQL. Design a query and then click on SQL view if you doubt it. But SQL stands for Sequential Query Language (at least in older texts.) Sequential - as in "one record at a time." Therefore, in queries that do not contain aggregate functions and a GROUP BY clause, i.e. summation queries, you have only the fields in the current record to work with. No variables in code (unless you have written a public function in a general module, which DOES work in queries).

On the other hand, in VBA code under a form or report, you have a class module that could hold variables. Plus the general modules, which can be visible if you need them. Plus the controls and properties on the form. Plus the recordset underlying the form, if it is a bound form.

Then there are macros, which include the ability to run code that can include expressions in that code. Not in a macro line, but in the implied target of a RunCode. There, you have no forms or reports, no controls, no document properties - but you can still have modules available. Recordsets are available if you open them yourself.

On top of all of these, you have functions, and these functions include the so-called Domain Aggregates - like DSum, DCount, DMax, etc. etc. - which can give you information about an entire (qualified or unqualified) set of records as specified in the two or three arguments you use.

Therefore, a big part of expressions is known what you can use when - and how. Of course, in a query that has TWO tables - JOINed at the hip, of course - you have to be able to specify your field source if there is any chance of ambiguity. So that means qualified field references as opposed to plain field references.

I understand that it can be quite bewildering to a relative beginner. You'd be surprised just how quickly you get used to it, though.

Now, having said that, I have to ask if you have used expressions in any other venue - say, VB or C++ or FORTRAN or Ada etc. etc. Java and other web-based languages also support expressions of various types.

Access is no different in the way it responds to the normal arithmetic operators and is no different in the way it manages mixed-type expressions. Where Access is different from other programming environments is in the (non-)availability of all the possible parts all of the time.

 

[not a code fan]

It's all Greek to me

Doc Man

I haven't used expressions in anything other than Access and how i have used expressions in Access has been very basic indeed.

A lot of what you said frankly didn't mean much to me as i don't have that sort of grasp on Access and so have not become familar with such terms. Saying that though it all sounds very complicated until you learn it.

I will have a look through the help files as was advised, but it sounds like it could be a slow learning process. I was hoping it would be simpler than that for someone like me with relatively basic access skills (that is comparing to the skill level of people on here).

My biggest problem with expressions is not so much what to put in the expression as the expression builder provides that, it is how it all fits together and makes an expression that works and doesn't give an error message.

 

[Mile-O]

Have you ever done formulae in Excel?

 

[The_Doc_Man]

how it all fits together and makes an expression that works and doesn't give an error message

Attacking something from the outsider viewpoint is always tough. The only way to do is by doing, in the final analysis. That's why schools give you classroom problems. All I can advise is that there is NO quick fix on learning how to do expressions.

Think of it this way, though. An expression is a FORMULA like any insane chemist (like me, for example..... {spooky laugh echoing strangely in the background}) would use to concoct some strange potion.

You use the formulas to tell the computer how to mix its available parts together to get a result. It is like (but not EXACTLY like) algebra, where you mix variables and constants interspersed with arithemetic operators like plus, minus, times, divide, exponentiate, etc. to generate a result.

Well, Access supports that. It ALSO supports string formulas that can be used to split or merge text strings. Like all good programming environments, it supports the distinction between counting numbers and real numbers (Integer and Long vs. Single and Double).

My best advice is to write some throw-away queries to play with expressions. You can include an expression in a field of a query and see what it generates. SELECT queries never update anything so they are safe to play with. DON'T play with UPDATE, APPEND, or ERASE queries until you are ready, but SELECT queries are always benign. (And are also the default, not by accident either.)

VBA doesn't lend itself well to just writing a program to say "HELLO" on your terminal because it tends to want to do what its hosting application does. Which, for Access, means it wants to diddle with record-oriented data. So breaking into expressions with VBA is actually not as straight-forward as in other environments.

 

[Mile-O]

Have a look at this database. It shows some expressions in action within queries. Look at the function keywords and learn them (it's the only way) - take the time to see what they do and what info they need in order to work.

Personally, I'd just forget about the Expression Builder; I've never been able to use it either.