Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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].

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
The query works fine if I do not put Filter Criteria. It only does this when I put filter criteria. –  xxl3ww Mar 17 '11 at 16:00
    
Which one of these cases you want to be displayed: –  Darius Mar 17 '11 at 16:42
    
When you put your criteria on the query are you sure all the spelling is correct? Look over the expression and identifiers listed in the "Field" and "Criteria" rows and see if any of the text matches what was shown in the Enter Parameter Value dialog box. –  Darius Mar 17 '11 at 16:49
    
The only thing I am doing in access is putting >5 in the criteria under the Diff field. I am going to change it to SQL view and see what I can find out –  xxl3ww Mar 17 '11 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.