Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Apr 2007
    Posts
    16

    Question Unanswered: Date/Time Query in Access

    Hi people

    I have a field in a tables of computer faults called 'Date Reported'

    I want a query that looks at the date reported i.e. 25/04/2007 and will show all faults that have elapsed 3 days

    Is this possible?

    Thanks in advance

    Rich

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It certainly sounds possible, however you might want to elaborate on what you mean by "elapsed 3 days"...
    - Faults that lasted 3 days or more?
    - Faults that happened 3 days prior and/or post the date reported?
    - Faults that are still occuring on the date entered?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    16
    It would be faults that have been 3 days since they were reported ie. reported on 22/04/07 and it is now the 25th and the fault still hasn't been fixed

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So... unresolved faults that were reported 3 days ago...
    Just 3 days ago, more than 3 days, less than 3 days?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    16
    Sorry for conitunally being vague here!

    It is faults that are 3 days old AND over

    (the idea being this will flag to admin that these faults have still not being fixed)


  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha! I think we have the bare bones for building a query now.
    Let's see if we can do this together, shall we?
    Would you care to post your current SQL so we can see where you are at?
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Posts
    16
    I haven't actually been able to get anywhere due to my knowledge of SQL and queries being rather basic!

    I can give you the table fields if you like

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That'd be a good idea.
    Oh, and if you haven't realised yet, I'm not going to do this for you... Instead I am going to help you to do it!
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2007
    Posts
    16
    Table 2 FAULTS


    Fault ID (Primary Key) AutoNumber
    Fault Description Text
    Date Reported Date/Time (short)
    Date Resolved Date/Time (short)
    User ID (foreign key) Number
    Assigned Technician ID (foreign key)
    Status Text

    Thats fine, i'll give it my best shot

    Thanks again

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for posting that. Now I think we have all the information we need to get the result you're wanting
    So let's go through the logic of this again:
    We want to return all faults that are not resolved (how do we identify this?) and where the date reported is 3 or more days ago (based on todays date)
    EDIT: changed the wording of "all results" to "all faults"
    Last edited by gvee; 04-25-07 at 11:55.
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2007
    Posts
    16
    Unresolved faults will have their Status saying one of the following 'Unresolved' ,'in Progress' and maybe others as opposed to 'Fixed'
    Last edited by Rich2701; 04-25-07 at 12:07.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, sounds like we have enough to begin with.
    So now's your chance to shine!
    I want you to write me 2 queries:
    1) One that returns all faults with their status saying "Unresolved"
    2) One that returns all faults that were logged more than 3 days ago (so use the date 22/04/2007)
    Think you can manage that?
    Code:
    SELECT *
    FROM Faults
    WHERE ...........
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2007
    Posts
    16
    Select *
    From Faults
    WHERE Status= 'Unresolved'

    not sure on the date one, hence the question. would it be something like

    select *
    from faults
    where date reported = 'date >=+3'
    and status ="Unresolved"

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Almost!
    Your first query is bang on, but you tried 3 steps at once for your second!
    Let's redo the second one, but let's get all faults that were reported today. (hint: What's the Access function for returning todays date?)
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2007
    Posts
    16
    =DateAdd("y", +3, [Reported_Date])

    Would that almost do the trick ^^

    The statement to get todays date is: =Date() i think?

Posting Permissions

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