Solved

Using Select Expert formula to select null date field

Posted on 2003-11-06
8
2,072 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:SMILEY7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 100 total points
ID: 9695526
Report -> Edit Selection Formula...

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

Expert Comment

by:PWinter
ID: 9696680
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9696704
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:SMILEY7
ID: 9696877
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
 
LVL 6

Assisted Solution

by:PWinter
PWinter earned 100 total points
ID: 9696989
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9697027
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
 

Author Comment

by:SMILEY7
ID: 9697063
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
 
LVL 1

Assisted Solution

by:bbgbp
bbgbp earned 50 total points
ID: 9703680
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question