Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Calculated fields as criteria (2000sp3)

    I've often run across this problem, but it's really infuriating me now, so I thought I'd ask if anyone can shed any light.

    Is it fundamental to SQL, or a quirk of access, that one can't use calculated fields in criteria?

    This works fine (stupid examples):

    SELECT tbl1.fldA, tbl1.fldB, tbl1.fldA+tbl1.fldB AS ABsum FROM tbl1 WHERE tbl1.fldA>10;

    but this

    SELECT tbl1.fldA, tbl1.fldB, tbl1.fldA+tbl1.fldB AS ABsum FROM tbl1 WHERE tbl1.fldA>10 AND ABsum<25

    requests a value for ABsum.

    There's no way round this - defining ABsum as (fldA+fldB)>25 and then trying to filter interactively on ABsum being true(-1) or even sorting by ABsum leads to a datatype mismatch in criterion error. I've even tried nesting the queries with the criterion for ABsum on the outside, but to no avail.

    Is there anyway round this other than to use a stored query? The reason for it being so irritating at the moment is that I'm interacting heavily with my data at the moment, and really need to do things on the fly.

    Thanks

    PS Off this topic, but another weird variable datatype thing. If I declare bCounter as byte, and then include it in a for next loop where it never takes values outside 0-254, but with a negative STEP, I get an overflow error. I kind of understood the intermediate value datatype gotcha, but this I really can't understand.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields as criteria (2000sp3)

    You can do this:

    SELECT tbl1.fldA, tbl1.fldB, tbl1.fldA+tbl1.fldB AS ABsum FROM tbl1 WHERE tbl1.fldA>10 AND (tbl1.fldA+tbl1.fldB)<25

    In other words, repeat the expression that defines the calculated field in the WHERE clause.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields as criteria (2000sp3)

    About the For ... Next loop: problem is that -1 (the step) is not a valid byte value. But there is no advantage to using a byte loop variable, I believe. Pentium processors are more comfortable with integers than with bytes, and more comfortable with long (32 bit) integers than with 16 bit integers. And a single byte variable probably doesn't conserve space, either - to keep things aligned on even numbered addresses, it is probably padded by an unused byte (or even by 3 unused bytes)

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Calculated fields as criteria (2000sp3)

    Thanks Hans,

    Lazy folk work the hardest, as they say

Posting Permissions

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