Parameter Query and Calculated Date Field

[Musa via AccessMonster.com]

Hello,

I have a field in a query that calculates 180 days from another date field
in the same query. When I try to place a parameter under the calculated field
the results are incorrect. I have Between [Enter Start Date] and [Enter End
Date] under the calculated field. The purpose is for the User to be able to
place dates in the fields and get a report listing the forms coming due for
that selected time range.

Thanks

 

[Douglas J. Steele]

Can you show the SQL of your query, as well as indicate what you're
providing for [Enter Start Date] and [Enter End Date]?

 

[Jeff Boyce]

What happens if you substitute actual date valules (e.g., #9/1/2007#,
#10/1/2008#) in place of the [] parameters in your selection criterion?

What happens when you run the query without selection criteria -- that is,
what is showing in the calculated date field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Musa via AccessMonster.com]

Here is the SQL for the Query.. I place a dates in the Enter Start Date and
Enter End Date e.g., 9/1/2008 Short Format

SELECT tblCLIENT.SurveyID, tblCLIENT.FNAME, tblCLIENT.LNAME, tblREFERRAL.
SEQ_ID1, tblREFERRAL.REFERRAL_DT, tblREFERRAL.OUTREACH_completed, CVDate(
[OUTREACH_completed]+180) AS CalcDt
FROM tblCLIENT INNER JOIN tblREFERRAL ON tblCLIENT.SurveyID = tblREFERRAL.
SurveyID
GROUP BY tblCLIENT.SurveyID, tblCLIENT.FNAME, tblCLIENT.LNAME, tblREFERRAL.
SEQ_ID1, tblREFERRAL.REFERRAL_DT, tblREFERRAL.OUTREACH_completed, CVDate(
[OUTREACH_completed]+180);

Can you show the SQL of your query, as well as indicate what you're
providing for [Enter Start Date] and [Enter End Date]?

Hello,

[quoted text clipped - 10 lines]

Thanks

 

[Douglas J. Steele]

That SQL doesn't have a WHERE clause, so it can't be the SQL that's causing
you problems.

I assume that running that particular query doesn't cause any problems. (For
what it's worth, if [OUTREACH_completed] is a date field, there's no need
for the CVDate function.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is the SQL for the Query.. I place a dates in the Enter Start Date
and
Enter End Date e.g., 9/1/2008 Short Format

SELECT tblCLIENT.SurveyID, tblCLIENT.FNAME, tblCLIENT.LNAME, tblREFERRAL.
SEQ_ID1, tblREFERRAL.REFERRAL_DT, tblREFERRAL.OUTREACH_completed, CVDate(
[OUTREACH_completed]+180) AS CalcDt
FROM tblCLIENT INNER JOIN tblREFERRAL ON tblCLIENT.SurveyID = tblREFERRAL.
SurveyID
GROUP BY tblCLIENT.SurveyID, tblCLIENT.FNAME, tblCLIENT.LNAME,
tblREFERRAL.
SEQ_ID1, tblREFERRAL.REFERRAL_DT, tblREFERRAL.OUTREACH_completed, CVDate(
[OUTREACH_completed]+180);

Can you show the SQL of your query, as well as indicate what you're
providing for [Enter Start Date] and [Enter End Date]?

Hello,

[quoted text clipped - 10 lines]

Thanks

 

[Musa via AccessMonster.com]

If I remove the criteria all the records show on the report..
I would like for the User to choose the date range based on that Calculated
Field..

What happens if you substitute actual date valules (e.g., #9/1/2007#,
#10/1/2008#) in place of the [] parameters in your selection criterion?

What happens when you run the query without selection criteria -- that is,
what is showing in the calculated date field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 10 lines]

Thanks

 

[Jeff Boyce]

I wasn't suggesting that you leave it out in your finished product, just
change it to get an idea of where it might be breaking.

What does that calculated field display?

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I remove the criteria all the records show on the report..
I would like for the User to choose the date range based on that
Calculated
Field..

What happens if you substitute actual date valules (e.g., #9/1/2007#,
#10/1/2008#) in place of the [] parameters in your selection criterion?

What happens when you run the query without selection criteria -- that is,
what is showing in the calculated date field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 10 lines]

Thanks

 

[Musa via AccessMonster.com]

The Calculated Field Displays the following ..
CalcDt: ([OUTREACH_completed]+180)

If I remove the criteria all the records show on the report..
I would like for the User to choose the date range based on that Calculated
Field..

What happens if you substitute actual date valules (e.g., #9/1/2007#,
#10/1/2008#) in place of the [] parameters in your selection criterion?

[quoted text clipped - 12 lines]

 

[Musa via AccessMonster.com]

Thanks.. If Figured it out.. I had to place the following in the parameter
query criteria
Between CDate [Start] and CDate [End]

I wasn't suggesting that you leave it out in your finished product, just
change it to get an idea of where it might be breaking.

What does that calculated field display?

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I remove the criteria all the records show on the report..
I would like for the User to choose the date range based on that

[quoted text clipped - 17 lines]

 

[John W. Vinson]

Thanks.. If Figured it out.. I had to place the following in the parameter
query criteria
Between CDate [Start] and CDate [End]

Actually that should be

Between CDate([Start]) and CDate([End])

or, better, put a line

PARAMETERS [Start] DateTime, [End] DateTime;

at the top of the SQL of the query; or equivalently, rightclick the grey
background of the tables, select Parameters, and add your two prompts to the
parameter grid, specifying date/time.