Need help with crystal report

[Pasi12]

HI All,

I keep getting errors with below formula: All I want to say is give me the date range inputed 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}

 

[kray4660]

First off. The insull is a function that you have to enclose the argument with parenthesis. Also on your first line you did not put in a range [1 to 4] for the year part of the string. The date function can take the individual parts of a date. (i.e., date({XXXBO_Hosp_Charges_.Admitt_Date}[1 to 4],{XXXBO_Hosp_Charges_.Admitt_Date}[5 to 6],{XXXBO_Hosp_Charges_.Admitt_Date}[7 to 8]). I am guessing that the string format for your field is YYYYMMDD. I have never used the substrings like you have, I have always used the various functions, LEFT, RIGHT, and MID. But I think they should work.

 

[kray4660]

Made a slight mistake (been a while since I did something like this). For each of the arguments in the date command. You need to use the VAL function to convert the string into a number.

 

[Pasi12]

Thanks Kray, Yes I missed the range [1.4] Can you modify my formula and sent it back to me? I am not sure how to modify this? I think my format is an old format date that turns string to actual date 4/8/2014 like you said .

Another word what the best way to minimize this code /shorten it?
Thanks!

 

[pmax9999]

Based on your code I am guessing the {XXXBO_Hosp_Charges_.Admitt_Date} field is TEXT in the format yyyyMMdd. I am also assuming the field can be NULL (as opposed to an empty string which needs to be dealt with differently).

Try this

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}

Cheers
Pete

 

[Pasi12]

Thanks! this gets me going and this works partially. your assumption is correct the date is empty, sometimes the clinicians don't input it as they are suppose to. This code gives me everything like last years too but I want to get the date range between "from" to "to" only, including the admit _dates that are blank for these dates ranges that I am picking? hope I am clear?
Thanks!

 

[pmax9999]

Sorry, but it's not clear (to me at least).

Perhaps you could post a sample of what data is being returned, and clearly explaining how it differs from what is expected.

In the meantime, and assuming that the Record Selection contains other criteria other than the part that deals with {XXXBO_Hosp_Charges_.Admitt_Date}, you should enclose the entire code that I provided within brackets, ie a ( and a ).

Try amending the code as follows (to del with empty strings as well as nulls:

(
	Isnull({XXXBO_Hosp_Charges_.Admitt_Date}) 	or
	Trim({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} 
)