Help : merge functions to one

[eddii]

how can the i merge many expression to one

query field names
regdate and freq and form input value mfg
02/apr/2009 and 6 and form input is 2011

then it works and gets the result

1st expr
TOTMON: DateDiff("m",[REGDATE],[newdatefield])

2nd expr
NewDateField: [Monthofcal] & "/30/" & [mfg]

3rd expr
divf: Fix([totmon]/[freq])

4th expr
divf1: ([divf]+1)

5th expr
addd: ([divf]+1)*[freq]

6th expr
SYSNEXTDUEDATE: DateAdd("m",[addd],[regdate])

is there a way to make this into 2 or 3 expr

attached database for reference

query name : monthwise

 

[vbaInet]

I don't know what does functions are doing in your database and what calculations you may require elsewhere, but isn't this a matter of substituting the field names for the calculation?

 

[eddii]

Thanks vbaInet : Re: Help : merge functions to one

you are right the thing is

i have a field in table imtemaster: [regdate] and [freq] and

exp: [mfg] in form to input year 2011

if the [regdate] is 10/dec/2006 and freq is 24(months) this means every 24 months it should have a date and if this date is in the current year it should be calibrated.
10/dec/2006
10/dec/2008
10/dec/2010
10/dec/2012

and if i input the year in [2011] it should

do

1st expr
NewDateField: [Monthofcal] & "/30/" & [mfg]
it becomes : dec/30/2011

2nd expr
TOTMON: DateDiff("m",[REGDATE],[newdatefield])
here it takes : dec/30/2011 - dec/10/2006 = 60

3rd expr
divf: Fix([totmon]/[freq])
here it is : 60/24= 2.5 and ity fixes to 2.0

4th expr
addd: ([divf]+1)*[freq]
here it is : 2.0+1 X24 = 72

5th expr
SYSNEXTDUEDATE: DateAdd("m",[addd],[regdate])
here it is month add to : 72 + dec/10/2006 = dec/10/2012

these calculation is done if i input 2011 so that i can get this dec/10/2012

how to make in one expression by using brackets so this circular reference error will not come

 

[vbaInet]

What circular reference? Just don't name the alias as the same name as any of the fields used in the calculation.

On a side note, what will happen if [Monthofcal] is April here? -->

NewDateField: [Monthofcal] & "/30/" & [mfg]
it becomes : dec/30/2011

 

[eddii]

THANKS vbaInet Re: Help : merge functions to one

circular Reference i think it may come if i use one formula but not sure a guess

On a side note, what will happen if [Monthofcal] is April here? -->

it calculates for apr and monthofcal does not change in table its fixed


NewDateField: [Monthofcal] & "/30/" & [mfg]
it becomes : APR/30/2011


if you have time check out in database

i will reply to you on 1 dec 2011 as iam out of station and do not have a laptop to reply

 

[vbaInet]

Give it a try first and if you have a problem come back. A cricular reference will occur if the alias name is the same as the field name used in the calculation.

Sorry, I was meant to say February, not April.

 

[eddii]

Thanks vbaInet

I combined all of them to one thanks for motivation and helping

calibtaion due date will be based on the month procured that will be registered date so problem if any month

this is the formual

allformulajoined: DateAdd("m",(Fix(DateDiff("m",[REGDATE],([Monthofcal] & "/30/" & ([Forms]![REPORTFORM]![mfgdate1])))/[freq])+1)*[freq],[regdate])


Thanks vbaInet

 

[vbaInet]

I knew you could do it. Good work!