QUERY NOT RETURNING RECORD THAT APPEARS TO FIT CRITERIA

[M1ndbender]

I have a filed that I have modified with the following value

IF([Step1].[GL_DATE] IS NULL) THEN ([Projects].[PRJ_LAST_UPDATE_DATE]) ELSE ([Step1].[GL_DATE])

This step is working fine and associating the date to the test record that has a null GL_DATE.

The issue is when I try to limit the data by date using the following Detail Filter, the record in question is not returned
[GL_DATE] between _first_of_month( add_months( current_date , -2 ) ) and _last_of_month( _add_months( current_date , -2 ) )

The only record not returning in my test is the one that started out as a null date field.

Any ideas?

[dougp]

Your question is perplexing.  I think you didn't include enough information. 

Do you have two queries, Step1 and Projects, that you have joined into a third query?

IF is not necessary in this context, try:
coalesce([Step1].[GL_DATE], [Projects].[PRJ_LAST_UPDATE_DATE])

Is that data item in your downstream query named GL_DATE?  So your filter refers to the data item in the same query, not to [Step1].[GL_DATE]?  Or are you saying that the filter is in the Step1 query?

[M1ndbender]

The filter is built in Step 2 ( query step 1 joined to query projects). It is then passed through step 3 (query step2 joined to another query) as Step3.GL_DATE and then through step 4 (query step3 joined to another query)as Step4.GL_DATE.

The record in question that starts out with a null GL_DATE then gets the value from [Projects].[PRJ_LAST_UPDATE_DATE] appended to it in Step 2 returns all the way through to Step 4 UNLESS I add the detail filter [GL_DATE] between _first_of_month( add_months( current_date , -2 ) ) and _last_of_month( _add_months( current_date , -2 ) ).

Thank you for your input in this. I appreciate it

[M1ndbender]

doing some more research on the issue

I added 2 data items (first of month and last of month) to see the values that were being returned by the above query. The first of the month field is populated with the first of june for every record returned except 1 - the record I am having issues with. The last of the month is returning a value for every record including the one that I am having issues with.

If I run >=2021-06-01 and < 2021-07-01 - the record returns.

It is all about the query [GL_DATE] between _first_of_month( add_months( current_date , -2 ) ) and _last_of_month( _add_months( current_date , -2 ) ) - for some reason it does no return the record even though almost every query sees it as June 2, 2021 and returns it properly

[dougp]

What if you put the filter in Step4?

[Penny]

Couldn't you add an OR on your filter to include the null date? 

[M1ndbender]

Same result if I move it to step4.

I can't do the or is null because I will get many records that are not for the month of June. That is why I moved the date over if the field was null but for some strange reason it does not want to play nice