Filtering Query on selection problem

[Exhausted]

Hi all
I'm pulling running sums from a Expressiong Build query on a subform in a report using a combox to filter by date. I can see the data ok, however, if a field is "retired", it shows the running sum backwards. If the field is "Not Retired", all is well. The report is to show archived daily activity, with a running sum on fields to the date entered in the criteria. The subform is not linked.

Really hard to explain! Sorry....

I'm using
ActDate: <=forms!frmcmbdate!cmbdate in criteria. Retired = No
ActDate: >=forms!frmcmbdate!cmbdate, Retired = Yes

If anyone has a clue what I'm trying to say, would love to hear from you how I can get my running sums if Retired in other order.

Thanks in advance.
J

 

[Guus2005]

No, not a clue.

Could you show the entire SQl statement?

HTH

 

[Exhausted]

Yep, confuses me too! This is a subform on a main report.
Thanks for having a look anyway

Basically, if ABC has been retired on 10/3/12, then I try to print off an archived report from say 1/3/12 (because someone has "misplaced" the original!!!), I want to see the sum results for ABC up to the 1/3/12. If after 10/3/12 then I don't want to see ABC at all. What I'm currently getting is if the report is reprinted for the 1/3/12 then it shows the sum of ABC from 10/3/12 backwards.

SQL Below

SELECT tblDailyData.ID, tblDetails.TypeCode, tblDetails.TJ, tblDetails.TUVs, IIf([TypeCode] Like "*ABC*",[qryItemCodes]![DueDate],"") AS DateDue, tblDetails.DailyID, tblDailyData.DateActual, qryItemCodes.Retired, qryItemCodes.DateRetired
FROM tblDailyData INNER JOIN (tblDetails LEFT JOIN qryItemCodes ON tblDetails.TypeCode = qryItemCodes.ItemCode) ON tblDailyData.ID = tblDetails.DailyID
WHERE (((tblDailyData.DateActual)<=[Forms]![frmCmbDate]![cmbDate]) AND ((qryItemCodes.Retired)=No)) OR (((tblDailyData.DateActual)>=[Forms]![frmCmbDate]![cmbDate]) AND ((qryItemCodes.Retired)=Yes))
ORDER BY tblDetails.TypeCode DESC , tblDailyData.DateActual;

Cheers.

 

[Guus2005]

The IIF statement expects a boolean

IIF boolean then X else Y

In your query [TypeCode] Like "*ABC*" does not produce a boolean.

You have to split up your query into two queries.
The first one to produce the DateDue (query1)
and the second one to produce the final result joining query1

Query1 could look like this:

SELECT tblDailyData.ID, [qryItemCodes]![DueDate] FROM tblDailyData inner join qryItemCodes ON ??=??
WHERE [TypeCode] Like "*ABC*"

and then left/right join this query with the final one.

If this doesn't work for you, post a sample database

HTH

 

[Exhausted]

Thanks for trying, I have given up! I've decided to just create a print to PDF button from Daily Entry Form so they can archive on server instead, that way it will always be there if they need it.

Appreciate your help Guus2005.