date formula

[mmwchac]

Version 10, W2k

I've been asked to modify a report written by someone else and I was wondering if someone could tell me what the "01" variable in this formula represents:

date(year({RHEADER.INVOICE_DATE}), month({RHEADER.CREATE_DATE}),01)

Thanks!

 

[TheBlondOne]

The ,01 at the end of the formula is in fact just inserting 01 at the start of the formula. This is so that when the formula is used in the report, crystal will read this formula as a date field i.e 01/07/2005 and so can be used in the select expert, parameters etc... if the 01 wasn't there then you would get errors as a date must have a day, a month and a year.

HTH

Steve

 

[mmwchac]

Thanks!!

 

[mmwchac]

If I want to change this formula to use it strictly for the year (dropping the month piece entirely), would I have to do something similar or could I just use:

date(year({RHEADER.INVOICE_DATE})

I an using this formula as a subreport link...

 

[lbass]

If you just want to link on year, then use:

year({RHEADER.INVOICE_DATE})

Assuming you have a year field or a matching formula in the subreport.

-LB

 

[mmwchac]

Odd - I changed it in both the main and the sub and I get an error, "a number is required here" in the main report.

 

[lbass]

year({table.date}) will always result in a number. {RHEADER.INVOICE_DATE} is of date datatype, isn't it?

-LB

 

[mmwchac]

It is Date/Time format in the main report which is using an Access table and the matching field in the sub is Date format using a table from a different database.

 

[lbass]

You are using a formula in each case, right? The formulas should be the same.

-LB

 

[mmwchac]

Actually, I am linking the transaction created date in the main report to the invoice date in the subreport. So, I am not using identical fields between the two reports (although they really mean the same thing). When I use the date(year({table.field}), the year of the subreport invoice date falls into the same group as the main reports transaction created year. So, it is working that way. When I unsupress the linking formulas, the date reads 6/25/1905.

But when I use year({table.field}), I get the error.
Maybe I'll just leave it alone - after all, it is doing what I want it to do. I just wanted to understand why it is working one way and not the other. Thanks LB!

 

[lbass]

The subreport results are totally dependent upon your linking. If you link on date fields from each of the main report and the subreport, you will ONLY get a subreport result when the entire dates exactly match. If you link on a formula from the main report to a formula in the subreport, with the formula like:

year({table.date})

The subreport will show all instances of the dates that fall within the year that matches the year of the date in the main report.

It really depends upon your report structure and the results you want to achieve.

-LB

 

[mmwchac]

Based on your last post, I just realized I left out an important piece and maybe it's the reason why it is working with the date(year({table.field}) syntax. The date formulas are my secondary subreport link. The primary subreport link is on Client ID.

Sorry about that - I hope I didn't waste too much of your time!

p.s.

 

[lbass]

A formula like:

date(year({table.date}))

...should result in an error, since it does not contain all the elements of a date:

date(year,month,day)

If you still need help, you need to explain your report structure (groups, etc.), where your subreport is located, and what you are trying to achieve by using the subreport.

-LB

 

[mmwchac]

In the main report, Group one is by Client ID, Group 2 is by Transaction Created Date with a change on Year. The subreport is in GF 2 and the linking formula date (transaction created date) is in GF1. The linking formula (invoice date) in the subreport is in the Report Header.

In the record selection of the subreport is the criteria:
{@ByYearLink} = {?Pm-@ByYearLink}

It's working so I don't need help in that area now. I just want to know WHY it is working!!!

 

[lbass]

If each formula is like:

date({transaction.createddate})

(or, date({invoice.date})

...then because the date in the main report is grouped by year, you will only get subreport dates that fall into that year AND which exactly match your main report date. The potential problem with this is that if you have a createdated date that is different by a few days from the invoice date, even though they are in the same year, you will not get the corresponding subreport record. If you want the subreport to show all dates within the corresponding year, you are better off creating a formula like:

year({transaction.createddate}) //or

year({invoice.date})

and then linking these two formulas together.

-LB

 

[mmwchac]

I understand. Thanks so much for your time (and knowledge!)