Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: date calculation

    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. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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. #3
    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. #4
    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. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    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.

    Could you please post your query and desired results?

  6. #6
    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. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Umm yeah... you said that...

  8. #8
    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. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, the first thing I'm thinking is to move the datediff function inside the subselect. I'll need a minute to prove whether this is worth the time or not...

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •