Date Range Issue

T

tslaght

I've created 3 queries to determine which files are due within 1 week,
2 week and 3 weeks.

The code i put in the query for files due in 1 week is the following:

Between Date() And DateAdd("d",7,Date())

For 2 Weeks
Between DateAdd("d",8,Date()) And DateAdd("d",14,Date())

For 3 Weeks
Between DateAdd("d",15,Date()) And DateAdd("d",21,Date())


The code I have for the comman button is

Dim stDocName As String
Dim strWhere As String

stDocName = "File Tracking Query 1 Week"
strWhere = "[Name]=""" & Me.[Name] & """"

DoCmd.OpenReport stDocName, acPreview, , strWhere


The purpose of the command button is to open a report for a specific
person to show all their files due in the next week.

The problem I have is that even though I've entered a date within 1
week of the current date it doesn't show up. I previously had an
overlap where the first date range was between todays date and 7 days
and for week 2 it was 7 days to 14 days. In this instance the file
showed up in both the 1 week and 2 week selections. I understand why
it would happen in that instance but not when i changed to it from 1-7
and 8-14.

Any thoughts on what the issue might be?
 
J

J_Goddard via AccessMonster.com

Does the record appear if the date is e.g. for tomorrow or two days from now?

John





I've created 3 queries to determine which files are due within 1 week,
2 week and 3 weeks.

The code i put in the query for files due in 1 week is the following:

Between Date() And DateAdd("d",7,Date())

For 2 Weeks
Between DateAdd("d",8,Date()) And DateAdd("d",14,Date())

For 3 Weeks
Between DateAdd("d",15,Date()) And DateAdd("d",21,Date())

The code I have for the comman button is

Dim stDocName As String
Dim strWhere As String

stDocName = "File Tracking Query 1 Week"
strWhere = "[Name]=""" & Me.[Name] & """"

DoCmd.OpenReport stDocName, acPreview, , strWhere

The purpose of the command button is to open a report for a specific
person to show all their files due in the next week.

The problem I have is that even though I've entered a date within 1
week of the current date it doesn't show up. I previously had an
overlap where the first date range was between todays date and 7 days
and for week 2 it was 7 days to 14 days. In this instance the file
showed up in both the 1 week and 2 week selections. I understand why
it would happen in that instance but not when i changed to it from 1-7
and 8-14.

Any thoughts on what the issue might be?
 
J

John Spencer

To be absolutely safe you might try the following. This will help with
dates that also have a time component
=Date() And <("d",8,Date())

For 2 Weeks
=DateAdd("d",8,Date()) And < DateAdd("d",15,Date())

For 3 Weeks
= DateAdd("d",15,Date()) And < DateAdd("d",22,Date())

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've created 3 queries to determine which files are due within 1 week,
2 week and 3 weeks.

The code i put in the query for files due in 1 week is the following:

Between Date() And DateAdd("d",7,Date())

For 2 Weeks
Between DateAdd("d",8,Date()) And DateAdd("d",14,Date())

For 3 Weeks
Between DateAdd("d",15,Date()) And DateAdd("d",21,Date())


The code I have for the comman button is

Dim stDocName As String
Dim strWhere As String

stDocName = "File Tracking Query 1 Week"
strWhere = "[Name]=""" & Me.[Name] & """"

DoCmd.OpenReport stDocName, acPreview, , strWhere


The purpose of the command button is to open a report for a specific
person to show all their files due in the next week.

The problem I have is that even though I've entered a date within 1
week of the current date it doesn't show up. I previously had an
overlap where the first date range was between todays date and 7 days
and for week 2 it was 7 days to 14 days. In this instance the file
showed up in both the 1 week and 2 week selections. I understand why
it would happen in that instance but not when i changed to it from 1-7
and 8-14.

Any thoughts on what the issue might be?
 
T

tslaght

To be absolutely safe you might try the following.  This will help with
dates that also have a time component
=Date() And <("d",8,Date())

For 2 Weeks
=DateAdd("d",8,Date()) And <  DateAdd("d",15,Date())

For 3 Weeks
= DateAdd("d",15,Date()) And < DateAdd("d",22,Date())

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.




I've created 3 queries to determine which files are due within 1 week,
2 week and 3 weeks.
The code i put in the query for files due in 1 week is the following:
Between Date() And DateAdd("d",7,Date())
For 2 Weeks
Between DateAdd("d",8,Date()) And DateAdd("d",14,Date())
For 3 Weeks
Between DateAdd("d",15,Date()) And DateAdd("d",21,Date())
The code I have for the comman button is
   Dim stDocName As String
   Dim strWhere As String
   stDocName = "File Tracking Query 1 Week"
   strWhere = "[Name]=""" & Me.[Name] & """"
   DoCmd.OpenReport stDocName, acPreview, , strWhere
The purpose of the command button is to open a report for a specific
person to show all their files due in the next week.
The problem I have is that even though I've entered a date within 1
week of the current date it doesn't show up.  I previously had an
overlap where the first date range was between todays date and 7 days
and for week 2 it was 7 days to 14 days.  In this instance the file
showed up in both the 1 week and 2 week selections.  I understand why
it would happen in that instance but not when i changed to it from 1-7
and 8-14.
Any thoughts on what the issue might be?- Hide quoted text -

- Show quoted text -

I fixed my problem

Moral of the story is to check you have selected the appropriate
record source.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top