MS Access Query Criteria Issue

Question

Currently I have a MS Access database query that has a field named FedEXDetTotal that totals 9 FedEX charge fields. I have another field that is from our inhouse system called "Total Charge". This is just a normal number field. I have created another Field in this query

Diff: [FedEXDetTotal]-[Total Charge]

This tells me the difference between the Fedex charge and what we actually charged. Everything works OK with this, but when I try to put the criteria >5 for the Diff field, when I run the query, I get a prompt saying "Enter Parameter Value FedEXDetTotal".

Why is Access doing this? How do I get around this? I'm trying to start out with something simple(>5), but what I really want is >[Forms]![Dis].[txtbox_Diff].

Answer 1

Aliases can be used in the SELECT statement with Jet/ACE SQL, but not in the WHERE clauses.

So, this is OK:

  SELECT FedExDetTotal, [FedEXDetTotal]-[Total Charge] AS Diff

But you then can't do this:

  SELECT FedExDetTotal, [FedEXDetTotal]-[Total Charge] AS Diff
  FROM Orders
  WHERE Diff > 5

Instead, you have to repeat ALL the calculations in the WHERE clause, thus:

 WHERE ([FedEXDetTotal]-[Total Charge]) > 5

(the parens are not required, but make it clear, and are required with some operators, like * and /)

The SQL dialects for some databases allow you to use aliases in the WHERE clauses, but my understanding is that the Jet/ACE way is completely consistent with the SQL standards.

Fact is, if you'd use the Access query builder to write your SQL, it will do this for you.

Answer 2

This Article from Microsoft might just help to solve your problem. I am assuming that there is some data in your DB to test that query against.