Need support on MS Access quering?

Question

What I would like to do is, I have a table which has 'warranty' field, that show as '1year, 2year,3months etc' But I would like it to convert into days. Eg, 1year = '365' or '60' for 2 months it to appear on a next field. Its urgent, do help!!!

I have very many records in that, the warranty values are very many, '1year, 2year' So its not practical me to add all the functions against each record, as u have described, BigRez. Is there no way where it automatically converts 1year value to 365?

Answer 1

if you don't want to use BigRez method , try this :

1. make a 'days' field, type integer.

2. separate number and 'year' or 'month' :
- update yourTable set Days=ConvertToInteger(trim(both 'month' from warranty) * 30 where position('month' in warranty)>0 ;
- update yourTable set Days=ConvertToInteger(trim(both 'year' from warranty) * 365 where position('year' in warranty)>0 ;

hope this help.

notes : open your MsAccess help/manual for Trim, Position and ConverToInteger similarity.
if you have vary case in warranty field (eg. 'Year' and 'year') maybe you want to use 'upper' or 'lower' query.
http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html

Answer 2

Probably the brute force method is you easiest way...


update myTable set warranty=365 where warranty='1 YEAR';
update myTable set Warranty=60 where warranty='2 months';

Do this a few times for each of the values in your table - shouldn't be too many combinations.

Answer 3

maybe a database admin

Answer 4

No, you need a "database."