Crystal Reports Trying to get prior day data formula

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Trying to get prior day data formula
	Page of 2 Next >>

Author	Message
u000098 Newbie Joined: 28 Sep 2009 Location: United States Online Status: Offline Posts: 8	Topic: Trying to get prior day data formula Posted: 28 Sep 2009 at 9:45am
	Every morning i generate reports to see prior day data. I'm looking for some assistance on creating a formula that will showcase prior date information. When i do a "browse field data" this is what i see: Type: String Length: 30 xx/xx/xxxx xx/xx/xx xx/xx/xx xx:xx:xx Your prompt assistance will be greatly appreciated.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 28 Sep 2009 at 9:55am
	try: date(table.field)=dateadd('d',-1,currentdate)
	IP Logged

u000098 Newbie Joined: 28 Sep 2009 Location: United States Online Status: Offline Posts: 8	Posted: 28 Sep 2009 at 11:12am
	Thanks DBlank for the quick turn around... But i get a "Bad Date Format String" error. The table name is - Tec_T_EVT_REP Dates under - Date_Event Should it look like this: date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate) I'm really terrible on this formula thing, i apologize. The way i get the prior day today is by right clicking the "Date_EVENT" title, then choosing "Select Expert" then "starts with", then typing in what ever the prior date was. Example "Sep 27". Not sure if this is helpfull or not, but this is my daily process. Thanks again.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 28 Sep 2009 at 11:28am
	2 questions: When you browse the data you were seeing strings in 3 different formats, one with xx/xx/xxxx a second with xx/xx/xx and a third with xx/xx/xx xx:xx:xx. Is that correct? Secondly, is this in a month/day/year or day/month/year format in the strings?
	IP Logged

u000098 Newbie Joined: 28 Sep 2009 Location: United States Online Status: Offline Posts: 8	Posted: 28 Sep 2009 at 11:50am
	1. Yes 2. Month/Day/Year Thanks again.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 28 Sep 2009 at 12:17pm
	You had the syntax correct: date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate) To try and figure this out make another formula field (call it datetest or whatever) as: isdate({TEC_T_EVT_REP.DATE_EVENT}) Now place the formula field and the Date_Event field on your detail section. YOu will see the date event field and a TRue or False next to it (the datetest formula) What are sample records that are showing up as False? Are most True but some are false? I am trying to figure out why the the other formula failed trying to convert your data to a daet field.
	IP Logged

u000098 Newbie Joined: 28 Sep 2009 Location: United States Online Status: Offline Posts: 8	Posted: 28 Sep 2009 at 1:19pm
	k..so: I got lot's of prior dates data, with 4 different dates format: Picked a random day: 1. Sep 25, 2009 xx:xx:xx 2. Sep 25 xx:xx:xx 3. 09/25/09 xx:xx:xx 4. 09/25/2009 Did not return any true or false.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 28 Sep 2009 at 1:45pm
	Yikes. That is some bad data consistency. That helps some but sounds likeyou did not add the formula field for the T/F. Sorry I can't give you a straight formward answer here but with that many variations in the data it is hard to make a conversion from a string to a date type. My guess is that it is choking on the likes of the second example where it has no year. When there is no year how do you know if the data is from the previous day or the previous year and a day? Try and use an isDate function in your select criteria. You can either filkter all the records that are not or include them and see if there is a consistent patternt o which fields are not able to be converted. So if you put this as your select criteria: if isDate({TEC_T_EVT_REP.DATE_EVENT}) then date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate) else NOT(isDate({TEC_T_EVT_REP.DATE_EVENT})) It should give you all the records from yesterday and all the records that are not considered real dates. What do you get back from this?
	IP Logged

u000098 Newbie Joined: 28 Sep 2009 Location: United States Online Status: Offline Posts: 8	Posted: 28 Sep 2009 at 2:00pm
	Tell me about it, is a mess here. =) I didnt add the formula, sorry. I tried, maybe i'm doing it wrong.. Now with the new statement, i only get these formats: Sep 25 xx:xx:xx Sep 25, 2009 xx:xx:xx Thanks again for your outstanding assistance DBlank, you're the man!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 28 Sep 2009 at 2:22pm
	OK from what I have gathered you likely added my select statment along with your "Like Sep" statement and you ended up with the 2 records that were on the your random Sep 25 selection and that are also not considered a date converted field. YOu could try and match the dateswhen it is convertable and then match the first 6 characters when it is not... if isdate({TEC_T_EVT_REP.DATE_EVENT}) then date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1, currentdate) else left({TEC_T_EVT_REP.DATE_EVENT},6)=totext(dateadd('d',-1, currentdate),'MMM dd')
	IP Logged

Page of 2 Next >>

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.035 seconds.