Auto Aditional Row in Query

[jiajian]

Hi

If I have a table with field [Name],[Date Purchase],[Payment Term] where
[Payment Term] could have 3 code e.g. A=annual, Q = quarterly & S=Semi.

E.g. [Purchase Date] is 20th May 1997 with [Payment Term] =Q, the person need to pay on 20th Aug 1997, 20th Nov 1997 & 20Feb 1998.

Question:

How can I auto generate a list when [payment Term] =Q, there will be total 4 rows with different date?
It apply to the [payment Term] =S too where 2 rows is required to be shown in the new query.
For [payment Term]=A, no change appeared in this query.

Thanks.

Regards

jiajian

 

[Tiro Cupidus]

You can use the IIf and DateAdd functions in your query to accomplish this. I've included some sample text from a field you would want to create in your query:

1: IIf([Payment Term]="q",DateAdd("q",1,[Purchase Date]))

If the Payment Term = q Then it will take the purchase date and add 3 months to it. Of course, you'll need a nested IIf to include possibilities of "a" and "s" as well. If you look up DateAdd in the help file, there's much more information to view