Expression Error in a Parameter Query

[mivan002]

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

 

[John W. Vinson]

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[Duane Hookom]

IMHO, parameter prompts are never good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. This might work better with
criteria from controls on forms.

Using DCount() is queries can be very inefficient. You might be able to use
a subquery in place of the DCount().

 

[mivan002]

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[John Spencer]

Try the following expression if you are going to use DCount expression. You
can use the query itself since you are using parameters. The DCount won't
have the parameter values available - it is a separate instance of the query.

DCount("*","PlanChart","dactl_to <= dschd_to AND dschd_To Between #" & [Enter
Start Date] & "# and #" & [Enter End Date] & "#") as On_Time

Perhaps you could use the following query instead:

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[Duane Hookom]

Again, I don't believe in using parameter prompts. Did you try change these
to reference controls on a form? I also wouldn't use a "domain" in a domain
aggregate function that is the current query. It scares me :-(

Isn't the DCount() going to return the same number for all rows in the
results? Is this what you want?

Is this query used for a report or form record source? If so, the DCount()
could be removed and calculated in the report or form.

--
Duane Hookom
Microsoft Access MVP

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[mivan002]

Thank you John, that worked great. The only problem is that it worked for
this particular expression, but I have a few other expressions that need in
there as well. And when I put them in the query, it goes back to getting the
error. Basically, I need a column that counts the On_Time dates, the LATE
dates, and the other columns find the emptys and then count the empty fields.
Which ultimately I would like it added to be part of the LATE dates. I'm
sure there is a simpler way of doing this but I'm just begining with all of
this. Here are the other expressions that I am trying to have here:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")
empty: IIf(IsNull([dactl_to]),"1")
No_Date: DCount("empty","qryPlanCounts","empty")

Thanks again.

Try the following expression if you are going to use DCount expression. You
can use the query itself since you are using parameters. The DCount won't
have the parameter values available - it is a separate instance of the query.

DCount("*","PlanChart","dactl_to <= dschd_to AND dschd_To Between #" & [Enter
Start Date] & "# and #" & [Enter End Date] & "#") as On_Time

Perhaps you could use the following query instead:

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

On Tue, 28 Oct 2008 18:41:01 -0700, mivan002

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.
Please post the actual SQL view of your query.

 

[mivan002]

The DCount isn't for a form or a report, it will be used with charts. But
yes, the DCount returns the same number for all rows, which as far as I know
doesn't matter as long as it returns the correct number. Also, that's not
the only thing that I need it to return, I basically need it to count how
many on time and how many lates (including the empty).

Thanks

Again, I don't believe in using parameter prompts. Did you try change these
to reference controls on a form? I also wouldn't use a "domain" in a domain
aggregate function that is the current query. It scares me :-(

Isn't the DCount() going to return the same number for all rows in the
results? Is this what you want?

Is this query used for a report or form record source? If so, the DCount()
could be removed and calculated in the report or form.

--
Duane Hookom
Microsoft Access MVP

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

On Tue, 28 Oct 2008 18:41:01 -0700, mivan002

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[Duane Hookom]

I would create a query that returns one record with the count. Then add this
query to your chart's Row Souce query. If you need another value, repeat this
to add the other value.

Again, don't use parameter prompts.
--
Duane Hookom
Microsoft Access MVP

The DCount isn't for a form or a report, it will be used with charts. But
yes, the DCount returns the same number for all rows, which as far as I know
doesn't matter as long as it returns the correct number. Also, that's not
the only thing that I need it to return, I basically need it to count how
many on time and how many lates (including the empty).

Thanks

Again, I don't believe in using parameter prompts. Did you try change these
to reference controls on a form? I also wouldn't use a "domain" in a domain
aggregate function that is the current query. It scares me :-(

Isn't the DCount() going to return the same number for all rows in the
results? Is this what you want?

Is this query used for a report or form record source? If so, the DCount()
could be removed and calculated in the report or form.

--
Duane Hookom
Microsoft Access MVP

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

:

On Tue, 28 Oct 2008 18:41:01 -0700, mivan002

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.

Please post the actual SQL view of your query.

 

[John Spencer]

I would guess that you need to use a reference to the table in the other
expressions also.

DCount("*","PlanChart","dactl_to > dschd_to AND dschd_To Between #" &
[Enter Start Date] & "# and #" & [Enter End Date] & "#") as Late

DCount("*","PlanChart","dactl_to Is Null AND dschd_To Between #" &
[Enter Start Date] & "# and #" & [Enter End Date] & "#") as No_Date

There is probably a better way of doing this. A query like the one belows
would give you the counts.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Abs(Sum(dactl_to <= dschd_to)) as On_Time
, Abs(Sum(dactl_to > dschd)) as Late
, Abs(Sum(dactl_to Is Null)) as No_Date
FROM PlanChart
WHERE deSchd_To Between [Enter Start Date] and [Enter End Date]

You should then be able to add the above to your original query to get the
results you apparently want. The following MAY work for you.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase]
, [Design Phase Buckets].[Bucket #]
, planchart.cmnum
, planchart.cdrawingno
, planchart.dschd_to
, planchart.dactl_to
, planchart.[New destdone]
, q.On_Time
, q.Late
, q.No_Date

FROM qSavedQuery as q ,
([Design Phase Buckets] INNER JOIN planchart
ON ([Design Phase Buckets].[DWG #] = planchart.cdrawingno)
AND ([Design Phase Buckets].MNUM = planchart.cmnum))
WHERE (((planchart.dschd_to) Between [Enter Start Date]
And [Enter End Date]));

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you John, that worked great. The only problem is that it worked for
this particular expression, but I have a few other expressions that need in
there as well. And when I put them in the query, it goes back to getting the
error. Basically, I need a column that counts the On_Time dates, the LATE
dates, and the other columns find the emptys and then count the empty fields.
Which ultimately I would like it added to be part of the LATE dates. I'm
sure there is a simpler way of doing this but I'm just begining with all of
this. Here are the other expressions that I am trying to have here:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")
empty: IIf(IsNull([dactl_to]),"1")
No_Date: DCount("empty","qryPlanCounts","empty")

Thanks again.

Try the following expression if you are going to use DCount expression. You
can use the query itself since you are using parameters. The DCount won't
have the parameter values available - it is a separate instance of the query.

DCount("*","PlanChart","dactl_to <= dschd_to AND dschd_To Between #" & [Enter
Start Date] & "# and #" & [Enter End Date] & "#") as On_Time

Perhaps you could use the following query instead:

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

The query is called TEST and here is the SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Design Phase Buckets].[Design Phase], [Design Phase Buckets].[Bucket
#], planchart.cmnum, planchart.cdrawingno, planchart.dschd_to,
planchart.dactl_to, planchart.[New destdone],
DCount("dactl_to","TEST","dactl_to <= dschd_to") AS On_Time
FROM [Design Phase Buckets] INNER JOIN planchart ON ([Design Phase
Buckets].[DWG #] = planchart.cdrawingno) AND ([Design Phase Buckets].MNUM =
planchart.cmnum)
WHERE (((planchart.dschd_to) Between [Enter Start Date] And [Enter End
Date]));

Thank you again.

:

On Tue, 28 Oct 2008 18:41:01 -0700, mivan002

All I am trying to do is add a parameter into the schedule_to field that
says: Between [Enter Start Date] And [Enter End Date]
If in my query I delete the expressions that I have put in prior to adding
the parameter, this works perfectly. It asks for start date and then for the
end date. The problem is that I have three different expressions that are
doing different counting and comparing in different columns and as soon as I
add any one of those expressions into the query I get the following error:
"The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'Enter Start Date' you entered
in the expression"

Here is one of the expressions I am using in another column of the query:
LATE: DCount("dactl_to","qryPlanCounts","dactl_to > dschd_to")

Any help would be greatly appreciated and thank you in advance.
Please post the actual SQL view of your query.