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
