?
Solved

Using Select Expert formula to select null date field

Posted on 2003-11-06
8
Medium Priority
?
2,074 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 400 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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

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 400 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 200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

719 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