Solved

Using Select Expert formula to select null date field

Posted on 2003-11-06
8
2,066 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

16 Experts available now in Live!

Get 1:1 Help Now