Formula for Filtering by Date Field

[bigdavidt]

I have a report that draws on data tables in a vertical market application. One of the fields is a numeric field in which the entries designate the year and the month: 200901 for January, 200902 for February, and so on. I am trying to set up a formula that will pull records for the year that run up through the present month. I have tried things like CStr(tablename.dtfield) <= CStr(Year(CurrentDate())) & CStr(Month(CurrentDate())), but so far no luck. Any suggestions as to proper syntax?

Thank you in advance for your help.

Also, if there s a better forum for this kind of question, please feel free to enlighten me.

 

[WaelYassin]

You have to extract the month and year from your number first.
// Suppose you field is called x
Right(X,2)<= Month(CurrentDate) AND Left(X,4) <= Year(CurrentDate)

 

[Madawc]

Try DatePart, lots of useful options. You can also use Year({your.date}) and Month({your.date})

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try:

totext({tablename.dtfield},0,"") <= totext(Year(CurrentDate),0,"") + totext(Month(CurrentDate),"00") and
left(totext({tablename.dtfield},0,""),4) = totext(year(currentdate),0,"")

-LB

 

[Gamblersgobroke]

I’m totally new to Crystal so you need to talk to me as if I were two.

We have Crystal XI and I need to create a twelve month Trend report for our vendors. Starting with January and adding each month after as the year progresses with a running total column.
At this point I’m totally clueless. I have the vendor number and vendor name and created a formula field that brings in the amounts for January, I also created one for February but when I run the report for February, January shows up as zeros. Any help would be greatly appreciated; I’m looking into taking some online classes soon.

 

[CR85user]

Please give some examples of the date as it shows in your report. Also if you right click the field and let us kknow the datatype it shows (Is it String / Number / Date).

If you give a couple of examples of the date data then I am confident that we can guide you in retreiving the correct data.

Note - it would also help if you post your current selection criteria. Go to Report -> Selection Formulas -> Record.

Copy paste the contents with your reply.

If the date field is a formula you have created yourself then please post the content of that or any other nested formulas (Nested formulas are formulas within formulas).

Also can I point you at a couple of good sites to get started on reading up:

http://www.chelseatech.co.nz/pubs.htm

http://www.crystalbook.com/crtcr/extras/10/AppA.pdf

(Is for Cr 10 but most features relevant for XI)

http://kenhamady.com/

https://forums.sdn.sap.com/forum.jspa?forumID=300&start=0

(Obviously not as good as TekTips but can occasionaly cover an interesting or basic subject)

Read the FAQs on here also:

http://www.tek-tips.com/faq.cfm?pid=767

Good luck....

'J

CR8.5 / CRXI - Discovering the impossible

 

[CR85user]

Apologies - I missed entirely your descriptions as yyyymm (200901) etc.

Using any of the solutions above will give you an addressable date and you can query on whatever basis suits you best.

E.g.

(Dateformula) in yeartodate

or

(Dateformula) > Minimum(yeartodate)

etc etc

Read up on date ranges and dates to find out more.

'J



CR8.5 / CRXI - Discovering the impossible

 

[Gamblersgobroke]

First, Thank you all for your suggestions.

Sorry for not giving enough information.

Did some reading last night and this is the formula I used. It was a lot easier then I thought.

My MNTH is a two charter numeric filed and the year is a separate field of four charters:

I created a formula for each Month of the year; that simply states.

If {LIB.MNTH}= 1 then {LIB.PHPPMT} ELSE 0
If {LIB.MNTH}= 2 then {LIB.PHPPMT} ELSE 0 etc..

I also created a formula for the Company Name, as we have seven properties that states.

If {LIB.COMP} = ' 100' then "Comp. 100 Company Name, Location"

ELSE if { LIB.COMP} = ' 101' then "Comp. 101 Company Name, Location"

ELSE if { LIB.COMP} = ' 102' then "Comp. 102 Company Name, Location"

I then drag the formulas for Jan, Feb, March, etc… into the Detail of the report and created a summary for each month and placed it into the group header and hid the remaining headers and footers, Created a report header and drag the Company Names into it, which gives me a report that looks pretty much like this plus a total column.

Company # Name ………………..
Vendor Payments For: MNT/YEAR

Vendor # Vendor Name Jan Feb March April etc…
30012404 A B C COMPANY 2,855.70 6,168.60 0.00 12,337.20