Seems Simple Enough

[draxx2755]

Hey there,
This seems simple enough but I seem to be having a brain fade or something. In my table I have a field named "Date_in" which signifies when a file came in. I also have a field, "Date_Comp" which is filled in when a task has been completed for that file. What I'm trying to do is put together a report to show files that are 30 days or more older than the "Date_In" and the "Date_Comp" is blank. Now the code for the "Date_Comp" blank is easy enough but I just can't seem to figure out how to show the files that are 30 days or more older than the date in.

For example, I thought maybe running a query for a range of dates in first and then querying those records for the ones that are still open and 30 days or more older than the "Date_in". Technically I shouldn't have to run 2 separate queries but I'm stumped as to how to get the records I need. If I use some thing like" Date_in >= Date_in + 30" it will look for specific dates in the "Date_In" field that meet that requirement, but if I have a set of records called up, let's say for November 2016, it will look for a "Date_in" of December 2 and up in the "Date_In" field. It obviously wouldn't find any because the current queried records are from November only. I can't call them up by the "Date_Comp" field being filled because there may be some where that field is actually not blank but the file is still not complete for other reasons. I can add those parameters to the query once I understand how to do it. I know this has something to do with aging but not sure how to approach it.

Hope this makes some sense. Thanks for taking a look. Oh, I will be gone from the office until Monday (11/30/2017).

Thanks,

Scott

[MoGrace]

Re: Seems Simple Enough

Hi Scott,
You didn't say how you want to display the query result - in a form or a report? However the simplest way is perhaps to add a numeric calc field to your table with this expression:

days_exp = IF(date_comp <> {}, date_comp-date_in, 0)

Now you can use this as your query filter: days_exp >= 30

[GGandhi]

Re: Seems Simple Enough

i think that days any job open is from that day the report is run
for example if date_in was 12/01/2016 then when the report is run the job is open for 1/27/2017 - 12/01/2016, 57 days
so i would design the report and then go to select records and fill in the filter

Date_In < (Date() - 30) .AND. Date_Comp = {}
or
date()-Date_in > 30 .AND. Date_Comp = {}

that should do it, i think.

edit: it will be good if you post what backend db you are using, this reply assumes that dbf is being used. the query will change if it were sql variant.

[draxx2755]

Re: Seems Simple Enough

Thanks guys, both are great approaches. For some reason I just could not wrap my head around it....lol. And Govindan, I am using A5 v11 only and no sql. I will be taking the results and exporting certain fields to produce an excel report that will be sent to one of our clients that are requesting which jobs are out past 30 days. I would prefer just to produce a report, but they like data that can be manipulated the way they want. Thanks so much for the ideas gentlemen, they are greatly appreciated!

Scott