Control Source Confusion

[Audrey Abbey]

I feel like the answer will be a simple one and I will go "doh!" - but here it goes.

I have a form that will be used to input variables (on which calculations will be made in another form or report). These variables are used to calculate total capacity for a gas plant. As such, they have an effective date.

I am trying to create a report or form that can be pulled up to display the variables and calculations on any given day.

Example:
I have sets of variables that are effective on 9/15; 9/25; and 10/15. I want to pull up the variables that were in effect on 10/1 - and then do my calculations and return the plant capacity on 10/1. On 10/1, I would want to pull up the variables for 9/25.

I have the calculations figured out. But I cannot seem to figure out how to populate my controls with the specific set of variables based on the effective date. Most of the time, they will be pulling the information for the current day, but I cannot rule out the possibility that someone would want historical data.

Is this even possible? Is there a better way to do this that I am not seeing?

[jimatqsi]

Audrey, I don't understand the problem. It seems you are saying that you are successfully grabbing the current date, apparently from Date(), and with that you go to some table where you find the effective parameters for date() and all is well. Or is that you are simply pulling the CURRENTLY effective parameters?

If instead of using Date(), you let the user enter some date, where does that create a problem? Is it because you don't have a history of the parameters and the date that they were effective? If you don't have a history of the parameters and their dates you will need to create one. Then the solution should be simple.

Is that helpful, or am I missing something?

Jim

[Audrey Abbey]

follow up

Sorry, this is hard to explain.

Currently, I can only get it to return the variables associated with the first record in the table, regardless of how much other data is entered. I have some dummy info in place to help me work out the details, by the way, this is a brand new process being implemented.

So my question is twofold.

1) I can't figure out how to select a specific record from the table I am pulling from in the first place.

2) I can't work out the logic on the effective date, especially once I have more than two dates to work with. A simple "greater than X, but less than Y" will only work if I have two dates.

If I have three dates:

9/1
9/15
10/25

And I am looking for variables for 9/16, I need some way to reject the 9/1 record and choose 9/15 instead.

I am self taught (and work mostly with VBA, rather than SQL or macros), just fyi. I can do cascading combo boxes but I can't figure this out, lol.

[jimatqsi]

Do you know how to make a column in a query to find the Max value of a data item? You could make a query to return the greatest date that is less than or equal to the selected date. It would look something like this:
Select Max([effectivedate]) as Eff_Date where [effectivedate] <= [Date User Selected] AND (whatever other criteria might be needed such as plant number=?)

Eff_Date will be the date you are looking for.

Jim