Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012

    Unanswered: Filtering on Calculated controls

    I have been trying to filter a report on a calculted field but when I put the calculated field in a query and try to give it a criteria, it comes up wanting an input for the calculated field name. The calculation gives me the values I want but i can't filter on them in a query. Have tried to do it in a report control but it keeps telling me it is an invalid parameter. I am calculating the DateDiff between Now(0) and an expiration date. I want to show only the values that give me a negative number. If I try to put the critera <0 I get "Data type mismatch in criteria expression". If I put <0 in the Field definition, it changes to 0/-1 values but I can't filter on -1, true, or anything. I am using Access 2007. I know I have done something like this in older versions but can't seem to get it to work in this version. Anyone have an idea?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    You have to repeat the expression yielding the calculated column (field) into the conditional part (WHERE...) of the query:
    SELECT [Col1], [Col2], [Col1] + [Col2] AS Total
    FROM [Table1]
    WHERE Total > 1000;
    SELECT [Col1], [Col2], [Col1] + [Col2] AS Total 
    FROM [Table1]
    WHERE [Col1] + [Col2] > 1000;
    Have a nice day!

  3. #3
    Join Date
    Jun 2012

    Smile Filtering on Calculated controls

    Thanks Sinndho but I tried that in several different formats and still would not work. I didn't try it from straight VBS but since then I have read a little one-liner on Access 2007 that you cannot filter on a calculated field. So I went another direction and created a temp table to import my data into with a field that I could actually create the calculated data as a set of field data. Then filtered on that. It seems to work fine.

    Thanks for the help.

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by sc2012 View Post
    ...I am calculating the DateDiff between Now(0) and an expiration date.
    Not sure this had anything to do with your problem, but it's worth noting that when doing this kind of thing, it is best practice to use Date() rather than Now().

    Date() returns only the Date-portion of the current Date/Time.

    Now(), on the other hand, returns both the Date-portion and Time-portion of the current Date/Time.

    Because of this, comparing the the current Date/Time (returned by Now()) with a Date-only Field, such as 'expiration date,' can lead to unexpected/incorrect results!

    Sinndho's advice about filtering on the Formula or Expression for a Calculated Field, rather than on the Field, itself, is right on the mark! You have to do the same thing when 'summing' a Calculated Field.


    Field3 = Field1 + Field2

    then you have to use

    Sum(Field1 + Field2)

    rather than


    Linq ;0)>
    Last edited by Missinglinq; 08-16-12 at 10:28.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts