Results 1 to 3 of 3
  1. #1
    Steve Barnes is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    2

    Date range query from form

    I am using a form to enter a date range which is being used by several queries in a macro.The query has - WHERE (((dbo_LDEdala_ApplicationConsent.DecisionIssuedDa te) Between [forms]![Decisions - Enter_Dates].[BeginningDate] And [forms]![Decisions - Enter_Dates].[EndingDate])



    The problem is that if I enter 25/7/2010 in the BeginningDate and 28/7/2010 in the EndingDate, I only get records for th 25/7, 26/7 and 27/7, not for the 28/7. My work around is to enter 28/7/2010 11:59:59PM into the EndingDate - then I get the reords for the 28/7 also. I don't want the users to have to enter "11:59:59PM" - what is the answer please?

    Thanks
    Steve

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    It looks like your date values might be full Date/Time (it includes the hour/min/sec as well as the date)instead of just Short Date. If that's the case, then the following should do what you want:

    Code:
    WHERE (((dbo_LDEdala_ApplicationConsent.DecisionIssuedDate) Between DateValue([forms]![Decisions - Enter_Dates].[BeginningDate]) And DateValue([forms]![Decisions - Enter_Dates].[EndingDate]))
    This will cut off the time portion and allow you to do a check involving only the Date of those Fields.

  3. #3
    Steve Barnes is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    2
    Thanks for the help, the full code actually ended up as - SELECT dbo_LDEdala_ApplicationConsent.ApplicationId, dbo_LDEdala_ApplicationDetail.DevelopmentNumber, dbo_LDEdala_ApplicationConsent.DecisionDate, DateValue([DecisionIssuedDate]) AS Expr1, dbo_LDEdala_ApplicationConsent.DecisionResultType
    FROM dbo_LDEdala_ApplicationConsent LEFT JOIN dbo_LDEdala_ApplicationDetail ON dbo_LDEdala_ApplicationConsent.ApplicationId = dbo_LDEdala_ApplicationDetail.ApplicationId
    WHERE (((DateValue([DecisionIssuedDate])) Between [forms]![Decisions - Enter_Dates].[BeginningDate] And [forms]![Decisions - Enter_Dates].[EndingDate]) AND etc.......

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

Similar Threads

  1. Query for date range from two column?
    By sunny in forum Queries
    Replies: 2
    Last Post: 07-28-2010, 07:12 AM
  2. Define a date range for a report - Help
    By Optimus_1 in forum Access
    Replies: 4
    Last Post: 06-02-2010, 04:50 AM
  3. Sum of Values over date range
    By bosnian in forum Queries
    Replies: 1
    Last Post: 01-12-2010, 03:41 PM
  4. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 AM
  5. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM

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