Between [start date] and [end date]

  • Thread starter spcscooter via AccessMonster.com
  • Start date
S

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 Expires:DateAdd
("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
 
J

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
 
S

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]));


KARL said:
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]

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA

Message posted via AccessMonster.com
 
S

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?

Jeff said:
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]

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA

Message posted via AccessMonster.com
 
K

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


spcscooter via AccessMonster.com said:
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]));


KARL said:
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]

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA

Message posted via AccessMonster.com
 
J

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

Jeff Boyce

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

spcscooter via AccessMonster.com said:
Jeff,

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

Jeff said:
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]

--
Scot Rawlings
Technical Trainer
Comcast
Auburn, WA

Message posted via AccessMonster.com
 

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

Top