How to populate a field with code.

[camidon]

I have a table that looks like this

ID Aging Category
1 current
2 1 week old
3 2 weeks old
ect all the way to 14

I have another table that has a date in it. Basically what I would like to do is find out how far back that date is and assign the corresponding ID to it based on the aging category. I don't really need it to be automatic (I can run it from a form).

My problem is figuring out how to populate that field. Would I use a query? Or do I need to create a module and do it with code?

Any help at all is very much appreciated!

Thanks,

Chris

 

[braindead2]

use this function
choose(datediff("ww",yourdatefield,date()),current,1,2,3,4,5,etc.. to 14)

 

[camidon]

Can I use this function in a query?

 

[braindead2]

yes the choose function will work in a query but that is not what I understood you were wanting to do. I thought you wanted to fill a field with the value, I assumed you would use it to find the value then do a dlookup from the table. I guess you could create a query use choose to fill the field and then on another query use that field as a join. My guess you will have data type errors doing it that way but what the heck give it a try.

 

[camidon]

I'm not familiar with dlookup. I will research that and see if I can find out some information on how it works.

Thanks for all your help.