Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Date Range Help

  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: Date Range Help

    I am working on a database that someone else wrote. I am trying to get the start and end date from the database using three tables. I have used criteria Between [StartDate] And [EndDate] then I chose Parameters Date/time. But no luck! The database works fine when I retrive date using calender, the calender only let me pick one day at time. but when I use parameters to get date range I get nothing. I have the follwowing fields DateCreated, DateAdmitted, and DATERECEIVED. Any Idea?
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Mark
    it might make it easier for others if you include the actual SQL you have tried aswell as the narrative

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Try to include your date between # and #
    i.e

    SELECT * From yourTable Where birthday between #05.03.1970# And #05.01.1970# #05.31.1970#

    Gets the people born between May the first and the thirty first

  4. #4
    Join Date
    Nov 2005
    Posts
    29
    use query design grid to define the date range expression under the column of your desire....eg if you want to display date range for DATE CREATED field then you have to define it under DateCreated column....

  5. #5
    Join Date
    Nov 2005
    Posts
    29
    use query design grid to define the date range expression under the column of your desire....eg if you want to display date range for DATE CREATED field then you have to define it under DateCreated column....

  6. #6
    Join Date
    Jun 2005
    Posts
    45
    Thanks guys for the help, But still no luck yet. below is the code I am using I have access 2000.

    PARAMETERS StartDate DateTime, EndDate DateTime;
    SELECT DISTINCT dbo_DIARYLOG.USERID, dbo_CLAIM.PHYSID, dbo_PROVIDERS.LASTNAME, dbo_CLAIM.CURRENTQ, Format([DIARYDATE],"mm/dd/yyyy") AS FinishedDate, dbo_DIARYLOG.ITEM, dbo_CLAIM.CSSNUM, dbo_CLAIM.INUSEBY, dbo_DIARYLOG.DIARYDATE
    FROM (dbo_DIARYLOG INNER JOIN dbo_CLAIM ON dbo_DIARYLOG.CSSNUM = dbo_CLAIM.CSSNUM) INNER JOIN dbo_PROVIDERS ON dbo_CLAIM.PHYSID = dbo_PROVIDERS.PHYSID
    WHERE (((dbo_DIARYLOG.USERID)=[forms]![reports Form].[cbo_user]) AND ((Format([DIARYDATE],"mm/dd/yyyy"))=[forms]![reports Form].[reportdate]) AND ((dbo_DIARYLOG.ITEM) Like '*post*' Or (dbo_DIARYLOG.ITEM) Like '*paperpro*') AND ((dbo_DIARYLOG.DIARYDATE) Between [StartDate] And [EndDate]))
    ORDER BY dbo_CLAIM.PHYSID;

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Does it work WITHOUT the date range criteria?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Dates

    I attached a small reporting program we use which uses the date criteria for a report. Maybe there's something in it which might help.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2005
    Posts
    45
    Yes, It does when I use calender for a specific day. Say 11/21/2005. It works but when i use parameters to get day range I get nothing.
    I am trying to get date range for a me so that I can audit the end user. However, I can audit the end user "finished claims" on per day only. But I would like this in a date range so I can audit the end user say weeks, days or months. below is the sql view for you to look at. I have the following fields admitted. created, card recived, reviewed, and claim finished.
    Thanks for the help.

    PARAMETERS StartDate DateTime, EndDate DateTime;
    SELECT DISTINCT dbo_DIARYLOG.USERID, dbo_CLAIM.PHYSID, dbo_PROVIDERS.LASTNAME, dbo_CLAIM.CURRENTQ, Format([DIARYDATE],"mm/dd/yyyy") AS FinishedDate, dbo_DIARYLOG.ITEM, dbo_CLAIM.CSSNUM, dbo_CLAIM.INUSEBY, dbo_DIARYLOG.DIARYDATE
    FROM (dbo_DIARYLOG INNER JOIN dbo_CLAIM ON dbo_DIARYLOG.CSSNUM = dbo_CLAIM.CSSNUM) INNER JOIN dbo_PROVIDERS ON dbo_CLAIM.PHYSID = dbo_PROVIDERS.PHYSID
    WHERE (((dbo_DIARYLOG.USERID)=[forms]![reports Form].[cbo_user]) AND ((Format([DIARYDATE],"mm/dd/yyyy"))=[forms]![reports Form].[reportdate]) AND ((dbo_DIARYLOG.ITEM) Like '*post*' Or (dbo_DIARYLOG.ITEM) Like '*paperpro*') AND ((dbo_DIARYLOG.DIARYDATE) Between [StartDate] And [EndDate]))
    ORDER BY dbo_CLAIM.PHYSID;

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think your problem may lie in the fact that you format the date - its no longer a date field in the row.

    I'd also consider using Hambakka's suggetsion on bractketing your dates with the hash symbol "#"

    HTH

  11. #11
    Join Date
    Jun 2005
    Posts
    45
    I got it to work using Between [Forms]![Reports Form]![ReportStartMonth] And [Forms]![Reports Form]![ReportEndMonth] in "FinishedDate: Format([DIARYDATE],"mm/dd/yyyy")" criteria. But now I have another question. When I enter start date 11/21/2005 and end date 11/22/2005. I get not only these two days activity but I also get 11/22/2003 and 11/22/2002. Is there a way just to get specific days requested.
    thanks

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    (yourDate = targetDate OR yourDate = othertargetDate)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jun 2005
    Posts
    45
    I have two text box start date and end date. Every is finally working except when I enter start date 11/21/2005 and end date 11/22/2005. I get not only these two days activity but I also get 11/21/2003 and 11/22/2002 activity.
    Thanks for Help!
    Last edited by markwaugh; 11-29-05 at 13:51.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You mentioned that... perhaps you should try my solution.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Jun 2005
    Posts
    45
    Sorry, Teddy, Ahh where and How do would I use this (yourDate = targetDate OR yourDate = othertargetDate). Sorry, I am a bit confused.
    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
  •