need help on Formula

[dinadvani via AccessMonster.com]

Hi,

I am working on a database and now i m stuck somewhere so need your help.

I had an excel sheet which i exported to access, now in excel this was 1
column which had formulas to calculate the status of transaction. But i m not
sure how to and where to transfer that in access so that each record has the
status.

I tried entering the query in queries but no luck.

This is the excel formula.=IF(K602<TODAY(),"Expired",IF(K602<=TODAY()+60,
"Renew","Active"))

K602 = cloumn name is Exp Date (go site)

Please help

Thanks
DA

 

[Guest]

In a query
Something:
IIf([FieldName]<Date(),"Expired",IIf(DateDiff("d",[FieldName],Date()>60),"Renew","Active"))

On a form (row source)
=
IIf([FieldName]<Date(),"Expired",IIf(DateDiff("d",[FieldName],Date()>60),"Renew","Active"))


Note "FieldName" is the name of the field containing the Date.
In the calucation Date() will give you todays date - so don't call the field
"Date"

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[Keith Wilby]

Hi,

I am working on a database and now i m stuck somewhere so need your help.

I had an excel sheet which i exported to access, now in excel this was 1
column which had formulas to calculate the status of transaction. But i m
not
sure how to and where to transfer that in access so that each record has
the
status.

I tried entering the query in queries but no luck.

This is the excel formula.=IF(K602<TODAY(),"Expired",IF(K602<=TODAY()+60,
"Renew","Active"))

K602 = cloumn name is Exp Date (go site)

You need a calculated field in a query. Try (untested):

IIf([YourFieldName]<Date(),"Expired",IIf([YourFieldName]<=(Date()+60),"Renew","Active"))

Note "IIf" and not "If".

Keith.
www.keithwilby.com

 

[dinadvani via AccessMonster.com]

Thanks a ton......

Would request you to please help me in future as well. As I still have some
issues.

Thanks,
Da

Hi,

[quoted text clipped - 13 lines]

K602 = cloumn name is Exp Date (go site)

You need a calculated field in a query. Try (untested):

IIf([YourFieldName]<Date(),"Expired",IIf([YourFieldName]<=(Date()+60),"Renew","Active"))

Note "IIf" and not "If".

Keith.
www.keithwilby.com