Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query beyond me (A2000)

    SELECT tblCourtesyCar.Unit, tblPeriod.FromDate, DMin("FromDate","tblPeriod","UnitID=" & [UnitID] & " And FromDate>#" & Format([ThruDate],"mm/dd/yy") & "#") AS FromDate
    FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID
    HAVING (((tblPeriod.FromDate) Is Null Or (tblPeriod.FromDate)<=[forms]![frmcourtesycarplanner]![txtDate]));

    This query is supposed to tell me which cars are "In Now" by pulling all vehicle registrations([Unit]) from tblCourtesyCar and show their next book out date.

    If I strip down the query, I'm basically looking for a vehicle which has no record:

    SELECT tblCourtesyCar.Unit, tblPeriod.FromDate
    FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID
    GROUP BY tblCourtesyCar.Unit, tblPeriod.FromDate
    HAVING (((tblPeriod.FromDate) Is Null Or (tblPeriod.FromDate)<=[forms]![frmcourtesycarplanner]![txtDate]));

    I presumed by joining the following, this would give the info I needed.
    It Doesn't. Problems with Joins and all sorts. I don't understand why I can't make it work.

    FromDate: DMin("FromDate","tblPeriod","UnitID=" & [UnitID] & " And FromDate>#" & Format([ThruDate],"mmm/dd/yy") & "#")

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Dave ~ because i am an amateur SQL-er, i usually (i.e., always) let Access write my sql. I would therefore approach your problem as a two-parter:
    1. design a query Q1 to select all vehicles with a next book out date (if that is what you want)
    2. design a query Q2 using the Find Unmatched Query Wizard (using Q1 as one of the participants).

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Thanks for the reply Pete.
    I've tried the way you said but the UnitId's conflict.

    Hans did an SQL last week that solved another problem, I tried inserting his code into the query and that conflicts too.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query beyond me (A2000)

    Isn't it just a matter of determining if "Date" (Now date) is between FromDate and ThruDate for any of the tblPeriod records?
    What exactly is the tblPeriod table, I assumed it was a booking table.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Yes I see what you mean.

    Date() is thru From and Thru dates, but.
    I am looking for Null Records and then records that are Not Null.
    My first query checks tblCourtesyCar and gets all the Units (Registration) so I have a complete list of vehicles data or not.
    The next step is to find the next time it is booked out, (Their may be dates after also).

    I need only to return the next

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query beyond me (A2000)

    Dave,

    Do you want to return a list of
    (a) all cars, or
    ([img]/forums/images/smilies/cool.gif[/img] only those cars that are "free" on the specified date (i.e. the specified date falls within none of the periods for that car)?

    Am I correct in assuming that you want to see the first "FromDate" after the specified date (if any) for those cars?

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Hans
    txtDate is always set to Date() whenever the form loads, so as to keep the current date.
    I need to return all cars that are not booked, (Do "not" have a Booking date) specific to the date value in txtDate.

    SELECT tblCourtesyCar.Unit, tblPeriod.FromDate AS [Next Booking]
    FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID
    GROUP BY tblCourtesyCar.Unit, tblPeriod.FromDate
    HAVING (((tblPeriod.FromDate) Is Null Or (tblPeriod.FromDate)<=[forms]![frmcourtesycarplanner]![txtDate]));

    You will see from the SQL here, this does just that. returning the registration in column 1
    Column 2 should hold the "next" fromdate.

    I could do this another and easier way but It would return multiple registrations [Unit].

    So answering your question:

    ([img]/forums/images/smilies/cool.gif[/img] only those cars that are "free" on the specified date

    you want to see the first "FromDate" after the specified date (if any) for those cars?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query beyond me (A2000)

    Dave,

    If txtDate is always set to Date() and can't be changed by the user, you might as well use Date() in the query.

    I think it's easiest to do this in two queries:

    (1) Create a query based on tblCourtesyCar and tblPeriod that selects the cars (UnitID's) booked at the current date:

    SELECT tblCourtesyCar.UnitID
    FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitID = tblPeriod.UnitID
    WHERE tblPeriod.FromDate<=Date() AND tblPeriod.ThruDate>=Date();

    Save this query as qryBookedCars.

    (2) Create a query based on tblCourtesyCar and qryBookedCars that selects the non-booked cars and computes the next FromDate:

    SELECT tblCourtesyCar.Unit,
    DMin("FromDate","tblPeriod","UnitID=" & [tblCourtesyCar].[UnitID] & " And FromDate>Date()") AS [Next Booking]
    FROM tblCourtesyCar LEFT JOIN qryBookedCars ON tblCourtesyCar.UnitID = qryBookedCars.UnitID
    WHERE qryBookedCars.UnitID Is Null;

    <img src=/w3timages/blueline.gif width=33% height=2>

    It *is* possible to put this in one query, by using a nested query as criteria, but this is much harder to debug and maintain:

    SELECT tblCourtesyCar.Unit,
    DMin("FromDate","tblPeriod","UnitID=" & [tblCourtesyCar].[UnitID] & " And FromDate>Date()") AS [Next Booking]
    FROM tblCourtesyCar
    WHERE tblCourtesyCar.UnitId Not In
    (SELECT tblCourtesyCar.UnitId FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID
    WHERE tblPeriod.FromDate<=Date() AND tblPeriod.ThruDate>=Date());

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Hans
    I'll use the nested query for now. If I have any hick-ups, I can always revert to the other options.
    Thanks again.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Hans

    Just a little thing I am trying to do with these queries.
    I'm trying to change the Output format to dd-mmm-yy.

    I've tried changing the individual properties in query design and also the individula format of the text on the forms, it won't display how I want.

    It still displays as 20/12/02 instead of 20-Dec-02

    Does this need to be formatted at SQL level.

    SELECT tblCourtesyCar.Unit, DMin("FromDate","tblPeriod","UnitID=" & [tblCourtesyCar].[UnitID] & " And FromDate>Date()") AS [Next Booking]
    FROM tblCourtesyCar
    WHERE (((tblCourtesyCar.UnitId) Not In (SELECT tblCourtesyCar.UnitId FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID
    WHERE tblPeriod.FromDate<=Date() AND tblPeriod.ThruDate>=Date())))
    ORDER BY tblCourtesyCar.Unit;


    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, DMin("FromDate","qrySelectCourtesy","UnitID=" & [UnitID] & " And FromDate>#" & Format([ThruDate],"mm/dd/yy") & "#") AS FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)=[forms]![frmcourtesycarplanner]![txtdate]));

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query beyond me (A2000)

    Dave,

    I'm not sure I understand this behaviour completely myself. Functions like DMin return a variant value, not a date, and apparently this makes the result ignore the Format property. You can set the desired format in the query:

    SELECT tblCourtesyCar.Unit, Format(DMin("FromDate","tblPeriod","UnitID=" & [tblCourtesyCar].[UnitID] & " And FromDate>Date()"),"dd/mmm/yy") AS [Next Booking]
    FROM tblCourtesyCar
    WHERE (((tblCourtesyCar.UnitId) Not In (SELECT tblCourtesyCar.UnitId FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID WHERE tblPeriod.FromDate<=Date() AND tblPeriod.ThruDate>=Date())))
    ORDER BY tblCourtesyCar.Unit;

    This will turn Next Booking into a string, i.e. you can't use it in calculations directly.

    Note: if this still displays with / on your PC, you can force hyphens as separators by using "dd-mmm-yy" as format string.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query beyond me (A2000)

    Dave,

    Here is an alternative form for the query that avoids the use of DMin; instead, it uses another nested query. In this query, you *can* format Booked Date by setting a Format in the Properties window (in the query or on a form).

    SELECT tblCourtesyCar.Unit, (Select Min(FromDate) From tblPeriod Where UnitID=tblCourtesyCar.UnitID And FromDate>Date()) AS [Next Booking]
    FROM tblCourtesyCar
    WHERE (((tblCourtesyCar.UnitId) Not In (SELECT tblCourtesyCar.UnitId FROM tblCourtesyCar LEFT JOIN tblPeriod ON tblCourtesyCar.UnitId = tblPeriod.UnitID WHERE tblPeriod.FromDate<=Date() AND tblPeriod.ThruDate>=Date())))
    ORDER BY tblCourtesyCar.Unit;

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Hans.

    I've got that but still struggling with this one :

    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, DMin("FromDate","qrySelectCourtesy","UnitID=" & [UnitID] & " And FromDate>#" & Format([ThruDate],"mm/dd/yy") & "#") AS FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)=[forms]![frmcourtesycarplanner]![txtdate]));

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query beyond me (A2000)

    Try this:

    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, (Select Min(A.FromDate) From qrySelectCourtesy As A Where A.UnitID=qrySelectCourtesy.UnitID And A.FromDate>qrySelectCourtesy.ThruDate) AS FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)=[Forms]![frmCourtesyCarPlanner]![txtDate]));

    Note: if txtDate is always the current date, you can replace [Forms]![frmCourtesyCarPlanner]![txtDate] by Date()

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query beyond me (A2000)

    Perfect
    Thanks

Posting Permissions

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