Solved

Using Select Expert formula to select null date field

Posted on 2003-11-06
8
2,065 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 100 total points
Comment Utility
Report -> Edit Selection Formula...

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

Expert Comment

by:PWinter
Comment Utility
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
Comment Utility
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
 

Author Comment

by:SMILEY7
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Assisted Solution

by:PWinter
PWinter earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now