Crystal Reports Displaying records based on date

Technical Questions

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

Topic: Displaying records based on date

Author	Message
jbattelle Newbie Joined: 06 Feb 2012 Location: United States Online Status: Offline Posts: 25	Topic: Displaying records based on date Posted: 14 Jan 2013 at 5:15pm
	Ok, so I have a date filed that is a string, that I am converting to a date field using the formula below. It works percectly. Problem is now, that I am trying to display ONLY records that are older than the last full month. For example, today is 01/14/13, and I only want to see records from 11/12 or older. Please know that the converted date field is in the following format MM/YYYY. The original data form the DB is a string in this format: YYYYMM NumberVar YearPart; NumberVar MonthPart; WhilePrintingRecords; YearPart:= tonumber({PremiumSoa.YrMo}[1 to 4]); MonthPart:= tonumber({PremiumSoa.YrMo}[5 to 6]); Date (YearPart,MonthPart,1)
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 Jan 2013 at 4:42am
	change it to *whilereadingrecords* or change it to *date(tonumber({PremiumSoa.YrMo}[1 to 4]),tonumber({PremiumSoa.YrMo}[5 to 6]),1)* then you can use it in the select expert. I beleive the whileprinting records happens in a data pass that occurs after the select expert data pass. if you data set does not allow for future dataes to be entered you can then use something like this in the sleect statement *NOT({PremiumSoa.YrMo} in monthtodate or {PremiumSoa.YrMo} in lastfullmonth)*
	IP Logged

jbattelle Newbie Joined: 06 Feb 2012 Location: United States Online Status: Offline Posts: 25	Posted: 15 Jan 2013 at 8:52am
	DBlank - Thanks, as always, for your help. I thnk I explained my issue poorly. The formula I posted I don't want to change. That simply converts the string to a date type and works great. I need a formula that will allow me to filter out records that are older than the last full month. So, I am putting the formula in the report (@ConvertDate) and trying to filter off of that, but the select expert won't let me choose that field. I suspect because it is not a DB field. Does that help clear up what I need? Thanks, Jason
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 Jan 2013 at 10:27am
	I was trying to explain that although the original formula works to convert the string to a date it is using the *whileprintingrecords* function to do the conversion. Crystal does things in "passes". the select statement happens in an early pass, the 'whileprintingrecords' happens in a later pass. Therefore the formula result does not exist until after the record selection has already happened. This is why the formula wont appear as an option to use. BY changing the fomrula to not use whileprinting records (to either option I gave you) it creates the result before the select expert pass and the formula will now appear as an option. I would then use the formula as below to get all values from last month or this month only (in my last post i accidently used the text field instead of the formula field) *{date_formula} in monthtodate or {date_formula} in lastfullmonth* Edited by DBlank - 15 Jan 2013 at 10:33am
	IP Logged

jbattelle Newbie Joined: 06 Feb 2012 Location: United States Online Status: Offline Posts: 25	Posted: 15 Jan 2013 at 10:32am
	Gotcha. Let me give that a try.
	IP Logged

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