Complex (for me) IIf query

After reading more of the help files, I'm wondering if I need to nest
my IIf inside of a SELECT as a subquery in the design grid?

The SQL you showed earlier should work.

Try running just the SELECT portion to see what's returned:


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
...
After reading more of the help files, I'm wondering if I need to nest
my IIf inside of a SELECT as a subquery in the design grid?

Hi Doug,

Thanks for staying with me. I think we're getting closer.

I used the following SELECT query with some success:

SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobF unctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

Notice I did qualify JobFunctionID and Shift and changed the numeric
value in the second IIf. It does just what I need.

So how can I place this into my Append query? I tried it and it
returns 0 records. No error message. Just 0 records. Here is the
Append query sql:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobF unctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
...
Hi Doug,

Thanks for staying with me. I think we're getting closer.

I used the following SELECT query with some success:

SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobF unctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

Notice I did qualify JobFunctionID and Shift and changed the numeric
value in the second IIf. It does just what I need.

So how can I place this into my Append query? I tried it and it
returns 0 records. No error message. Just 0 records. Here is the
Append query sql:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf([EmployeeProduction].
[Shift]=3,IIf(EmployeeProduction!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobF unctionID=1,8,0)) AS
HoursMachine, IIf([EmployeeProduction].
[Shift]=3,IIf([EmployeeProduction].
[JobFunctionID]=2,7.5,0),IIf([EmployeeProduction].
[JobFunctionID]=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID = EmployeeProduction.EmployeeID) ON (Shift.Shift
= Employees.Shift) AND (Shift.Shift = EmployeeProduction.Shift)
WHERE (((Employees.Department)=[forms]![frmSetEmpHours]![cboDept]) AND
((Employees.Shift)=[forms]![frmSetEmpHours]![cboShift]));

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?


Hi Doug,

I am actually using the parameter and I am doing it from a form. What
I am trying to accomplish is to allow each shift supervisor to "batch"
populate tblEmployeeProduction from frmSetEmpHours. Each shift/dept
combination always has the same people theoretically working the same
amount of hours in the same job function - theoretically.

frmSetEmpHours lets the supervisor enter the date in an unbound text
field [txtDate] and then select the department and shift from combo
boxes. Then the supervisor clicks a command button which runs the sql
in my very first post - which happens to be an append query. This adds
a new record for every employee for that shift and department in
tblEmployeeProduction with the input date including the employee's
name and JobFunctionID. All of this works so far. Each employee has a
"default" job function but can act in either capacity during a shift
which leads us to my current question.

I want to run an append query (above) that also has 2 calculated
fields in it. Like I said, each employee can act in 1 or 2 or both job
functions during a shift and I need to track the number of hours spent
by an employee in each. Since each employee has "default" job function
I would like my append query to also place the corresponding "default"
number of shift hours in either [HoursMachine] or [HoursAssembly]. If
necessary those hours can be changed later by the supervisor if an
employee moves from one job function to another.

The original append query works well by itself and the query you have
helped me design works also as a secondary query to the first (i.e.,
if I run the append query first and then run the query containing the
IIf it does what I need it to do - just in an extra step).

Is it possible to merge them into one query or not? Will Access allow
the IIf calculation on JobFunctionID and Shift at the same time it is
doing an append? Or is it possible to run the append query in VBA and
include the IIf?

The VBA attached to the command button is simply:
Private Sub cmdFillRecords_Click()
On Error GoTo Err_cmdFillRecords_Click

Dim stDocName As String

stDocName = "qryEmpShiftHours"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdFillRecords_Click:
Exit Sub

Err_cmdFillRecords_Click:
MsgBox Err.Description
Resume Exit_cmdFillRecords_Click

End Sub

wrote in message
...

As far as I can tell, you're not using the parameter
forms![frmSetEmpHours]![txtDate] anywhere. Try removing it and see
whether
that makes a difference.

How are you running the query? Just by double-clicking on it, or through
VBA? If through VBA, what's the code?


Hi Doug,

Is it possible to merge them into one query or not? Will Access allow
the IIf calculation on JobFunctionID and Shift at the same time it is
doing an append? Or is it possible to run the append query in VBA and
include the IIf?

It should work.


See whether this works any better:

Private Sub cmdFillRecords_Click()
On Error GoTo Err_cmdFillRecords_Click

CurrentDb.QueryDefs("qryEmpShiftHours").Execute dbFailOnError

Exit_cmdFillRecords_Click:
Exit Sub

Err_cmdFillRecords_Click:
MsgBox Err.Description
Resume Exit_cmdFillRecords_Click

End Sub

If you get an error about dbFailOnError not being declared, make sure you
have a reference set to Microsoft DAO 3.6 Object Library (You do this
through Tools | References while in the VB Editor)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Hi Doug,

I tried your code verbatim but I received the following dialog box:
"Too few parameters. Expected 3." Depending on how you look at it,
frmSetEmpHours does request and qryEmpShiftHours does utilize 3
parameters. The first is txtDate (unbound) stored as ProductionDate;
cboDept (como box w/ row source SELECT Department.Department FROM
Department) and cboShift (combo box w/ row source SELECT Shift.Shift
FROM Shift).

I tried the following query:
PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction!JobFunctionID=1,7.5,0), IIf(EmployeeProduction!
JobFunctionID=1,8,0)) AS HoursMachine, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction.JobFunctionID=2,7.5,0),
IIf(EmployeeProduction.JobFunctionID=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID=EmployeeProduction.EmployeeID ) ON
(Shift.Shift=Employees.Shift) AND
(Shift.Shift=EmployeeProduction.Shift)
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboD ept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift)) ;

Notice that I do use [txtDate] AS ProductionDate in the SELECT
statement.

I also tried my original query (1st post) and received the same dialog
box "Too few parameters..."

Is there a way for me to check how many parameters it is receiving if
any and which ones? Private Sub cmdFillRecords_Click()

If you use

DoCmd.OpenQuery "qryEmpShiftHours"

instead of

CurrentDb.Execute "qryEmpShiftHours"

that should solve the parameter problem with FORMS!formName!ControlName.
Indeed, DoCmd solves the FORMS!formName!ControlName parameters for you, but
CurrentDb does NOT do it automatically, for you.


(you can also use DoCmd.RunSQL instead of DoCmd.OpenQuery, if more
appropriate, given the situation).



Vanderghast, Access MVP


wrote in message
...
Hi Doug,

I tried your code verbatim but I received the following dialog box:
"Too few parameters. Expected 3." Depending on how you look at it,
frmSetEmpHours does request and qryEmpShiftHours does utilize 3
parameters. The first is txtDate (unbound) stored as ProductionDate;
cboDept (como box w/ row source SELECT Department.Department FROM
Department) and cboShift (combo box w/ row source SELECT Shift.Shift
FROM Shift).

I tried the following query:
PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction!JobFunctionID=1,7.5,0), IIf(EmployeeProduction!
JobFunctionID=1,8,0)) AS HoursMachine, IIf(EmployeeProduction.Shift=3,
IIf(EmployeeProduction.JobFunctionID=2,7.5,0),
IIf(EmployeeProduction.JobFunctionID=2,8,0)) AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID=EmployeeProduction.EmployeeID ) ON
(Shift.Shift=Employees.Shift) AND
(Shift.Shift=EmployeeProduction.Shift)
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboD ept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift)) ;

Notice that I do use [txtDate] AS ProductionDate in the SELECT
statement.

I also tried my original query (1st post) and received the same dialog
box "Too few parameters..."

Is there a way for me to check how many parameters it is receiving if
any and which ones? Private Sub cmdFillRecords_Click()

Hi DOug and Mike,

I tried a modifcation of your code:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "qryEmpShiftHours"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

with this query:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, HoursAssembly )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID,
IIf(EmployeeProduction.Shift=3,IIf(EmployeeProduct ion!
JobFunctionID=1,7.5,0),IIf(EmployeeProduction!JobF unctionID=1,8,0)) AS
HoursMachine,
IIf(EmployeeProduction.Shift=3,IIf(EmployeeProduct ion.JobFunctionID=2,7.5,0),IIf(EmployeeProduction. JobFunctionID=2,8,0))
AS HoursAssembly
FROM Shift INNER JOIN (Employees INNER JOIN EmployeeProduction ON
Employees.EmployeeID=EmployeeProduction.EmployeeID ) ON
(Shift.Shift=Employees.Shift) AND
(Shift.Shift=EmployeeProduction.Shift)
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboD ept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift)) ;


and it works great! I've only run it a couple fo times but it looks
like it is going to work!

Thank you both very much for your help.

Now more testing! :-)

Tim

Well,

I guess I spoke too soon. Using the above code and query with an EMPTY
tblEmployeeProduction I receive no error messages but it runs the
append query and returns 0 records.

When I ran it before with success tblEmployeeProduction actually had
data in it and I suspect that the query was repeating records from
tblEmployeeProduction rather than querying the Employees table.

The reason I think this is that, selecting a date, specific department
and shift appended far too many records. For instance, Dept 1, 3rd
Shift has 18 employees and the query appended 54 on repeated attempts.

Any thoughts?

Thanks,

Tim