Criteria for query calculated field

[Eljefegeneo]

I have the following field in a query that is calculated:

  SDvCSTF: IIF([DPY] = -1 and [DPM] = -1, -1, IIF([DPY] = -1 and [DPM] = 0, 0, -1))

The results are exactly what I want. But then I want to omit any 'FALSE' or 0 so I make the citeria -1. Unfortunately it then asks me for the [DPY] and [DPM] values. I also tried 'true' as the criteria with the same result.

The codes for them in the query are:

  DPY: IIF(DatePart("yyyy",[ContractStarts]) = DatePart("yyyy",[StartDate]), -1, 0)
  DPM: IIF(DatePart("m",[ContractStarts]) = DatePart("m",[StartDate]), -1, 0)

In my table I have two dates, StartDate which is the original start date of the contract and ContractStarts which is the actual date of the start of the contract. While in 99% of the cases they are the same for new contracts, once in a while they are not. If they are not the same, it shows us that the client has delayed their contract by X number of days. So I want to exclude them. And yes, I need both dates. The else or final -1 (true) condition is for contracts that are renewals, that is, from previous years.

The first part of the first code listed determines if the year is the same for both StartDate and ContractStarts. The second is to determine if the month is the same. If the year is the same and the months are different, the 'false' reading, I don't want them included.

How can use a criteria in this calculated field, SDvCSTF to give me the results I want? Thanks.

 

[plog]

ow can use a criteria in this calculated field, SDvCSTF to give me the results I want?

By using the query that calculates them in another query. You can't calculate a field and use it in a calculation in the same query. You need to build yet another query and build the second set of calculated fields there.

However, if you would like to post some sample data from your table and then what you expect your query to produce based on that sample data, maybe we can eliminate the intermediate calculated field. Your logic leaves a lot to be desired. This statement:

SDvCSTF: IIF([DPY] = -1 and [DPM] = -1, -1, IIF([DPY] = -1 and [DPM] = 0, 0, -1))

is logically equivalent to this statement:

SDvCSTF: IIF([DPY] = -1 and [DPM] = 0, 0, -1)


You've got an unneeded iif statement in there. It would be best to just post data to demonstrate what you want to occur. Post 2 sets:

A. starting sample data from your table. Include table and field names and enough data to test all cases.

B. expected results of A. Show what you expect your query to produce when you feed it the sample data you provide in A.

 

[Eljefegeneo]

I will post it a little later. Thanks.

 

[Eljefegeneo]

Attached is the sample DB with the pertinent data and two queries. "qry1" displays all the data from the table but does not exclude the one record #7548 which does not meet the criteria described in my first post. "qry2" a query made from the first query does show the correct data. Of course I would only have the actual date displayed in qry2, not needing the StartDate or ContractStarts fields.

Is this the correct way to solve my problem or is there a simpler and more efficient way? Or is this one of those "move on to the next problem" and marked 'solved'?

Thanks.

 

[plog]

I'm confused. If you have queries that display what data you want then your done. If not, please provide me with actual data of what you want your query to produce.

A. starting sample data from your table. Include table and field names and enough data to test all cases.

B. expected results of A. Show what you expect your query to produce when you feed it the sample data you provide in A.

You gave me A, now give me B.

 

[Eljefegeneo]

B would be the second query, that is, all records except for the one mentioned. Except, after waking up this AM I realized that that record would be included next month because the "ContractStarts" date is in August. So the code needs further tweaking.

The correct result would be all those shown in qry2 plus any records where the "ContractStarts" while not = "StartDate" where the date is =< Date().

 

[plog]

B would be the second query...except for...Except...So the code needs further tweaking.

Too many words and exceptions, just provide data please. Last chance--you provided A, now provide B.

 

[Eljefegeneo]

Thank you for your reply. The data is in the table tblCalcQuery. It lists 41 records that have active contracts, or rather meet the criteria that the DateCancelled is Null (or = "").
What I want to see is all those that meet the above criteria and:
1. Whose ContractStarts date is equal to the StartDate.
2. Whose ContractStarts date and StartDate have the same DatePart of year ("yyyy") but the DatePart of month ("m") is different for these two fields (meaning a delay start) but the ContractStarts date is in the current or previous month. (This is the criteria that I have not yet figured out).
3. And excluding those where the DatePart of year is the same for both fields but the DatePart for month is greater (next month or more) for the Month.


Data Examples:

    [U]StartDate[/U]             [U]ContractStarts[/U]     ActualDate                        [U]Keep[/U]
  3/1/13                  3/6/2016               3/1/13                         Yes
  4/7/16                  5/7/16                  5/7/16                          Yes
  5/6/16                  5/6/16                  5/6/16                          Yes
  7/5/16                  8/15/16               8/15/16                          No

tblWantedData is B. It has 40 records that meet this criteria.



I hope I have explained this correctly.

 

[Minty]

I can't get the words in your description to make sense.

Rather than using datepart to describe it just use plain English. It might help if the fields were better labeled as well. Describe in simple terms what StartDate, ContractStarts and the most confusingly named ActualDate really represent.

 

[plog]

Now, I'm ready to read words, but just those that answer my questions:

Why is 7548 excluded from this expected results?

Please limit your explanation to 1 sentence.

 

[Eljefegeneo]

Because the actual start date of the contract, the ContractStarts date is in the next month, it was delayed by one month and is therefore not considered a current Active contract; only those contracts that will be active in July 2016 are desired.

Sorry, long sentence.

 

[Minty]

Have a contract created date, which would indicate when the contract was put on your system, and never changes, then have a ContractStartDate which is the actual date the contract will become or did become valid. This can be then updated if the contract is renewed.

 

[plog]

7215 has a [ContractStarts] date even later than 7548. How come it is in the results?

 

[Eljefegeneo]

Because that is a renewal date.

 

[plog]

You can't use terms that' aren't field names. I have no idea what a start date is, what a renewal date is, what anything but your field names are.

Please tell me using field names why 7215 isn't included.

 

[Eljefegeneo]

The [ContractStarts] date is more than one year after the [StartDate], signifying that it is a renewal of a contract and therefore is included.

I am sorry that I am not explaining myself more clearly. Your patience is much appreciated.

 

[plog]

You're doing fine. The key to generating code of any kind is writing it out in english first, making sure its air-tight accurate and then converting it to code. For criteria, you should chose to Include or Exclude--not both. You either start with every record and then identify what to exclude or start with no records and then identify what to include. Don't mix/match includes and excludes it gets too confusing.

With that said, here is what I have interpreted your criteria as in English:

Exclude records where the [ContractStarts] is next month and it is also less than a year after the [StartDate].

If that's right this is the query you want:

SELECT tblCalcQuery.SequenceNumber, tblCalcQuery.DateCancelled, tblCalcQuery.StartDate, tblCalcQuery.ContractStarts
FROM tblCalcQuery
WHERE (((IIf(DateDiff("yyyy",[StartDate],[ContractStarts])>0,1,0)+IIf(DateDiff("m",Date(),[ContractStarts])<1,1,0))>0))
ORDER BY tblCalcQuery.SequenceNumber DESC;

If not, let me know. The code above does generate the results you want, but may fail on other data if my criteria statement is incorrect.

 

[Eljefegeneo]

Thank you. Your SQL statement makes sense to me. Unfortunately I cannot try it until Sunday as I am away from my regular computer until then. Will let you know the outcome.

 

[Eljefegeneo]

Well, I finally had a chance to test it on the real data. Of course it gives me the results I was looking for.

I never thought about using two IIF statement as you did, IIF#1 + IIf#2 to get a result.

I had to add one thing to my query to get the actual first starting date of a new contract:

[FONT=&quot]ActualDate:  IIf(DateDiff("yyyy",[StartDate],[ContractStarts])>0, [StartDate], [ContractStarts])

So simple, probably why I couldn't think of it. Always seem to make it as complex as possible without thinking it through first.


Thank you. Will make me THINK first; decide what outcome I want before writing some complex code.

[/FONT]