| Author |
Message |
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
  
Topic: need help with formula
Posted: 08 Apr 2014 at 8:38am |
|
HI All,
I keep getting errors with below formula: All I want to say is give me the dates including date range for form/to that are null.
Any help is appreciated!
I have to change the date into string because the date is in sting.
(
isnull date({XXXBO_Hosp_Charges_.Admitt_Date}[5 to 6]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date}[7 to 8]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date} )in {?From} to {?To})
or
date({XXXBO_Hosp_Charges_.Admitt_Date}[5 to 6]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date}[7 to 8]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date}[1 to 4]) in {?From} to {?To}
|
 IP Logged |
|
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 08 Apr 2014 at 10:50am |
|
this should be what you need
isnull({XXXBO_Hosp_Charges_.Admitt_Date}) or
date({XXXBO_Hosp_Charges_.Admitt_Date}[5 to
6]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date}[7 to
8]+"/"+{XXXBO_Hosp_Charges_.Admitt_Date}[1 to 4])in
{?From} to {?To}
|
| IP Logged |
|
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
Posted: 08 Apr 2014 at 11:09am |
Thank you Kostya! this works! but it give me everything like from last year as well. I am triggering on my
{?from} to {?to} date and it has to be within these dates. 
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 08 Apr 2014 at 12:34pm |
|
show an example of data from this field
{XXXBO_Hosp_Charges_.Admitt_Date}
|
| IP Logged |
|
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
Posted: 08 Apr 2014 at 1:02pm |
|
what kind of example? its just a date? not sure what you are looking for? how do I attach it here?
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 08 Apr 2014 at 1:11pm |
|
i just need to know
the format of the field like
yyyymmdd
or is it
mmddyyyy
|
| IP Logged |
|
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
Posted: 08 Apr 2014 at 1:41pm |
|
ok its yyyymmdd
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 08 Apr 2014 at 1:46pm |
|
try this
isnull({XXXBO_Hosp_Charges_.Admitt_Date}) or
date(left{XXXBO_Hosp_Charges_.Admitt_Date,4}+","+mid{XXXBO_Hosp_Charges_.Admitt_Date,3,2}+","+right{XXXBO_Hosp_Charges_.Admitt_Date,2})in {?From} to {?To}
|
| IP Logged |
|
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
Posted: 08 Apr 2014 at 1:53pm |
|
no its not working getting error for unknown field, it looks like you modified inside the actual field. All I am trying to do is to get the dates within my range "from" to "to" including the dates that are empty in the field {XXXBO_Hosp_Charges_.Admitt_Date}.
|
| IP Logged |
|
pasi12
Newbie
Joined: 08 Apr 2014
Online Status: Offline
Posts: 7
|
Posted: 08 Apr 2014 at 1:58pm |
|
I changed your code to :
isnull({XXXBO_Hosp_Charges_.Admitt_Date}) or
Date(Left({XXXBO_Hosp_Charges_.Admitt_Date},4) + '/' + {XXXBO_Hosp_Charges_.Admitt_Date}[5 to 6] + '/' + Right({XXXBO_Hosp_Charges_.Admitt_Date},2)) in {?From} to {?To}
to correct the formatting but I am not getting the "ISNULL" records, only the records that have been populated in the field {XXXBO_Hosp_Charges_.Admitt_Date}. sometime the nurse don't input this field and leave it blank. that's what I want to get as well.
Thanks!
|
| IP Logged |
|
|
|
