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 what i am doing wrong?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Date Range help
- Thread starter ttechie
- Start date
Look up "between".
Mark P.
Providing Low Cost Powerful Point of Sale Solutions.
Mark P.
Providing Low Cost Powerful Point of Sale Solutions.
- Thread starter
- #3
We need a better description of the error or behavior to figure out what's going wrong here. If you are filtering directly on the table, you have to enclose your dates with #, like #1/1/2005# Might be able to help with a little more information.
----
JBR
----
JBR
- Thread starter
- #5
Ahh, I don't get any error, but I don't get any data either when i user date range. 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, 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;
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;
Just curious...
"I used Look up "between" in criteria under field name DATERECEIVED but I still got nothing"
but you used this syntax
AND ((dbo_DIARYLOG.DIARYDATE) Between [StartDate] And [EndDate]))
Did you mean DIARYDATE or DATERECEIVED?
"I used Look up "between" in criteria under field name DATERECEIVED but I still got nothing"
but you used this syntax
AND ((dbo_DIARYLOG.DIARYDATE) Between [StartDate] And [EndDate]))
Did you mean DIARYDATE or DATERECEIVED?
- Thread starter
- #7
You have to know what you want:
WHERE (dbo_DIARYLOG.USERID=[forms]![reports Form].[cbo_user])
AND ([highlight]Format([DIARYDATE],"mm/dd/yyyy")=[forms]![reports Form].[reportdate][/highlight])
AND (dbo_DIARYLOG.ITEM Like '*post*' Or dbo_DIARYLOG.ITEM Like '*paperpro*')
AND ([highlight]dbo_DIARYLOG.DIARYDATE Between [StartDate] And [EndDate][/highlight])
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
WHERE (dbo_DIARYLOG.USERID=[forms]![reports Form].[cbo_user])
AND ([highlight]Format([DIARYDATE],"mm/dd/yyyy")=[forms]![reports Form].[reportdate][/highlight])
AND (dbo_DIARYLOG.ITEM Like '*post*' Or dbo_DIARYLOG.ITEM Like '*paperpro*')
AND ([highlight]dbo_DIARYLOG.DIARYDATE Between [StartDate] And [EndDate][/highlight])
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
- Thread starter
- #9
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 guys for your help.
thanks guys for your help.
Why comparing dates as strings ?
If you insist on this, then at least use a coherent (chronological) string representation:
Format([DateTime field], 'yyyymmdd')
But I suggest something like this (SQL code):
PARAMETERS [Forms]![Reports Form]![ReportStartMonth] DateTime, [Forms]![Reports Form]![ReportEndMonth] DateTime;
SELECT ...
FROM ...
WHERE (dbo_DIARYLOG.DIARYDATE Between [Forms]![Reports Form]![ReportStartMonth] And [Forms]![Reports Form]![ReportEndMonth])
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
If you insist on this, then at least use a coherent (chronological) string representation:
Format([DateTime field], 'yyyymmdd')
But I suggest something like this (SQL code):
PARAMETERS [Forms]![Reports Form]![ReportStartMonth] DateTime, [Forms]![Reports Form]![ReportEndMonth] DateTime;
SELECT ...
FROM ...
WHERE (dbo_DIARYLOG.DIARYDATE Between [Forms]![Reports Form]![ReportStartMonth] And [Forms]![Reports Form]![ReportEndMonth])
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
The thread is spinning out of control... Back up and review what you asked... If this is the query you are trying to fix, look at what is wrong with it:
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;
In your query you have asked for records where the [DIARYDATE] EQUALS the [reportdate] AND the [DIARYDATE] is BETWEEN [StartDate] And [EndDate]. This will produce records for a single date (EQUAL to [reportdate]) and only if the [reportdate] is between the [StartDate] And [EndDate]. (i.e., if [reportdate] is 11/29/2005 and [StartDate] is 11/1/2005 and [EndDate] is 11/10/2005, you will get 0 records.)
If you want records for [DIARYDATE] within a date range, try removing the expression ((Format([DIARYDATE],"mm/dd/yyyy"))=[forms]![reports Form].[reportdate]) from your query.
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;
In your query you have asked for records where the [DIARYDATE] EQUALS the [reportdate] AND the [DIARYDATE] is BETWEEN [StartDate] And [EndDate]. This will produce records for a single date (EQUAL to [reportdate]) and only if the [reportdate] is between the [StartDate] And [EndDate]. (i.e., if [reportdate] is 11/29/2005 and [StartDate] is 11/1/2005 and [EndDate] is 11/10/2005, you will get 0 records.)
If you want records for [DIARYDATE] within a date range, try removing the expression ((Format([DIARYDATE],"mm/dd/yyyy"))=[forms]![reports Form].[reportdate]) from your query.
- Status
Similar threads
