Charts and criteria in the underlying Query

R

Ray

I have a chart on a report that is based on the results of 3 queries. Query1
generates data that is used by Query2 which which generates data that is used
by Query3 which provides the data for the chart. I have a form which allows
me to select the date range to be used in Query1. I have placed the following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " & [Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which contains the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or expression.

Why would Query3 run fine, but when placed in a chart in a report it fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date range. (the
form is open when I do this)

Please help
 
D

Duane Hookom

Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
 
R

Ray

It worked just fine. Had not expected that to be the problem. Thanks.
In keeping with the situation I listed below, I want to filter Query1 using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue is that it
IsNull many times. I have tried adding something like the following to the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is "A-Shift" or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no result. Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to avoid the
first issue.)

Duane Hookom said:
Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



Ray said:
I have a chart on a report that is based on the results of 3 queries.
Query1
generates data that is used by Query2 which which generates data that is
used
by Query3 which provides the data for the chart. I have a form which
allows
me to select the date range to be used in Query1. I have placed the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date range.
(the
form is open when I do this)

Please help
 
D

Duane Hookom

What is "IsNull many times" the field or the control on the form? You might
be able to use something like:

WHERE Nz([Shift],"NA") = Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

Ray said:
It worked just fine. Had not expected that to be the problem. Thanks.
In keeping with the situation I listed below, I want to filter Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue is that
it
IsNull many times. I have tried adding something like the following to the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is "A-Shift" or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to avoid
the
first issue.)

Duane Hookom said:
Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



Ray said:
I have a chart on a report that is based on the results of 3 queries.
Query1
generates data that is used by Query2 which which generates data that
is
used
by Query3 which provides the data for the chart. I have a form which
allows
me to select the date range to be used in Query1. I have placed the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date range.
(the
form is open when I do this)

Please help
 
R

Ray

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank (Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank (Null)

Duane Hookom said:
What is "IsNull many times" the field or the control on the form? You might
be able to use something like:

WHERE Nz([Shift],"NA") = Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

Ray said:
It worked just fine. Had not expected that to be the problem. Thanks.
In keeping with the situation I listed below, I want to filter Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue is that
it
IsNull many times. I have tried adding something like the following to the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is "A-Shift" or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to avoid
the
first issue.)

Duane Hookom said:
Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3 queries.
Query1
generates data that is used by Query2 which which generates data that
is
used
by Query3 which provides the data for the chart. I have a form which
allows
me to select the date range to be used in Query1. I have placed the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date range.
(the
form is open when I do this)

Please help
 
D

Duane Hookom

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

Ray said:
I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank (Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank (Null)

Duane Hookom said:
What is "IsNull many times" the field or the control on the form? You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

Ray said:
It worked just fine. Had not expected that to be the problem. Thanks.
In keeping with the situation I listed below, I want to filter Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue is
that
it
IsNull many times. I have tried adding something like the following to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is "A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3 queries.
Query1
generates data that is used by Query2 which which generates data
that
is
used
by Query3 which provides the data for the chart. I have a form
which
allows
me to select the date range to be used in Query1. I have placed the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date
range.
(the
form is open when I do this)

Please help
 
R

Ray

We are getting close. I get a request from Query1 for Parameter value. I have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

Duane Hookom said:
Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

Ray said:
I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank (Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank (Null)

Duane Hookom said:
What is "IsNull many times" the field or the control on the form? You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem. Thanks.
In keeping with the situation I listed below, I want to filter Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue is
that
it
IsNull many times. I have tried adding something like the following to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is "A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you must
specify the data types of your parameters. Select Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3 queries.
Query1
generates data that is used by Query2 which which generates data
that
is
used
by Query3 which provides the data for the chart. I have a form
which
allows
me to select the date range to be used in Query1. I have placed the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize '[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date
range.
(the
form is open when I do this)

Please help
 
D

Duane Hookom

I'm not sure how you can expect specific assistance when we can't see your
screen. What's the "request from Query1 for Parameter value"? What's the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

Ray said:
We are getting close. I get a request from Query1 for Parameter value. I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

Duane Hookom said:
Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

Ray said:
I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the form? You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue
is
that
it
IsNull many times. I have tried adding something like the following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you
must
specify the data types of your parameters. Select Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates data
that
is
used
by Query3 which provides the data for the chart. I have a form
which
allows
me to select the date range to be used in Query1. I have placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " "
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using
this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report
it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date
range.
(the
form is open when I do this)

Please help
 
R

Ray

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift] Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


Duane Hookom said:
I'm not sure how you can expect specific assistance when we can't see your
screen. What's the "request from Query1 for Parameter value"? What's the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

Ray said:
We are getting close. I get a request from Query1 for Parameter value. I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

Duane Hookom said:
Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the form? You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue
is
that
it
IsNull many times. I have tried adding something like the following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you
must
specify the data types of your parameters. Select Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates data
that
is
used
by Query3 which provides the data for the chart. I have a form
which
allows
me to select the date range to be used in Query1. I have placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " "
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using
this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report
it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date
range.
(the
form is open when I do this)

Please help
 
R

Ray

also, if it helps....Forms!SelectionCriteriaCharts.Shift is a combo box with
values of "A-Shift, "B-Shift" and "C-Shift" and is listed as Value List with
1 column count and 1 bound column with no default value

Ray said:
Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift] Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


Duane Hookom said:
I'm not sure how you can expect specific assistance when we can't see your
screen. What's the "request from Query1 for Parameter value"? What's the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

Ray said:
We are getting close. I get a request from Query1 for Parameter value. I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values (no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the form? You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The issue
is
that
it
IsNull many times. I have tried adding something like the following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you
must
specify the data types of your parameters. Select Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates data
that
is
used
by Query3 which provides the data for the chart. I have a form
which
allows
me to select the date range to be used in Query1. I have placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & " "
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3 using
this
criteria in the Query1, But when I try to open the report which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name or
expression.

Why would Query3 run fine, but when placed in a chart in a report
it
fails?
Also, the chart works fine if I use the following code in Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the date
range.
(the
form is open when I do this)

Please help
 
D

Duane Hookom

I'm not sure where the "Value" data type came from and the [Shift] should be
[ProducedShift]. Your form must be open with a value entered into the shift
text box. Where are the PARAMETERS declared for the date and time controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Ray said:
Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift] Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


Duane Hookom said:
I'm not sure how you can expect specific assistance when we can't see
your
screen. What's the "request from Query1 for Parameter value"? What's the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

Ray said:
We are getting close. I get a request from Query1 for Parameter value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates
data
that
is
used
by Query3 which provides the data for the chart. I have a
form
which
allows
me to select the date range to be used in Query1. I have
placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & "
"
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3
using
this
criteria in the Query1, But when I try to open the report
which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name
or
expression.

Why would Query3 run fine, but when placed in a chart in a
report
it
fails?
Also, the chart works fine if I use the following code in
Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the
date
range.
(the
form is open when I do this)

Please help
 
R

Ray

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1 worked fine
without them. I did have to enter them into the chart which used results from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for the
confusion.

The whole point to this is to have the Query run if there is no value
entered into the [Shift] control on the form. I am trying to create the same
effect by entering the code directly into the criteria section of the Query
as I get from using the following code to create stLinkCriteria. I want to
have the criteria ignore no value entered into the forms control. It would be
easy if there was always a value entered into the control. No value is the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] & "#" & _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


Duane Hookom said:
I'm not sure where the "Value" data type came from and the [Shift] should be
[ProducedShift]. Your form must be open with a value entered into the shift
text box. Where are the PARAMETERS declared for the date and time controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Ray said:
Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift] Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift, MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


Duane Hookom said:
I'm not sure how you can expect specific assistance when we can't see
your
screen. What's the "request from Query1 for Parameter value"? What's the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for Parameter value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so, you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates
data
that
is
used
by Query3 which provides the data for the chart. I have a
form
which
allows
me to select the date range to be used in Query1. I have
placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate] & "
"
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3
using
this
criteria in the Query1, But when I try to open the report
which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field name
or
expression.

Why would Query3 run fine, but when placed in a chart in a
report
it
fails?
Also, the chart works fine if I use the following code in
Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the
date
range.
(the
form is open when I do this)

Please help
 
D

Duane Hookom

Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form value.
If the form control value is null, then compare the field value to itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

Ray said:
I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1 worked
fine
without them. I did have to enter them into the chart which used results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for the
confusion.

The whole point to this is to have the Query run if there is no value
entered into the [Shift] control on the form. I am trying to create the
same
effect by entering the code directly into the criteria section of the
Query
as I get from using the following code to create stLinkCriteria. I want to
have the criteria ignore no value entered into the forms control. It would
be
easy if there was always a value entered into the control. No value is the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] & "#" & _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


Duane Hookom said:
I'm not sure where the "Value" data type came from and the [Shift] should
be
[ProducedShift]. Your form must be open with a value entered into the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Ray said:
Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we can't see
your
screen. What's the "request from Query1 for Parameter value"? What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it
produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in
Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so,
you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates
data
that
is
used
by Query3 which provides the data for the chart. I have a
form
which
allows
me to select the date range to be used in Query1. I have
placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate]
& "
"
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3
using
this
criteria in the Query1, But when I try to open the report
which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field
name
or
expression.

Why would Query3 run fine, but when placed in a chart in a
report
it
fails?
Also, the chart works fine if I use the following code in
Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the
date
range.
(the
form is open when I do this)

Please help
 
R

Ray

No. It didnt work. Yes. I do understand that. I just didn't know of it before.

I copied and pasted it directly into the criteria of ProducedShift column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting frustrating.

Duane Hookom said:
Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form value.
If the form control value is null, then compare the field value to itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

Ray said:
I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1 worked
fine
without them. I did have to enter them into the chart which used results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for the
confusion.

The whole point to this is to have the Query run if there is no value
entered into the [Shift] control on the form. I am trying to create the
same
effect by entering the code directly into the criteria section of the
Query
as I get from using the following code to create stLinkCriteria. I want to
have the criteria ignore no value entered into the forms control. It would
be
easy if there was always a value entered into the control. No value is the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] & "#" & _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


Duane Hookom said:
I'm not sure where the "Value" data type came from and the [Shift] should
be
[ProducedShift]. Your form must be open with a value entered into the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we can't see
your
screen. What's the "request from Query1 for Parameter value"? What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is blank
(Null)

:

What is "IsNull many times" the field or the control on the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the problem.
Thanks.
In keeping with the situation I listed below, I want to filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift]. The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull [Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift] is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it
produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in
Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If so,
you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results of 3
queries.
Query1
generates data that is used by Query2 which which generates
data
that
is
used
by Query3 which provides the data for the chart. I have a
form
which
allows
me to select the date range to be used in Query1. I have
placed
the
following
to set the date criteria in Query1.....

Between [Forms]![SelectionCriteriaCharts].[StartDate]
& "
"
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of Query3
using
this
criteria in the Query1, But when I try to open the report
which
contains
the
chart based upon Query3 I get the following error message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field
name
or
expression.

Why would Query3 run fine, but when placed in a chart in a
report
it
fails?
Also, the chart works fine if I use the following code in
Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate the
date
range.
(the
 
D

Duane Hookom

If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the criteria,
do you get results? Try replace the "." with "!". What do you get if you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and paste
it as a field?

--
Duane Hookom
MS Access MVP

Ray said:
No. It didnt work. Yes. I do understand that. I just didn't know of it
before.

I copied and pasted it directly into the criteria of ProducedShift column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting frustrating.

Duane Hookom said:
Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

Ray said:
I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1 worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for the
confusion.

The whole point to this is to have the Query run if there is no value
entered into the [Shift] control on the form. I am trying to create the
same
effect by entering the code directly into the criteria section of the
Query
as I get from using the following code to create stLinkCriteria. I want
to
have the criteria ignore no value entered into the forms control. It
would
be
easy if there was always a value entered into the control. No value is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] & "#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the [Shift]
should
be
[ProducedShift]. Your form must be open with a value entered into the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is
blank
(Null)

:

What is "IsNull many times" the field or the control on the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the
problem.
Thanks.
In keeping with the situation I listed below, I want to
filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift].
The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull
[Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift]
is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it
produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in
Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If
so,
you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results
of 3
queries.
Query1
generates data that is used by Query2 which which
generates
data
that
is
used
by Query3 which provides the data for the chart. I have
a
form
which
allows
me to select the date range to be used in Query1. I have
placed
the
following
to set the date criteria in Query1.....

Between
[Forms]![SelectionCriteriaCharts].[StartDate]
& "
"
&
[Forms]!
[SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]

All works fine when I am looking at the results of
Query3
using
this
criteria in the Query1, But when I try to open the
report
which
contains
the
chart based upon Query3 I get the following error
message:

The Microsoft Jet database engine does not recognize
'[Forms]!
[SelectionCriteriaCharts].[StartDate]' as a valid field
name
or
expression.

Why would Query3 run fine, but when placed in a chart in
a
report
it
fails?
Also, the chart works fine if I use the following code
in
Query1

Between #1/1/2006 06:00:00# and #6/1/2006 06:00:00#

It just fails when I refer to the Form used to generate
the
date
range.
(the
 
R

Ray

Tried all. Got all records when did first suggestion. No change with using
"!". New field and nothing in criteria yielded all records and no value in
new field. Put criteria back and got all records whth value in new field. No
records if Form's Shift control was Null.

Is there any way of applying selection criteria to Query1 when it opens to
create the data for the Graph in the Report???? We have a selection criteria
code that works.

Duane Hookom said:
If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the criteria,
do you get results? Try replace the "." with "!". What do you get if you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and paste
it as a field?

--
Duane Hookom
MS Access MVP

Ray said:
No. It didnt work. Yes. I do understand that. I just didn't know of it
before.

I copied and pasted it directly into the criteria of ProducedShift column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting frustrating.

Duane Hookom said:
Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1 worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for the
confusion.

The whole point to this is to have the Query run if there is no value
entered into the [Shift] control on the form. I am trying to create the
same
effect by entering the code directly into the criteria section of the
Query
as I get from using the following code to create stLinkCriteria. I want
to
have the criteria ignore no value entered into the forms control. It
would
be
easy if there was always a value entered into the control. No value is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] & "#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the [Shift]
should
be
[ProducedShift]. Your form must be open with a value entered into the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value, [Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is
blank
(Null)

:

What is "IsNull many times" the field or the control on the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the
problem.
Thanks.
In keeping with the situation I listed below, I want to
filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift].
The
issue
is
that
it
IsNull many times. I have tried adding something like the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull
[Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of [Forms]![SelectionCriteriaCharts].[Shift]
is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it
produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in
Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query. If
so,
you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate] DateTime
[Forms]![SelectionCriteriaCharts].[StartTime] DateTime
[Forms]![SelectionCriteriaCharts].[EndDate] DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the results
of 3
queries.
Query1
generates data that is used by Query2 which which
generates
data
that
is
used
by Query3 which provides the data for the chart. I have
a
form
 
D

Duane Hookom

Set no value in the combo box and open the debug window (Press Ctrl+G) and
enter
? [Forms]![SelectionCriteriaCharts]![Shift]
and then
? IsNull([Forms]![SelectionCriteriaCharts]![Shift])

what do you get from these?

--
Duane Hookom
MS Access MVP

Ray said:
Tried all. Got all records when did first suggestion. No change with using
"!". New field and nothing in criteria yielded all records and no value in
new field. Put criteria back and got all records whth value in new field.
No
records if Form's Shift control was Null.

Is there any way of applying selection criteria to Query1 when it opens to
create the data for the Graph in the Report???? We have a selection
criteria
code that works.

Duane Hookom said:
If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the
criteria,
do you get results? Try replace the "." with "!". What do you get if you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and
paste
it as a field?

--
Duane Hookom
MS Access MVP

Ray said:
No. It didnt work. Yes. I do understand that. I just didn't know of it
before.

I copied and pasted it directly into the criteria of ProducedShift
column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting frustrating.

:

Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1
worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for
the
confusion.

The whole point to this is to have the Query run if there is no
value
entered into the [Shift] control on the form. I am trying to create
the
same
effect by entering the code directly into the criteria section of
the
Query
as I get from using the following code to create stLinkCriteria. I
want
to
have the criteria ignore no value entered into the forms control. It
would
be
easy if there was always a value entered into the control. No value
is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] &
"#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the [Shift]
should
be
[ProducedShift]. Your form must be open with a value entered into
the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value,
[Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we
can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for
Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is
often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is
blank
(Null)

:

What is "IsNull many times" the field or the control on
the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the
problem.
Thanks.
In keeping with the situation I listed below, I want to
filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift].
The
issue
is
that
it
IsNull many times. I have tried adding something like
the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull
[Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of
[Forms]![SelectionCriteriaCharts].[Shift]
is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
[Forms]![SelectionCriteriaCharts].[Shift] is blank, it
produces
no
result.
Am
I missing something. (By the way, I have added
[Forms]![SelectionCriteriaCharts].[Shift] as Text in
Parameters
to
avoid
the
first issue.)

:

Your chart's Row Source is probably a Crosstab query.
If
so,
you
must
specify the data types of your parameters. Select
Query->Parameters
and
enter:
[Forms]![SelectionCriteriaCharts].[StartDate]
DateTime
[Forms]![SelectionCriteriaCharts].[StartTime]
DateTime
[Forms]![SelectionCriteriaCharts].[EndDate]
DateTime
[Forms]![SelectionCriteriaCharts].[EndTime] DateTime
--
Duane Hookom
MS Access MVP



I have a chart on a report that is based on the
results
of 3
queries.
Query1
generates data that is used by Query2 which which
generates
data
that
is
used
by Query3 which provides the data for the chart. I
have
a
form
 
R

Ray

Null and Yes.

Duane Hookom said:
Set no value in the combo box and open the debug window (Press Ctrl+G) and
enter
? [Forms]![SelectionCriteriaCharts]![Shift]
and then
? IsNull([Forms]![SelectionCriteriaCharts]![Shift])

what do you get from these?

--
Duane Hookom
MS Access MVP

Ray said:
Tried all. Got all records when did first suggestion. No change with using
"!". New field and nothing in criteria yielded all records and no value in
new field. Put criteria back and got all records whth value in new field.
No
records if Form's Shift control was Null.

Is there any way of applying selection criteria to Query1 when it opens to
create the data for the Graph in the Report???? We have a selection
criteria
code that works.

Duane Hookom said:
If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the
criteria,
do you get results? Try replace the "." with "!". What do you get if you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and
paste
it as a field?

--
Duane Hookom
MS Access MVP

No. It didnt work. Yes. I do understand that. I just didn't know of it
before.

I copied and pasted it directly into the criteria of ProducedShift
column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting frustrating.

:

Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1
worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for
the
confusion.

The whole point to this is to have the Query run if there is no
value
entered into the [Shift] control on the form. I am trying to create
the
same
effect by entering the code directly into the criteria section of
the
Query
as I get from using the following code to create stLinkCriteria. I
want
to
have the criteria ignore no value entered into the forms control. It
would
be
easy if there was always a value entered into the control. No value
is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime] &
"#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the [Shift]
should
be
[ProducedShift]. Your form must be open with a value entered into
the
shift
text box. Where are the PARAMETERS declared for the date and time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value,
[Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we
can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for
Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is
often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift] is
blank
(Null)

:

What is "IsNull many times" the field or the control on
the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the
problem.
Thanks.
In keeping with the situation I listed below, I want to
filter
Query1
using
the value in [Forms]![SelectionCriteriaCharts].[Shift].
The
issue
is
that
it
IsNull many times. I have tried adding something like
the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull
[Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of
[Forms]![SelectionCriteriaCharts].[Shift]
is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
 
D

Duane Hookom

Do you actually have a field named "ProducedShift"? What happens if you just
put a criteria under the ProducedShift field of
[ProducedShift]

If that doesn't work please post back with your query's full SQL view.
--
Duane Hookom
MS Access MVP



Ray said:
Null and Yes.

Duane Hookom said:
Set no value in the combo box and open the debug window (Press Ctrl+G)
and
enter
? [Forms]![SelectionCriteriaCharts]![Shift]
and then
? IsNull([Forms]![SelectionCriteriaCharts]![Shift])

what do you get from these?

--
Duane Hookom
MS Access MVP

Ray said:
Tried all. Got all records when did first suggestion. No change with
using
"!". New field and nothing in criteria yielded all records and no value
in
new field. Put criteria back and got all records whth value in new
field.
No
records if Form's Shift control was Null.

Is there any way of applying selection criteria to Query1 when it opens
to
create the data for the Graph in the Report???? We have a selection
criteria
code that works.

:

If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the
criteria,
do you get results? Try replace the "." with "!". What do you get if
you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and
paste
it as a field?

--
Duane Hookom
MS Access MVP

No. It didnt work. Yes. I do understand that. I just didn't know of
it
before.

I copied and pasted it directly into the criteria of ProducedShift
column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting
frustrating.

:

Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the
form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1
worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for
the
confusion.

The whole point to this is to have the Query run if there is no
value
entered into the [Shift] control on the form. I am trying to
create
the
same
effect by entering the code directly into the criteria section of
the
Query
as I get from using the following code to create stLinkCriteria.
I
want
to
have the criteria ignore no value entered into the forms control.
It
would
be
easy if there was always a value entered into the control. No
value
is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime]
&
"#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] &
"'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the
[Shift]
should
be
[ProducedShift]. Your form must be open with a value entered
into
the
shift
text box. Where are the PARAMETERS declared for the date and
time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text (
255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value,
[Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we
can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for
Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is
often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return
all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift]
is
blank
(Null)

:

What is "IsNull many times" the field or the control on
the
form?
You
might
be able to use something like:

WHERE Nz([Shift],"NA") =
Nz([Forms]![SelectionCriteriaCharts].[Shift],"NA")

--
Duane Hookom
MS Access MVP

It worked just fine. Had not expected that to be the
problem.
Thanks.
In keeping with the situation I listed below, I want
to
filter
Query1
using
the value in
[Forms]![SelectionCriteriaCharts].[Shift].
The
issue
is
that
it
IsNull many times. I have tried adding something like
the
following
to
the
criteria of Shift in Query1.

IIf (Not "IsNull
[Forms]![SelectionCriteriaCharts].[Shift]",
[Forms]![SelectionCriteriaCharts].[Shift], "IsNull")

If the Value of
[Forms]![SelectionCriteriaCharts].[Shift]
is
"A-Shift"
or
"B-Shift" it filters correctly, but, if
 
R

Ray

I see where you are going....
I put [ProducedShift] in Query1. Got all records, which is what I want.
Here is the current SQL...

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text ( 255 );
SELECT MainTable.UpdateDate, MainTable.QuantConf, [ActLabor]*[RunTime] AS
DLH, MainTable.ProducedShift
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON Lines.LineID =
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=[ProducedShift]) AND ((Lines.LineNum)="4303" Or
(Lines.LineNum)="4304" Or (Lines.LineNum)="4305" Or (Lines.LineNum)="4306")
AND ((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Duane Hookom said:
Do you actually have a field named "ProducedShift"? What happens if you just
put a criteria under the ProducedShift field of
[ProducedShift]

If that doesn't work please post back with your query's full SQL view.
--
Duane Hookom
MS Access MVP



Ray said:
Null and Yes.

Duane Hookom said:
Set no value in the combo box and open the debug window (Press Ctrl+G)
and
enter
? [Forms]![SelectionCriteriaCharts]![Shift]
and then
? IsNull([Forms]![SelectionCriteriaCharts]![Shift])

what do you get from these?

--
Duane Hookom
MS Access MVP

Tried all. Got all records when did first suggestion. No change with
using
"!". New field and nothing in criteria yielded all records and no value
in
new field. Put criteria back and got all records whth value in new
field.
No
records if Form's Shift control was Null.

Is there any way of applying selection criteria to Query1 when it opens
to
create the data for the Graph in the Report???? We have a selection
criteria
code that works.

:

If you remove [Forms]![SelectionCriteriaCharts].[Shift] from the
criteria,
do you get results? Try replace the "." with "!". What do you get if
you
remove [Forms]![SelectionCriteriaCharts]![Shift] from the criteria and
paste
it as a field?

--
Duane Hookom
MS Access MVP

No. It didnt work. Yes. I do understand that. I just didn't know of
it
before.

I copied and pasted it directly into the criteria of ProducedShift
column.
After I ran it, it created a new column "ProducedShift" with
Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift]) as the
criteria. I got no results whether or not I had a value in
[Forms]![SelectionCriteriaCharts].[Shift]

I really do appreciate your help and am sure it is getting
frustrating.

:

Did it work to use
ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
This expression basically says to compare ProducedShift with the
form
value.
If the form control value is null, then compare the field value to
itself.
Do you understand that?

--
Duane Hookom
MS Access MVP

I was just trying to see if Value would work since Text didn't.

I didnt declare the values for date/time controls because Query1
worked
fine
without them. I did have to enter them into the chart which used
results
from
Query1.

I have made the change to [ProducedShift] as u suggest. Sorry for
the
confusion.

The whole point to this is to have the Query run if there is no
value
entered into the [Shift] control on the form. I am trying to
create
the
same
effect by entering the code directly into the criteria section of
the
Query
as I get from using the following code to create stLinkCriteria.
I
want
to
have the criteria ignore no value entered into the forms control.
It
would
be
easy if there was always a value entered into the control. No
value
is
the
problem. Here is what I use to get around Null values when I use
stLinkCriteria on other things.

vStr = "[UpdateDate]>#" & Me![StartDate] & " " & Me![StartTime]
&
"#"
& _
" And " & "[UpdateDate]<#" & Me![EndDate] & " " &
Me![EndTime] & "#"

' *** TEXT ***
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] &
"'"
End If

' *** NUMBER ***
If Not IsNull(Me![TeamLeaders]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If


:

I'm not sure where the "Value" data type came from and the
[Shift]
should
be
[ProducedShift]. Your form must be open with a value entered
into
the
shift
text box. Where are the PARAMETERS declared for the date and
time
controls?

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Text (
255 );
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE
MainTable.UpdateDate Between
[Forms]![SelectionCriteriaCharts].[StartDate] +
[Forms]![SelectionCriteriaCharts].[StartTime]
And
[Forms]![SelectionCriteriaCharts].[EndDate] +
[Forms]![SelectionCriteriaCharts].[EndTime]
AND

ProducedShift=Nz([Forms]![SelectionCriteriaCharts].[Shift],[ProducedShift])
AND
Lines.LineNum IN("4303","4304" ,"4305", "4306")
AND
Mid([RoutingNum],3,2)="01"
ORDER BY MainTable.UpdateDate;

--
Duane Hookom
MS Access MVP

Full sql:

PARAMETERS [Forms]![SelectionCriteriaCharts].[Shift] Value,
[Shift]
Value;
SELECT MainTable.UpdateDate, MainTable.ProducedShift,
MainTable.QuantConf,
[ActLabor]*[RunTime] AS DLH
FROM Routing INNER JOIN (Lines INNER JOIN MainTable ON
Lines.LineID
=
MainTable.LineID) ON Routing.RoutingID = MainTable.RoutingID
WHERE (((MainTable.UpdateDate) Between
[Forms]![SelectionCriteriaCharts].[StartDate] & " " &
[Forms]![SelectionCriteriaCharts].[StartTime] And
[Forms]![SelectionCriteriaCharts].[EndDate] & " " &
[Forms]![SelectionCriteriaCharts].[EndTime]) AND
((MainTable.ProducedShift)=Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift]))
AND ((Lines.LineNum)="4303" Or (Lines.LineNum)="4304" Or
(Lines.LineNum)="4305" Or (Lines.LineNum)="4306") AND
((Mid([RoutingNum],3,2))="01"))
ORDER BY MainTable.UpdateDate;

Parameter request is as follows:
Enter Parameter Value
Forms!SelectionCriteriaCharts.Shift

Have tried setting parameters to both text and value

Sorry to be so difficult...doing the best I can


:

I'm not sure how you can expect specific assistance when we
can't
see
your
screen. What's the "request from Query1 for Parameter value"?
What's
the
full SQL view of your query?
--
Duane Hookom
MS Access MVP

We are getting close. I get a request from Query1 for
Parameter
value.
I
have
tried entering the following into Query->Parameters

1) [Forms]![SelectionCriteriaCharts].[Shift] and
2) Nz([Forms]![SelectionCriteriaCharts].[Shift]) and
3) [Shift]

All listed as Text.

No luck with any of the 3 attempts

:

Set the criteria to
Nz([Forms]![SelectionCriteriaCharts].[Shift],[Shift])

--
Duane Hookom
MS Access MVP

I am sorry, [Forms]![SelectionCriteriaCharts].[Shift] is
often
blank
(Null)
I want Query1 to filter for any non Null value in
[Forms]![SelectionCriteriaCharts].[Shift] , but return
all
values
(no
filtering) if [Forms]![SelectionCriteriaCharts].[Shift]
is
blank
(Null)

:

What is "IsNull many times" the field or the control on
the
form?
You
might
be able to use something like:
 

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