Using Select Expert formula to select null date field

How do I select null date fields?

I have two tables that cannot be joined b/c their date fields are in different formats.  Therefore, I want to use the select expert to give me records where DateTable1 = DateTable2 (which are formula fields that convert the date fields to the same format) or where there isn't a date record in the second table:
DateTable1 = Date Table2 or DateTable2 is null
SMILEY7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frodomanCommented:
Report -> Edit Selection Formula...

((IsNull(DateTable2)) OR (DateTable1 = DateTable2))  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PWinterCommented:
Hi.

My two cents: remember, that
((IsNull(DateTable2)) OR (DateTable1 = DateTable2))
 would work, but
((DateTable1 = DateTable2) OR (IsNull(DateTable2)))  
would not!

Good Luck.
PW
0
frodomanCommented:
That's why I put the formula the way I did ;-)  But PW is right - the order of your clauses does matter in this case...
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

SMILEY7Author Commented:
Okay, maybe I need to give a little more data here...

DateTable1 is a date/time field as is DateTable2, since the times are different, the two fields are never exactly the same and therefore I cannot link the tables by the date field.

DateTable1 is set in the select expert to = {?DateRange}   a parameter field so that I only pull records from Table that are in the specificied date range.

What I really need is to display the info from Table2 only if the date is the same as the date in Table1.  I created formula fields for both dates to contain on the date (without the time), so that when they both occur on the same day, it matches.  Even when I rearrange the formula as you mentioned, I am still only getting info in my report when there is data in both tables...

({@CompletedDate} = {?Date Range}) and
((IsNull({@TLC Date})) OR ({@TLC Date} = {@CompletedDate}))
0
PWinterCommented:
Hi.

{@CompletedDate} and {@TLC Date} are formulae, that obtain data from fields, right?
Then first make sure that at each formula you have checked the fields on null. I guess one of the formula (likely {@TLC Date}) returns only valid fields but never nulls.

Good Luck.
PW
0
frodomanCommented:
I think PW is right.  @TLC Date probably returns a date all the time or returns an empty string sometimes but never returns a null value.  Check your TLC Date formula and make sure you're using the keyword null to return a null value when appropriate (remember - no quotes around the null either).
0
SMILEY7Author Commented:
Yes, {@CompletedDate} and {@TLC Date} are formulae that just truncate the date field.  {@TLC Date} therefore would not contain any null values.  I tried using the orginal date field in place of {@TLC Date} in the Isnull portion of the statement, but didn't get any different results.  

I'm really beginning to wonder if I can get a formula to do what I want.  Again, Table 1 date field must first be in the selected date range.  Then, if a record even exists in the 2nd table, I only want to display the data if that date field is also in the selected date range.  Maybe I need to be going with an IF/Then statement instead of OR b/c there really are 3 possibilities... there could be no records in Table2 to match other criteria, there could be a record that is there but is not in the selected date range, and finally there are those records in the 2nd table that meet all other criteria and are in the selected date range.  Make sense?  Typically, I would use a left outer join on the tables with the date field but b/c of the seconds never matching, I can't get around that without adding a new field to the table...

If you still think using the formula field for {@TLC Date} is the way to go, tell me a little more about how to get it to create a null value; haven't tried that one before.
0
bbgbpCommented:
Since you cannot link the tables directly, have you considered using a subreport? You can then use your formula for stipping the time off the datetime field as a link to the subreport. Create a similar formula in the subreport. Link by these formulas and show the field you need from table2.

Just a thought...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.