1. Registered User
Join Date
Apr 2004
Posts
173

Is it possible to use a SELECT statement as a criteria in a function expression. For instance is it possible to say DateDiff("d", SELECT(something here), NOW()).

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Sure, that would be a sub-select. The main constraint with subselects is they may NOT return more then 1 value. Other then that, just be sure to enclose the entire statement in ()'s:

SELECT Datediff("d", (SELECT something FROM something WHERE something), Now())

I am assuming you are pulling more then just this single figure? If not, you don't really need to do a subselect at all..

3. Registered User
Join Date
Apr 2004
Location
Kingsland, Georgia
Posts
231

## Re: date calculation

Originally posted by mmbosman
Is it possible to use a SELECT statement as a criteria in a function expression. For instance is it possible to say DateDiff("d", SELECT(something here), NOW()).
I'm not certain, but i don't think so. That's not really the way the SELECT statement is used. you'd probably have to do it programmatically in a previous statement, assigning the results to a recordset and then pull the value out by calling the recordset's .Fields() method. so you could

Code:
```Dim rs as Recordset
set rs = CurrentDb.OpenRecordset("SELECT fldDate from tblMembers WHERE fldID=335534")

DateDiff("d", rs.Fields("fldDate"), Now())```
hope that helps.

4. Registered User
Join Date
Apr 2004
Posts
173

## Date Calculation

Thanks for the help. Actually I'm using that subselect in the datediff function in correalation with the results I'm returning as a filter on those results. If it is possible is it necessary to use the format function to return similar date formats to get accurate results?

5. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102

## Re: Date Calculation

Originally posted by mmbosman
Thanks for the help. Actually I'm using that subselect in the datediff function in correalation with the results I'm returning as a filter on those results. If it is possible is it necessary to use the format function to return similar date formats to get accurate results?
I don't understand your question and application.

6. Registered User
Join Date
Apr 2004
Posts
173

## Date Calculation

Thanks for the help. Actually I'm using that subselect in the datediff function in correalation with the results I'm returning as a filter on those results. If it is possible is it necessary to use the format function to return similar date formats to get accurate results?

7. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Umm yeah... you said that...

8. Registered User
Join Date
Apr 2004
Posts
173

## query text post

SELECT tblUnits.unitNum, tblUnits.scheduleId, tblServiceScheduleLines.description, tblServiceScheduleLines.mileInt, tblServiceScheduleLines.daysInt,

(SELECT MAX(tblTrip.endOdo) as maxMileage FROM tblTrip WHERE tblTrip.unit = tblUnits.unitNum) AS Cmax,

(SELECT MAX(tblServiceLines.dateComplete) as lastDate FROM tblServiceLines WHERE tblServiceLines.unitNum = tblUnits.unitNum AND tblServiceLines.description = tblServiceScheduleLines.description) AS LDate,

(SELECT MAX(tblServiceLines.mileage) as lastMiles FROM tblServiceLines WHERE tblServiceLines.unitNum = tblUnits.unitNum AND tblServiceLines.description = tblServiceScheduleLines.description) AS LMiles

FROM tblServiceScheduleLines INNER JOIN tblUnits ON tblServiceScheduleLines.scheduleId = tblUnits.scheduleId

WHERE

(datediff("d",(SELECT MAX(tblServiceLines.dateComplete) FROM tblServiceLines WHERE tblServiceLines.unitNum = tblUnits.unitNum AND tblServiceLines.description = tblServiceScheduleLines.description),NOW()) > tblServiceScheduleLines.daysInt)

((SELECT MAX(tblTrip.endOdo) as maxMileage FROM tblTrip WHERE tblTrip.unit = tblUnits.unitNum)-(SELECT MAX(tblServiceLines.mileage) as lastMiles FROM tblServiceLines WHERE tblServiceLines.unitNum = tblUnits.unitNum AND tblServiceLines.description = tblServiceScheduleLines.description)) >=(tblServiceScheduleLines.mileInt-tblUnits.avgMiles);

The desired result of this query is a listing of all the service lines that are due based on the values of their day intervals and mileage intervals. The mileage portion works like a charm but not the datediff portion. Essentially what that section is attempting to due is compare the date of the last service to today's date and if that difference is greater than the value of that service days interval than display that row. Thanks for the help.

9. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102