Date Formula Problem

I am a complete noob with crystal reports and am in real trouble with monthly billing, so any help would be greatly appreciated.
our billing period is from 7/31 - 8/30, and the reports are run on 8/31. We found a bunch of mistakes and had them fixed but now I need to be able to rerun
the report for that time period today. All my time formulas are below, please help.

Report Date

"For the Month of " & ToText(CurrentDate - 15, "MMMM") & " " & ToText(CurrentDate - 365, "yyyy") & " & " & ToText(CurrentDate - 25, "yyyy")

Current Year

If {orders.DATE} in Maximum(LastFullMonth) to CurrentDate - 1 then 1

Previous Year

If {orders.DATE} in Maximum(LastFullMonth) - 365 to CurrentDate - 366 then 1That's the problem with using CurrentDate - it only works on the actual date!
Replace the use of CurrentDate with a formula. Make the formula return the date you want to simulate running for. Then you'll only ever need to change one thing in the report to get it to run for whatever date you like.
Or take it one step further and use a parameter so you don't even have to change the report. You could still use an intermediate formula so that you can make it work however you want without having to alter the report much at all.|||How would I go about setting it up to just use a specified date range, I tried using DateValue and for some reason it would always come up with the year 1898.

Date Conversion

I am having trouble with a simple date conversion in a table. I have
a field that is stored in a legacy system as a text value. When I
bring the data into reporting services I need to convert it to a date,
however, some records have invalid date formats, which cannot be
converted. I tried to solve the problem using the following
expression, but I still get the dreaded "#Error" in the fields that
cannot be converted to date.
=IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
ABACCD.Value),nothing)Perhaps you could use regular expressions such as:
System.Text.RegularExpressions.Regex.IsMatch(StartDate,
"(((0[1-9]|[1-9])|1[012])[- /.]((0[1-9]|[1-9])|[12][0-9]|3[01])[-
/.](19|20)\d\d)|((January|February|March|April|May|June|July|August|September|October|November|December)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)|((Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)")
This returns false if the format is incorrect
"repettry@.fcbinc.com" wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
>|||On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
You're probably going to need to use custom code to do what you're
attempting to do with the IIF. Since IIF is a function call, the
entire statement is evaluated at run time so it's throwing the error
simply because it's seeing the invalid date value in the statement.
Something like this should work:
Public function ConvertDate (exp1)
If IsDate(exp1) = False Then
ConvertDate = Nothing
Else ConvertDate = CDate(exp1)
End If
End function
Then insert
=code.ConvertDate(Fields!AVACCD.Value)
in the appropriate text box.|||On Apr 13, 1:53 pm, "toolman" <t...@.infocision.com> wrote:
> On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> > I am having trouble with a simple date conversion in a table. I have
> > a field that is stored in a legacy system as a text value. When I
> > bring the data into reporting services I need to convert it to a date,
> > however, some records have invalid date formats, which cannot be
> > converted. I tried to solve the problem using the following
> > expression, but I still get the dreaded "#Error" in the fields that
> > cannot be converted to date.
> > =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> > ABACCD.Value),nothing)
> You're probably going to need to use custom code to do what you're
> attempting to do with the IIF. Since IIF is a function call, the
> entire statement is evaluated at run time so it's throwing the error
> simply because it's seeing the invalid date value in the statement.
> Something like this should work:
> Public function ConvertDate (exp1)
> If IsDate(exp1) = False Then
> ConvertDate = Nothing
> Else ConvertDate = CDate(exp1)
> End If
> End function
> Then insert
> =code.ConvertDate(Fields!AVACCD.Value)
> in the appropriate text box.
Thanks. The custom code worked great! I used the same concept to
convert numbers as well.

Date comparison logic in a trigger without hard-coding the year

I'm having trouble trying to figure out how to write logic into a trigger
that will find when a date in the Tax Due Date field, falls in a particular
quarter and was created after a set day of the year without hard-coding the
year information. I'm trying to avoid having to update the trigger.
Scenario: An extract of data for 1st Quarter tax payments is always sent to
the agent on November 7th of the previous year, 2nd Quarter on February 15th
of the same year, 3rd Quarter on May 15th of the same year, and 4th Quarter
on August 15th of the same year. (The extract is always sent out before the
quarter starts to allow for processing/payment before the tax is actually
due.) The Tax Due Date is auto-generated according to different country
laws. I want to send an email to the user whenever they generate a Tax Due
Date that falls into a particular quarter for which the data extract has
already been sent out. Basically if today's date = December 15, 2006 or
January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send an
email because December 15th or January 4th is after November 7th. This woul
d
apply until the day before the same quarter extract is sent out for the
following year, which in this example would be November 6th, 2007. The
extracts are always sent on the same day of the year. Any help would be
greatly appreciated!
--
Finn GirlUse a calendar table.
http://www.aspfaq.com/2519
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:E888E3D6-EB97-4DBA-B73E-19C6EAA12633@.microsoft.com...
> I'm having trouble trying to figure out how to write logic into a trigger
> that will find when a date in the Tax Due Date field, falls in a
> particular
> quarter and was created after a set day of the year without hard-coding
> the
> year information. I'm trying to avoid having to update the trigger.
> Scenario: An extract of data for 1st Quarter tax payments is always sent
> to
> the agent on November 7th of the previous year, 2nd Quarter on February
> 15th
> of the same year, 3rd Quarter on May 15th of the same year, and 4th
> Quarter
> on August 15th of the same year. (The extract is always sent out before
> the
> quarter starts to allow for processing/payment before the tax is actually
> due.) The Tax Due Date is auto-generated according to different country
> laws. I want to send an email to the user whenever they generate a Tax
> Due
> Date that falls into a particular quarter for which the data extract has
> already been sent out. Basically if today's date = December 15, 2006 or
> January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send
> an
> email because December 15th or January 4th is after November 7th. This
> would
> apply until the day before the same quarter extract is sent out for the
> following year, which in this example would be November 6th, 2007. The
> extracts are always sent on the same day of the year. Any help would be
> greatly appreciated!
> --
> Finn Girl