Go to in Query

[goslincm]

Good morning. I'm not certain I'm in the right forum, but I believe I am.

I have a table that contains an autoID for the primary key. In that table I'm collecting [audit_received_date],[fiscal_year], [county_code], [population].

I've then created a form, where I need to calculate the [audit_due_date] value. The [population] will determine WHEN the [audit_due_date] is.

IF the [population] is <4000 an audit must be filed within 24 months of the last [audit_received_date] IF >4000 then the [audit_due_date] is every 12 months.

I do not know how to put this together for those under 4000.

 

[THWatson]

goslincm
Assuming that all information is available on the form...

If Me.population < 4000 Then
Me.[audit_due_date] = DateAdd("m", 24, [audit_received_date])
Else
Me.[audit_due_date] = DateAdd("m", 12, [audit_received_date])
End If

Tom

 

[dhookom]

I would do this with a public function so that I don't have code or expressions spread out around my application that relies on business rules. I would expect the population threshhold would change. Placing 4000 in an expression or code in a report or form might lead to hard to maintain applications.

Consider a function in a module named basBusinessCalcs

Public Function GetDueMths(plngPopulation as Long) as integer
    If plngPopulation < 4000 Then
       GetDueMths = 24
     Else
       GetDueMths = 12
    End If
End Function

You can then use a control source in a form or report like:
=DateAdd("m", GetDueMths(Population), audit_received_date)
When the 4000 changes to 3000, you make a change in only one place in your application. A better practice would be to place the 4000 in a table so you would only edit data and not code or expression.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

THWatson, thanks for the code, however given that the table in a linked table I cannot add the field "audit received table to it, instead I need the calculated field. I don't know where I'm putting the code you've indicated. If its not going into a control source for a field, then I have to put it in my query yes? But wouldn't I have to use the IIF function?

Dhookom, I understand why your suggesting what you are, that makes sense to me, however I am wayyy inexperienced with private functions or public ones. Obviously a lot of this is over my head.

 

[lespaul]

You're contradicting yourself:

In that table I'm collecting [audit_received_date]

and now:

however given that the table in a linked table I cannot add the field "audit received table to it

Do you or do you not have a field named [audit_received_date]?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Sorry Leslie (and all others), in my last post I mispoke.

But if you look at my original post, what I have is a table (yes linked), that has the field [audit_received_date] being entered. In my form, I have to create a field to perform the calculate to indicate [audit_due_date].

We currently know when audits are being received, but we don't know when they were due, and that is determined in part by the field [population].

 

[THWatson]

goslincm
DHookom has offered the best way forward for you in the long run and has indicated how it will work. I get that you don't understand this completely as I have been there myself, but try it and you will like it.

1. First, copy the code that Duane has shown.
2. Go to Modules, create a new module, and paste in the code
3. Save the Module as basBusinessCalcs (the name Duane has suggested)
4. On your form, make the control source for the calculated field =DateAdd("m", GetDueMths(Population), audit_received_date)

Tom

 

[goslincm]

THWatson and Dhookom, I can't thank you enough for the help with this.