calculated field

  • Thread starter Thread starter angie
  • Start date Start date
A

angie

i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.
 
i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.
Click to expand...


The easiest way would be to to use the field 'customer' and 'the field 'balance' in a query,
then use 'totals' (3rd menu), and replace 'group by' in the query by the word 'sum'

If you could mail me ( bbq0014 @ gmail.com ) an example of a table with say 20 records and a query you made I can show you what possibilities there are
Somethime you first make another table and later add this table to your query again.


This adds 3 months to to todays date
Expr1: DateAdd('m',3,(Date()))

This adds 3 months to a date fielld in Table 1, field 'date'
Expr1b: DateAdd('m',3,[Table1]![date])

This adds 3 months to todays date, if the present month is 12
Expr2: IIf(Month(Date())=12,DateAdd('m',3,(Date())),'')

The field (date()) can also be replaced by a date field, I am not sure to what date you want to add three months.
 
you can use the dateadd function to add 3 month to date:
ThreeMonthLater: DateAdd("m",3,[yourdatefield])

The other part I don't understand, do you want a running balance field?
 
Back
Top