Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date/Time Field Problem (Access 2000)

    I have a table that has a Date/Time Field and I have a query that asks for the begining and ending date but because there is a time in the field by default the querie returns no results unles I put the date in like this:

    1/9/2004 12:00 AM

    What I would like to do is retrieve all records that are for a specific date created after 12:00 AM

    I can not seem to find a way of doing this. Can anyone help me.

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

    Re: Date/Time Field Problem (Access 2000)

    If you put in a date without specifying the time, Access should automatically assume midnight as the time. Could you post the SQL for your query?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date/Time Field Problem (Access 2000)

    One of the problems of dealing with a data/time field is using selection criteria on this field. For example, when you want all records with a date of 4/1/04, you are in effect telling the system to check for all dates that are precisely equal to 4/1/04 12:00 am! If your data/time field just contains a date (but no time portion), then there is no problem. When it does contain a time component, however, you will never get a match (or a least hardly ever).

    So, if your field is TransDate (for example), your SQL needs to include this in the WHERE clause:
    TransDate >= #1/9/04# AND TransDate< #1/10/04#

    This essentially says "give me all records with a TransDate greater than or equal to 1/9/04 12:00am and less than 1/10/04 12:00am"!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    SELECT [Main Data].TSR_ID, [Main Data].First_Name, [Main Data].Last_Name, [Main Data].SpouseName, [Main Data].Phone, [Main Data].Bus_Phone, [Main Data].ArrivlDate, [Main Data].Appt_Time, [Main Data].Res_Date2, [Main Data].Res_Date_Time2, [Main Data].Res_Date3, [Main Data].Res_Date_Time3, [Main Data].Resort_Code, [Main Data].CXL, [Main Data].Showed
    FROM [Main Data]
    WHERE ((([Main Data].ArrivlDate)=[Enter Appointment Date]) AND (([Main Data].Resort_Code)="AP") AND (([Main Data].CXL)=No) AND (([Main Data].Showed)=No))
    ORDER BY [Main Data].ArrivlDate, [Main Data].Appt_Time;


    The problem is the ArrivalDate has not just a date but it looks like this:

    1/9/2004 1:35:17 PM

    And I need to pull all the appointments for that day but only want the data entry person to have to enter the date.

    Hope this helps.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date/Time Field Problem (Access 2000)

    old: [Main Data].ArrivlDate=[Enter Appointment Date]

    Simply change the above part of your SQL to this:
    [MainData].ArrivlDate>=cdate([Enter Appointment Date]) AND [MainData].ArrivlDate<cdate([Enter Appointment Date])+1
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    Wouldn't this then require 2 dates be entered?

    I am trying to make this so only one date needs to be entered to get the result.

    Thanks

  7. #7
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    Also inserted that statement and not only does it ask for 2 dates but even if I put the 2 dates in it does not return the record

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date/Time Field Problem (Access 2000)

    If you type it exactly the same in both cases, and declare it as a parameter (may not be necessary), then it should only give you one prompt. The basic issue here is that your dates are being stored as a date and a time, presumably using the Now() function, so if you want everything you need to specify Between myDate And myDate+1 or do it the way Mark suggested. You may have a typo or something that's causing it not to work, but I've never seen if fail when correctly implemented.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    The information contained in the ArrivlDate is placed there on direct import and I have no way of stripping the time from it.

    In an Access query couldn't I have a critereia like [Enter Appointment Date] And >#12:00 AM# which I have tried and it doesn't seem to return any result either.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date/Time Field Problem (Access 2000)

    If it's an import file, is it really a date/time file? Look at the design of the table and check that. If it is, date time fields are structured so that the integer portion is the number of days since 12/31/1899, and the decimal portion is the portion of a day, representing time. That is why you should be able to add 1 to the date you want to select and get all the records for that day. You can test that by modifying the SQL string to not use prompts, and plug in constants for a day you know there are a certain number of records.

    Your suggested criteria doesn't work because the time is not stored as a separate value. You could get cute and take the integer portion using a function, convert it back to a date, and use that as the criteria, but it's considerably more involved than the suggested approach. So start by trying something like Between #4/10/2004# And #4/11/2001# - unless you have records that have a date of exactly midnight, you will get only records for 4/10. If you do have records with exactly midnight, then you need to use >= and < as your criteria.
    Wendell

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date/Time Field Problem (Access 2000)

    As Wendell suggested, you must have a typo. You must have [Enter Arrival Date] EXACTLY the same in both places; that is, the exact same characters between the brackets. If they match, Access will only ask once.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    Ok the example returns the right record.

    Between #4/10/2004# And #4/11/2001# now how could I just have to enter the one date. I know this is repititious but I keep getting a syntax error each time I try.

  13. #13
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time Field Problem (Access 2000)

    OK I finally got it to work............ Thank you all for your help and time.

Posting Permissions

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