Date Add

[goslincm]

Hi, I am trying to compute some due dates and here is how it should work:

DueDate: IIf([2000 population]>4000

Then I need to take field [FiscalYearEnd] which represents a two digit month of the year, ADD 6 months to that number and ADD to that the value of the current year.

So if the population is over 4000, and they have a fiscal year ending of March, their due date should be: September of 2007

Can anyone help me with this?

 

[lespaul]

what if the fiscal year ends in July - December? The due date will always be in the NEXT year....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[randy700]

Something like...

DueDate: IIf([2000 population]>4000, dateadd("m",6,[FiscalYearEnd]) & iif([FiscalYearEnd] > 6, Year(Now()) + 1, Year(Now())), [FiscalYearEnd] & Year(Now()))

Randy

 

[goslincm]

Lespaul, if the fiscal year ends in July, or September, etc then yes, I still need to take that month 07 add six more month grace period to it, and slap on the following year.

 

[goslincm]

Randy700, when I ran that, my final value came out with: 7/11/19002008

That was for someone with a FiscalYearEnding in December(12)

 

[lespaul]

Ok, so from January to June it would be the current year and July to December the following year. I think Randy has a viable solution for you.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Yes LesPaul that is correct, Jan-June current year July-Dec following year.

But when I ran his, the value displayed as 7/11/19002008

 

[goslincm]

Actually, when I look closer at my data after running that query, the values in my [population] field also changed.

 

[lespaul]

Nothing Randy provided would change any data...what's the entire SQL statement?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

SELECT [Local Unit DB-Contact Info].[County Name], [Local Unit DB-Contact Info].[Type-Name], [Local Unit DB-Contact Info].LocalUnitType, [Local Unit DB-Contact Info].Type, [Local Unit DB-Contact Info].[Office ID], [Local Unit DB-Contact Info].CountyCd, [Local Unit DB-Contact Info].LocalUnitCd, [Local Unit DB-Contact Info].Name, [Local Unit DB-Contact Info].FiscalEndMM, [Local Unit DB-Contact Info].[Contact Name], [Local Unit DB-Contact Info].[Contact Title], [Local Unit DB-Contact Info].[Contact Address], [Local Unit DB-Contact Info].[Contact City], [Local Unit DB-Contact Info].[Contact State], [Local Unit DB-Contact Info].[Contact Zip Code], [Local Unit DB-Contact Info].[Contact Phone Number], [Local Unit DB-Contact Info].[Contact Fax Number], [Local Unit DB-Contact Info].[Contact Email], [Local Unit DB-Contact Info].Note, [Local Unit DB-Contact Info].[No audit needed], [Local Unit DB-Contact Info].BIENNIAL, [Local Unit DB-Contact Info].[EVEN AUDIT YEAR], [Local Unit DB-Contact Info].Auditor, [Local Unit DB-Contact Info].[Open File], [Local Unit DB-Contact Info].[2000 Population], [Local Unit DB-Contact Info].[TYPE-NAME2], [Local Unit DB-Contact Info].[SPECIAL LETTER], [Local Unit DB-Contact Info].WATCH, [Local Unit DB-Contact Info].AuditDueBY, IIf([2000 population]>4000,DateAdd("m",6,[FiscalEndMM]) & IIf([FiscalEndMM]>6,Year(Now())+1,Year(Now())),[FiscalEndMM] & Year(Now())) AS DueDate

FROM [Local Unit DB-Contact Info]

ORDER BY [Local Unit DB-Contact Info].Name;

 

[goslincm]

My best example is one companies fiscal year ends in Dec. So their audit should be due no later than June of 2007 yet after running the query is displays: 7/11/19002008

 

[PHV]

Perhaps this ?
DueDate: DateSerial([EVEN AUDIT YEAR],[FiscalEndMM]+IIf([2000 population]>4000,7,1),0)

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

 

[goslincm]

Well the unfortunately part of that PHV is that staff have not used that [Even Audit Year] field.

Maybe I haven't been clear enough. The field [FiscalEndMM] is a 2 digit numeric field representing the month the companies fiscal year ends. Audits are due annually if the population is over 4,000 yet they really have 18 months before the audit is considered LATE.

I haven't thrown the other half of this out here but if the population is less than 4,000 their audit is not due annually, but every 2 years, but again not considered late until 6 months beyond the 2 year period.

I am open to any way of setting this up. Either a query that calculates the correct due dates and then I can add six months onto the due date for late audits, or working it all into the initial query.

 

[randy700]

I think you're getting the 1900 in your results because the DateAdd function requires a date argument. What we are giving it is only a number. So, it starts at 1/1/1900 and works from there.

You need to find a way to get a date into the formula.

DateAdd(Interval as string, Number as double, Date)
Example: DateAdd("m", 6, #07/14/2007#)


Randy