Date Range Issue

[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_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?

 

[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?

 

[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.