Hello!! I have a database for a property maintenance company... There's a combo box called "Freq" (for Frequencies - of which there are 13 different ones), a field called "Total Fee" (how much we're billing the client) and another called "Monthly Total Avg" (it's a hidden field, important for other pages in the database).

This is displayed on a datasheet.

For the Monthly Total Avg, if Freq is 1X/YR then I need Total Fee to be divided by 12 to get the monthly average.

I tried doing this in a module, but with limited experience I couldn't get it working properly. I can't do it with an iif statement because it's too long (there are 1X/YR, 6X/YR, 1X/MO, etc, etc)...

Also, I need the data for some queries (not sure if that makes a difference or not - thankfully I know how to make queries). I tried using an expression to give [Monthly Total Avg] a value after Frequency is updated, but it updated ALL of the records on the sheet, not just the one I changed the frequency for.

Any help would be greatly appreciated!! Thank you!!

Again some code snippets, or the whole mdb uploaded would be great.

jamie,

Not much info to go on here.

Assume that you have a table for the frequencies:

tblFrequencies
==============
FreqID - AutoNumber
FreqType - Text (Annual, Qtrly, Monthly ...)
FreqDivisor - Number (12, 4, 1)


Select A.CompanyName As CompanyName,
A.TotalFee As TotalFee,
A.TotalFee / B.FreqDivisor As MonthlyAvg
From YourTable As A Inner Join tblFrequencies As B On
A.FreqType = B.FreqType
Order By A.CompanyName


Then you alleviate the requirement for code in the first place.

If you want to get your code working:

1) Make sure the code is in your modules collection on the database Tab
2) Make sure that is a Public Function
3) Make sure that the function and the module don't have the SAME name
4) In your query add a new column --> NewClm: YourFunction([TotalFee], [FreqType])

You need to add the field names in the call so that it will fire for EVERY row.

hth,
Wayne

Hey!! Thank you very much for taking the time to respond. To be honest with you, I didn't exactly understand your code (haha - not so smart), HOWEVER, in the bottom section, you said to ensure the function and module have different names... I switched the function name and my code started working!!

Thank you so much!!

Jamie,

The first part was just to show you how to do it without code.

With code there are a few common problems ... glad that yours was one of them.

Glad you have it working.

Wayne