Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Question Unanswered: selecting records by date

    Hello all,
    First time on this forum so here goes........ I am trying to write a database application to deal with flight schedules and bookings. My problem is this:- I need to create a number of user screens eg display all flights from a city between two dates. I can get the flights between two dates using "BETWEEN" but this only picks out the flights that start or stop in that period. Some flights start in JAN and will run through till NOV, others are all year round and some just summer. So I need to be able to do the following: Is the flight running this time of year? Then is the flight running on this particular day or range of days and put these in a list for the user to see.

    If someone can help I would be very greatful

    To clarify, i need to query the start date and the end date of a service and then select a period somewhere in the middle and show all the services that are running on that day
    Last edited by dapman2002; 08-30-03 at 21:51.
    the light is on, someone is home, but they dont know they are. HELLO!

  2. #2
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    If you have the two dates of the period you're requesting info on (let's call them StartUserDate and EndUserDate), you should go this way:

    If a service has a Startdate <= StartUserDate AND a EndDate >= EndUserDate, then the service is running in the user assigned period, otherwise it is not!

    Remember that in this way, if a service begins before the requested period, but closes before the end of the requested period, it will be considered NON running in the user requested period, since it is not running in the WHOLE user requested period.

    Bye!
    The only failure is not trying to do it.

  3. #3
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    thank you for taking the time to reply. how would i write this in sql?
    the light is on, someone is home, but they dont know they are. HELLO!

  4. #4
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Just as i said before:

    Sql= "SELECT * FROM Services WHERE Startdate <= " & StartUserDate & " AND EndDate >= " & EndUserDate

    You may have to understand by yourself what is the date format you should pass, #MMDDYYYY# is the more common one.

    Bye!
    The only failure is not trying to do it.

  5. #5
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    dates

    Thanks Ive got it working now. Well almost.
    Lets say i have lots of flights starting in march say 25th of march, When I query my table 25th is ok 26th is ok 27th is ok 28th is ok 29th is ok 30th is ok 31st is ok, but theb the 1st of April shows no results? Any Ideas...... Is it to do with the date format?
    the light is on, someone is home, but they dont know they are. HELLO!

  6. #6
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    It's probable...

    Can you please post the full query you're trying to execute, after all variables have been inserted?
    The only failure is not trying to do it.

  7. #7
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    There you go!
    sSql1 = "SELECT [tblSsimFile].dateStart, [tblSsimFile].dateEnd, [tblSsimFile].Days, [tblSsimFile].[Flt No]," & _
    "[tblSsimFile].UTCSTD, [tblSsimFile].POD, [tblSsimFile].UTCSTA, [tblSsimFile].POA, [tblSsimFile].Aircraft" & _
    "FROM [tblSsimFile] " & _
    "GROUP BY [tblSsimFile].dateStart, [tblSsimFile].dateEnd, [tblSsimFile].Days, [tblSsimFile].[Flt No]," & _
    "[tblSsimFile].UTCSTD, [tblSsimFile].POD, [tblSsimFile].UTCSTA, [tblSsimFile].POA, [tblSsimFile].Aircraft " & _
    "HAVING ((([tblSsimFile].dateStart)<=#" & txtFromDate & "#) AND (([tblSsimFile].dateEnd)>=#" & txtToDate & "#) " & _
    "AND (([tblSsimFile].POD)= '" & sFrom & "') AND ((tblSsimFile.POA)='" & sTo & "'));"

    Thank you so much.
    the light is on, someone is home, but they dont know they are. HELLO!

  8. #8
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    First of all, you've NOT posted the real query AFTER all the variables have been explicited, theyr content inserted into the query string.

    Then, i don't understand why you use a GROUP BY clause: why should you group two services in one?

    You should use, in my opinion, a ORDER BY clause, to sort them correctly, but there's no sense in grouping services, especially by date; also, it is surely redundant to group by POA and POD fields, since you have a HAVING clause that states that you want only records with a certain POA and POD.
    The only failure is not trying to do it.

Posting Permissions

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