Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: I need to filter results in query

    In a query I have a field [BN1] that returns results containing both positive and negative values. I only want to see positive values of, 1 or above, so I created another field that turns all negative numbers to 0 and retains all positive numbers.

    NewField: IIf([BN1]<=0,0,[BN1])

    I now want this field to only display values greater than "0" but whenever I try to place a criteria in the column the query returns a requirement to Enter Parameter Value BN1.

    How do I cure this problem?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Just go to your field BN1 and add the criteria ">0" (without the quotes).

    If you have any further questions, can you post your SQL string? I'm not sure how familiar you are with Access, but if you don't know what this is, go to View > SQL View... and copy the string you see.
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree with Nick, but I'll add this for educational purposes. Because the WHERE clause is evaluated before the SELECT clause, the aliased field name isn't recognized. In your case, you could change:

    WHERE NewField > 0

    to

    WHERE IIf([BN1]<=0,0,[BN1]) > 0

    But again, as Nick suggested, just put your criteria on the BN1 field.
    Paul

  4. #4
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Hi,
    I have added your suggestion. This returns only values of 0 or -1.
    As per your request the SQL string is below. Please note that the Newfield is actually termed Bednights in this string.

    SELECT T_Placements.PlacementNumber, T_Placements.PlaceStart, [Enter Date _ _/_ _/_ _ ] AS QueryDate, IIf([PlaceStart]>=[QueryDate ],[PlaceStart]) AS PS2, IIf([PlaceStart]<[QueryDate ],[QueryDate]) AS PS3, Nz([PS2],[PS3]) AS PS4, T_Placements.PlacetEnd, T_Placements.RefAcceptDate, [RefAcceptDate]+56 AS 56Days, T_Placements.ClientID, T_EnterClientDemographics.ClientName, T_EnterClientDemographics.ClientSurName, [ClientName]+" "+[ClientSurName] AS ClntFullName, T_EnterClientDemographics.ClientDoB, DateDiff("yyyy",[ClientDoB],Now()) AS Age, T_Placements.CgID, T_EnterCareGiverDemographics.[CgName(s)], T_EnterCareGiverDemographics.CgSurName, [CgName(s)]+" "+[CgSurName] AS CgrFullName, T_EnterClientDemographics.RefOrg, T_EnterClientDemographics.RefSite, T_EnterClientDemographics.BdNtCgrPmnt, IIf(IsNull([PlacetEnd]),DateDiff("d",[PS4],Now()),DateDiff("d",[PS4],[PlacetEnd])) AS BN1, IIf([BN1]<=0,0,[BN1]>0) AS Bednights, ([Bednights])*[BdNtCgrPmnt] AS CgPmnt, T_EnterClientDemographics.PocketMoney
    FROM T_EnterClientDemographics INNER JOIN (T_EnterCareGiverDemographics INNER JOIN T_Placements ON T_EnterCareGiverDemographics.CgID = T_Placements.CgID) ON T_EnterClientDemographics.ClientID = T_Placements.ClientID
    WHERE (((T_Placements.PlacetEnd)<=[Enter QueryEnd Date _ _/_ _/_ _ ])) OR (((IsNull([T_Placements].[PlacetEnd]))<>False))
    ORDER BY T_EnterCareGiverDemographics.CgSurName;

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It's because of the >0 in the third argument. Try

    IIf([BN1]<=0,0,[BN1])
    Paul

  6. #6
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Hi Paul,
    Yep, that's what I had in the beginning (See top post) which began my search for an answer. That did not remove the 0 values from the returning query.
    Any more thoughts would be welcome. I'm stumped.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I wouldn't expect it to. Because you have so many aliased fields depending on each other (BedNights depends on BN1, which depends on PS4, etc), my solution in post 3 isn't practical. I would simply base another query on this one, which will enable you to put a >0 criteria on BedNights.
    Paul

  8. #8
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Absolutely Right. With this thought I created a new query and solved the problem. Thanks heaps

Posting Permissions

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