Date range parameter query

[Guest]

Good morning,

I need to know how to set up and what to use as criteria for a parameter
query that will prompt for a start date and end date and then bring up
records = to and between those dates. I was thinking of creating a start date
field w/ criteria >= and end date field w/ criteria of <= but I am not sure
if that will work or what syntax I should use to include a control statement
like >= and a prompt statement like [Enter Start Date:]. Any suggestions?
Thanks.

 

[Guest]

Use this for criteria --
Between [Enter start date] and [Enter end date]

 

[John Vinson]

Good morning,

I need to know how to set up and what to use as criteria for a parameter
query that will prompt for a start date and end date and then bring up
records = to and between those dates. I was thinking of creating a start date
field w/ criteria >= and end date field w/ criteria of <= but I am not sure
if that will work or what syntax I should use to include a control statement
like >= and a prompt statement like [Enter Start Date:]. Any suggestions?
Thanks.

It makes a difference if the table field you're searching has (or
might have) a time component: #7/1/2005 17:58:20# is in fact NOT
between #6/30/2005# and #7/1/2005#, though you may well want that
record retrieved.

A robust criterion which works for me is

= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))

John W. Vinson[MVP]

 

[Guest]

I am using the query below and when I try to test it out I get "Syntax Error
(missing operator) in query expression 'ServiceNote.DateWritten >=
CDate([Enter start date:]) AND < DateAdd("d",1,CDate([Enter end date:]))'.

Can you help me out

SELECT ParticipantInfo.LastName, ParticipantInfo.FirstName,
ParticipantInfo.Medicaid, ParticipantInfo.Intensive,
ParticipantInfo.FundingSource, ParticipantInfo.ValuedOutcome,
ServiceNote.Location, ServiceNote.DateServiceStart,
ServiceNote.DateServiceEnd, ServiceNote.ServiceProvided,
ServiceNote.ResponseRendered, ServiceNote.DateToSign,
ServiceNote.DateWritten, ServiceNote.[Notes-SpecialInstructions],
IntensiveTimeLog.[1Date], IntensiveTimeLog.[1WKH], IntensiveTimeLog.[1PE],
IntensiveTimeLog.[1ON], IntensiveTimeLog.[1OFF], IntensiveTimeLog.[2Date],
IntensiveTimeLog.[2WKH], IntensiveTimeLog.[2PE], IntensiveTimeLog.[2ON],
IntensiveTimeLog.[2OFF], IntensiveTimeLog.[3Date], IntensiveTimeLog.[3WKH],
IntensiveTimeLog.[3PE], IntensiveTimeLog.[3ON], IntensiveTimeLog.[3OFF],
IntensiveTimeLog.[4Date], IntensiveTimeLog.[4WKH], IntensiveTimeLog.[4PE],
IntensiveTimeLog.[4ON], IntensiveTimeLog.[4OFF], IntensiveTimeLog.[5Date],
IntensiveTimeLog.[5WKH], IntensiveTimeLog.[5PE], IntensiveTimeLog.[5ON],
IntensiveTimeLog.[5OFF], IntensiveTimeLog.[6Date], IntensiveTimeLog.[6WKH],
IntensiveTimeLog.[6PE], IntensiveTimeLog.[6ON], IntensiveTimeLog.[6OFF],
IntensiveTimeLog.[7Date], IntensiveTimeLog.[7WKH], IntensiveTimeLog.[7PE],
IntensiveTimeLog.[7ON], IntensiveTimeLog.[7OFF]
FROM (ParticipantInfo INNER JOIN ServiceNote ON
ParticipantInfo.ParticipantID = ServiceNote.ParicipantID) INNER JOIN
IntensiveTimeLog ON ServiceNote.ServiceNoteID = IntensiveTimeLog.ServiceNoteID
WHERE ServiceNote.DateWritten >= CDate([Enter start date:]) AND <
DateAdd("d", 1, CDate([Enter end date:]));

Good morning,

I need to know how to set up and what to use as criteria for a parameter
query that will prompt for a start date and end date and then bring up
records = to and between those dates. I was thinking of creating a start date
field w/ criteria >= and end date field w/ criteria of <= but I am not sure
if that will work or what syntax I should use to include a control statement
like >= and a prompt statement like [Enter Start Date:]. Any suggestions?
Thanks.

It makes a difference if the table field you're searching has (or
might have) a time component: #7/1/2005 17:58:20# is in fact NOT
between #6/30/2005# and #7/1/2005#, though you may well want that
record retrieved.

A robust criterion which works for me is

= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))

John W. Vinson[MVP]

 

[Jamie Collins]

A robust criterion which works for me is

= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))

The 'robust criterion' did not work fine for me using the valid
datetime value of 9999-12-31 <g>.

Jamie.

--

 

[Pieter Wijnen]

It's not a valid Date in Access though, I believe a day in 7xxx AD is the
Max Date, but I won't bother to check it out

Pieter

A robust criterion which works for me is

= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end
date:]))

The 'robust criterion' did not work fine for me using the valid
datetime value of 9999-12-31 <g>.

Jamie.

 

[Pieter Wijnen]

I retract, It's the Last Valid Date, so you can't add to it - missed the <g>
Pieter

"Pieter Wijnen"

It's not a valid Date in Access though, I believe a day in 7xxx AD is the
Max Date, but I won't bother to check it out

Pieter

A robust criterion which works for me is

= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end
date:]))

The 'robust criterion' did not work fine for me using the valid
datetime value of 9999-12-31 <g>.

Jamie.

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!