Selecting all date fields between two dates

[Swatkins]

I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.

I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.

So, I have three tables.
Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost

Table: t_AllMonths
Fields: MonthStartDate

Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate

Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.

My sql looks like this:

SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);

I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."

Can anybody see where I'm going wrong with this sql?

 

[Swatkins]

Just a follow up note:

I also tried adding:

GROUP BY t_Assets.AssetID, t_AllMonths.MonthStartDate;

to the end of the query. When I do that, the query treats [MinOfStartDate] and [MaxOfEndDate] as parameters to be entered by the user, rather than using the values generated by the Min and Max functions in the MinOfStartDate and MaxOfEndDate fields...

 

[plog]

Lots to cover here, and at the end of it, I'm not entirely sure you're query will do what you want.

Min and Max are aggregate functions. When you use them you have an aggregate query. All the fields in the SELECT clause of an aggregate query must either use an aggregate function, or appear in the GROUP BY clause of the query, and they can't do both. So when you added AssetID and MonthStartDate to the GROUP BY clause you made it valid SQL.

MinOfStartDate and MaxOfEndDate aren't fields in your underlying data sources, so you can't use them elsewhere in the query. The WHERE clause has no idea what you are referencing with those names so it prompts the user to enter them. To get around this, you could remove the WHERE clause and use that query as the basis of another query. Doing so will make MinOfStartDate and MaxOfEndDate fields in its underlying datasource and available to use in a WHERE clause...

But like I said, even doing all that, I'm not sure your query will provide you with what you want. If it doesn't, post back here with sample data from all the tables, and then what data the query should return based on that sample data. Be sure to include field and table names.

 

[Swatkins]

Okay, that's what i ended up doing previously when I used the Access query builder to do this. I was trying to do this in vba with recordsets and was hoping for something more elegant in the sql.

So, say I'm building sql statements to use with record sets on vba and I figure on saving the min and max dates in variables to use in a similar query:
1) What datatype do I use to dim the date variables?
2) What's the appropriate syntax to use these variables to build a sql string in an open recordset command?
3) Mostly unrelated, but while I'm on the subject of record sets, something I've been wondering about recently is if I should be closing my record sets before leaving my sub, if so how, why or why not?

Thanks!

 

[plog]

Those are all VB questions that I'm not confident in answering. You should start a new thread in that section of forum for those questions.

However, I think this can be solved with just a query (with maybe some sub-queries). So, if you want to post sample starting data and what the resulting data should be based on that sample data, I can help you in this post.

 

[Swatkins]

Thanks. I actually got this part working, previously, using layers of queries on queries. I was trying to see if I could do better than Access' query builder using SQL inside a VBA module to get something less cluttered and more efficient. I'll post something in the VBA section asking a little more about this.