How to calculate dates using info from columns 1

[Cowboysooner]

The formula in the Date Next Due column causes syntax errors and does not work in Access 97. It works just fine in Excel 97. Please help.

Required Last Date
Document Frequency Received
Audit Annually 12/31/01
Compilation Annually 12/31/01

Date
Next Due
IF([Document]="Audit",[Last Date Received]+515)
IF([Document]="Compilation",[Last Date Received]+485)

Audited statements are due 515 days from last reciept.

Compilations are due 485 days from last reciept.

 

[smedvid]

U need to use the dateadd() function. Pretty easy to use, check help on MS Access... htwh Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[Cowboysooner]

I agree, DateAdd()is easy to use. I am able to add a specific number of months etc. to every date in a column containing dates.

What I can't figure out is how to add different numbers of days to the date in the Last Date Received column based on the type of financial statement listed in the Document column.

The folowing formula works in Excel 97 but not in Access 97. I tried to import this table to Access 97 but the formula did not import properly.

A B C
Required Last Date Date next
1 Document Frequency Received Due
2 Audit Annually 12/31/01 *
3 Compilation Annually 12/31/01

* IF(A2="Audit",C2+515,IF(A2="Compilation",A2+485))

 

[smedvid]

I am 99.9% sure there is a "d" parameter to DateAdd().

Assuming you have a database table with the following structure:

Document LastDate NextDate Freq.
Audit 12/31/01 Annual
Compilation 12/31/01 Annual



=IIF([Document]= "Audit",dateadd("d",[LastDate],515),dateadd("d",[LastDate],485)

hthwh Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[Cowboysooner]

Your suggestion worked.

How can I use a wildcard in a formula to account for spelling differences? For example: Audit, Audited, or a misspelled variation. I tried to use "Aud*" but this didn't work.

Thanks for your help!