First off, I would like to thank you so much for this service. I get tired of reading the access books, and just watching your tutorial videos have been so helpful to me.

This is a function that I would like to add to my access database that I'm not sure if I can/cannot do. If I can do it, would you kindly point me in the direction that I should go?

I want to be able to automatically set a record value in a field based on data that has just been entered in the same table. I understand that I could probably use the onupdate function, but I want the value to be pulled from another table with a minimum and maximum.

difficult to explain without saying EXACTLY how i want to use it:

I am building a database for a Pet Show and we classify the pets (in this case fish) according to their size. Each show has a different size classification, so I would like to set up a table that has minimum and maximum values per size. (e.g., size 1 is between 0 and 40 cm, size 2 is between 41 and 60 cm, size 3 is greater than 60 cm). From there, I would like the form to automatically input the size classification for the fish based on the measurement size that we input.

Is this something I can do? What function should i use? I started with a update query, but it grew tiresome to have to keep clicking messages. I embedded the size categories in the code, but i had to always go in there and change the values every time we would have to work with different show mechanics or with a different show.

Forgive the lengthy e-mail, and thank you again so much for the outstanding service you are providing! :-)

Warm Regards,
Mikki

---

My Response:

Mikki, if I understand this correctly, you want to input a size, and then look up what classification should be from a table? First set up your classification table:

ClassT: ClassID, MinSize, MaxSize
1, 0, 40
2, 41, 60
3, 60, 999

Now in the AfterUpdate event for the form field where you input the actual size value (let's call it Size) you would say:

Classification = DLOOKUP("ClassID","ClassT","MinSize<=" & Size & " AND MaxSize>=" & Size)

So if you type in a 5, you would get Class 1.
If you type in a 40, you would get Class 1.
If you type in a 75, you would get Class 3.

Easy enough?