Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Retrieving Dates From Excel to Access?

    Currently new to the access software.

    I have managed to set up a query linking an excel spreadsheet to retrieve my information. In the excel spreadsheet i have a list of work areas that all have start dates and completion dates. (looks a bit like below)

    Area 1 13/07/11 16/07/11
    Area 1 14/07/11 19/07/11
    Area 1 15/07/11 18/07/11

    The text highlighted in red is the information that im trying to group.

    So the query when ran onto the data sheet should list like below

    Area 1 13/07/11 19/07/11

    However when i run the query i can either list the start date by first or last and it just picks out the first section or last section of that data within the spreadsheet.

    The information im trying to get is the earlist start date, and the latest finish date. Im not clued up with all the coding. Any help would be much appreciated as iv had this problem for a long time now. Thanks.

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Have you tried min and max?
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Aug 2011
    Posts
    5
    Yes tried the min & max function but no luck with that either. The database doesnt seem to recognise the values i need from the list. Is an expression something i will have to use?

  4. #4
    Join Date
    Aug 2011
    Posts
    5
    When using the min & max, the max works fine however the min function brings out 00:00:00 in areas where a start date hasnt being inputted into the excel spreadsheet. This would work fine however i also need to include the areas where there isnt a start date inputted as the list needs to be kept to a full list, tried putting in >0 but that takes out the dates that havnt being inputted that come under 00:00:00. Anyone any idea how i can show the earliest start date that doesnt not = 0:00:00 but also show the 0's?

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Dump question are the date values stored as date value
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Aug 2011
    Posts
    5
    Yes they are have no idea how this can be done.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Min and Max would work fine if it weren't for the cells that you have no data in for the Min column... right?

    You might have to do two things. One, get a query to show you the SECOND smallest value in that data for the start date (which should be the min). And then use a second query to do a max and a lookup in the first query for the min for each area.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Aug 2011
    Posts
    5
    Thanks, thats not a bad suggestion to set up a seperate query for the start date, like you say its not a problem with the finish date because its the maximum that i need. When it comes to finding the start date thats an issue due to there being no information in putted into some of the cells (because some tasks havnt started).

    Say i set up another query for the start date? would i have to use an expression in order to show the second minimum date? how would this be possible. 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
  •