G
Guest
I got it!!!!!! Had it long ago. Just had values reversed.
IIf(IsNull([Forms]![SelectionCriteriaCharts].[Shift]),[ProducedShift],[Forms]![SelectionCriteriaCharts].[Shift])
Seems so obvious now. Sorry to have taken so much of your time. I did learn
a lot of new stuff like the Nz function. Glad you tried so hard to help.
IIf(IsNull([Forms]![SelectionCriteriaCharts].[Shift]),[ProducedShift],[Forms]![SelectionCriteriaCharts].[Shift])
Seems so obvious now. Sorry to have taken so much of your time. I did learn
a lot of new stuff like the Nz function. Glad you tried so hard to help.
Ray said: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.
:
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