Date end Calc Error 1

[endoflux]

Gentlemen--

I have this formula calculating the last day of the month for a given date...the given date is determined by a table of incrementing values substracting months off the current date (IE, for today I would receive 12 dates-- 11/30/2005, 10/31/2005...12/31/2004).

RSLTDAY: IIf([RSLTMONTH] In (1,3,5,7,8,10,12,0,-2,-4,-5,-7,-9,-11),"/31/",
(IIf([RSLTMONTH] In (4,6,9,11,-1,-3,-6,-8),"/30/",(IIf(Right(CStr(Date()),4)<>"2008","/28/","/29/")))))

For some reason, if the date meets the first set of criteria, I receive an output of #Error in Datasheet View for those records, but all values fitting into the remaining criteria categories print fine...

...but what makes it even stranger is that if I change the /31/ to /15/ (or any other number) it all returns just fine-- its as if Access believes there is no month with 31 days in it!

Ideas??

 

[SkipVought]

Why are you working with strings, when you could be using DATE FUNCTION!!!

Do you realize that REAL DATES are just NUMBERS?

Like today is 38673. What you SEE is a FORMAT (11/17/2005) and NOT a VALUE.

Don't mess around with "/31/" et al -- there's absolutely not point to it!

What are you ultimately trying to accomplish?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[endoflux]

Ultimately, I have a database of work order records, each with an open date and closed date, and several other milestone dates...I want to be able to report on them in Crystal in a dynamic fashion, giving me results for records Currentmonth --> 11 months back without entering a date range.

Here's where it gets messy and I'm admittedly lost: All the reports I want to generate involve more than 1 date field per record, and I want to show each record in as many places on the report as are valid for the criteria. I'm under the impression that in order to do this, i need to group my records by a date, not simply have them printing out by work order number. However, since each report calls for a different set of date fields, I can't group all my data by 1 specific date field, especially since other date fields for that record may not fall in that same month.

This brings me to creating this table of dynamic date records, which i intended to join into my data so if any of the date records matched the range of the current group, the record would print, and I could run formulas on it at that location to return 1's or 0's, later summarized and charted

That probably doesn't help...and this isn't a Crystal forum...but I'm bound and determined to figure this out one way or another...I appreciate any light you can shed!

 

[PHV]

To get the last day (as date) of a month:
DateSerial([Year value], 1+[Month value]), 0)

If you're only interested in the day (as number):
Day(DateSerial([Year value], 1+[Month value]), 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typos :~/
To get the last day (as date) of a month:
DateSerial([Year value], 1+[Month value], 0)

If you're only interested in the day (as number):
Day(DateSerial([Year value], 1+[Month value], 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[endoflux]

Sure is difficult to teach yourself these things when half the expressions available aren't in the access library in the program...hmmph.

Meanwhile, it would un-complicate things a bit if there were a function that I could take any given date for and return the last day of that month...?

 

[PHV]

I could take any given date for and return the last day of that month
Have you read and understood my last post ?
Last day: Day(DateSerial(Year[any given date], 1+Month[any given date], 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[endoflux]

Got it...The "0" confused me at first; i had discounted it as an out of an IIf, but when i read the description of the function, it made sense--

RSLTDATEEND: DateSerial(Year([RSLTDATESTART]),1+Month([RSLTDATESTART]),0)

For anyone reading this who might be curious, here's the function in english

Return the following date:

Add 1 Month to the input date
Return the "0" day of that month (last day of the previous month)
Return the same Year of the input date.

Works like a charm- thanks!