Results 1 to 6 of 6
  1. #1
    Reaper is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    57

    Query by a certain date


    I want to create a query that will give me all records from a certain date. The table being queried stores the date/time in general format, which includes both date and time.
    When I enter query criteria to filter the date I get no records. For example, if I put in 4/26/2012 as the criteria, I get no records. I'm assuming it's because I haven't matched
    the whole field.

    How can I enter criteria in the query to get the all records from the table on a given date?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    All records from a certain date

    SELECT * from yourtable where yourtable.yourDatefield > the date involved

    eg. suppose I want records where the LastChangedDate is greater than 30 September 2011

    SELECT * FROM MYTable where LastChangedDate > #9/30/2011#

    the # delimiters are required for Date datatype values

  3. #3
    Reaper is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    57
    Thanks for your reply.

    Not quite what I wanted. I want to select all records from an exact date, ie; 4/26/2012 from a field that stores the date in General Date format.
    This query will be used to populate a report containing the transactions from a particular date. The date will be user selected from a form and the
    date passed to the query.
    I think your example will give me everything after the date.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    Then use = sign.

    The field has time parts saved? Then extract the mm/dd/yyyy. Is report RecordSource a query? Calculate a field that extracts the mm/dd/yyyy

    LCD: CDate(Format(datefield,"m/d/yyyy"))

    Then:

    DoCmd.Open "formname", , , "LCD=#" & Me.datetextbox & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Reaper is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    57
    Got it, thanks.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A date query
    By Andrias14 in forum Queries
    Replies: 15
    Last Post: 11-23-2011, 09:50 AM
  2. query to update a date based on another date
    By tkendell in forum Access
    Replies: 3
    Last Post: 06-29-2011, 09:32 PM
  3. Date query
    By Philangr8 in forum Queries
    Replies: 7
    Last Post: 10-06-2009, 04:37 PM
  4. Date Query
    By drako28 in forum Queries
    Replies: 2
    Last Post: 09-10-2009, 11:11 AM
  5. Replies: 2
    Last Post: 07-31-2009, 06:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums