Between [start date] and [end date]

[spcscooter via AccessMonster.com]

I am having a problem with criteria where I am trying to return
certifications that expire between two dates. The criteria works in one
database but not the one I am working on now. The query looks like this;

Employee ID Last Name First Name LH Cert Date ExpiresateAdd
("yyyy",1,[LH Cert Date])

Between [Start Date] and [End Date]

Now when I enter the start date as 01/01/07 and the end date as 01/01/08 I
get nothing back. But when I enter 01/01/07 for the start and 12/31/07 for
the end I get back records that will expire in 2008.

This stuff really frustrates me. And I am sure that it's simple.

Thanks

 

[KARL DEWEY]

Post your SQL statement.

 

[Jeff Boyce]

Since those are dates, see what happens if you add the date delimiters
around the prompts, something like (untested aircode):

Between # & [Start Date] & # And # & [End Date] & #

Or try using the Query | Parameter menu choice and designate those two
prompts as Date/Time datatypes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[spcscooter via AccessMonster.com]

SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name], [Master
Roster].[First Name], [Master Roster].[Ladder Certification Date], DateAdd
("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE (((DateAdd("yyyy",1,[Ladder Certification Date])) Between [Start Date]
And [End Date]));

Post your SQL statement.

I am having a problem with criteria where I am trying to return
certifications that expire between two dates. The criteria works in one

[quoted text clipped - 12 lines]

Thanks

 

[spcscooter via AccessMonster.com]

Jeff,

I tried the satement that you put in your post and was given and error window.
Can you help me with that?

Since those are dates, see what happens if you add the date delimiters
around the prompts, something like (untested aircode):

Between # & [Start Date] & # And # & [End Date] & #

Or try using the Query | Parameter menu choice and designate those two
prompts as Date/Time datatypes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having a problem with criteria where I am trying to return
certifications that expire between two dates. The criteria works in one

[quoted text clipped - 13 lines]

Thanks

 

[KARL DEWEY]

Try this ---
SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name], [Master
Roster].[First Name], [Master Roster].[Ladder Certification Date],
DateAdd("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE (((DateAdd("yyyy",1,[Ladder Certification Date])) Between
CVDate([Start Date]) And CVDate([End Date])));

--
KARL DEWEY
Build a little - Test a little

SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name], [Master
Roster].[First Name], [Master Roster].[Ladder Certification Date], DateAdd
("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE (((DateAdd("yyyy",1,[Ladder Certification Date])) Between [Start Date]
And [End Date]));

Post your SQL statement.

I am having a problem with criteria where I am trying to return
certifications that expire between two dates. The criteria works in one

[quoted text clipped - 12 lines]

Thanks

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA

 

[John Spencer]

Are you sure that LH Cert Date is a datetime field and not a text field that
contains a string that looks like a date?

Try the following which declares the parameter types

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name]
, [Master Roster].[First Name], [Master Roster].[Ladder Certification Date]
, DateAdd("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE (((DateAdd("yyyy",1,[Ladder Certification Date])) Between [Start Date]
And [End Date]));

OR this variation, which should be more efficient.
Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name]
, [Master Roster].[First Name], [Master Roster].[Ladder Certification Date]
, DateAdd("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE [Ladder Certification Date] Between DateAdd("yyyy",-1,[Start Date])
And DateAdd("yyyy",-1,[End Date])

IF both those fail try

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT [Master Roster].[Employee ID], [Master Roster].[Last Name]
, [Master Roster].[First Name], [Master Roster].[Ladder Certification Date]
, DateAdd("yyyy",1,[Ladder Certification Date]) AS Expires
FROM [Master Roster]
WHERE DateValue([Ladder Certification Date])
Between DateAdd("yyyy",-1,[Start Date])
And DateAdd("yyyy",-1,[End Date])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeff Boyce]

I can't help what I can't see...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I tried the satement that you put in your post and was given and error
window.
Can you help me with that?

Since those are dates, see what happens if you add the date delimiters
around the prompts, something like (untested aircode):

Between # & [Start Date] & # And # & [End Date] & #

Or try using the Query | Parameter menu choice and designate those two
prompts as Date/Time datatypes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having a problem with criteria where I am trying to return
certifications that expire between two dates. The criteria works in one

[quoted text clipped - 13 lines]

Thanks

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA