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