Query requesting parameter value twice

[tbrogdon]

I have a SELECT query:
PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
SELECT Employees.EmployeeID, [ProductionDate] AS Expr1,
Employees.Department, Employees.Shift, Shift.ShiftHours
FROM Shift INNER JOIN (Department INNER JOIN Employees ON
Department.Department=Employees.Department) ON
Shift.Shift=Employees.Shift
WHERE ((([ProductionDate])=forms!frmSetEmpHours!txtDate) And
((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

I have frmSetEmpHours w/
txtProductionDate: unbound text field - where user enters date
cboDepartment: bound
cboShift: bound

I want to pull all a recordset of all Employees from a specific
department/shift combination and add a date field on the fly to all
the entries.

I thought that I could use the unbound txtDate from the form to
capture the date input and funnel that into Expr1 to accomplish this.
Well it works...sort of.

When I fill out the txtDate, cboDept, and cboShift and click the
cmdRunQuery button Access pops up a parameter dialog box
"EnterParameter Value" caption: ProductionDate instead of taking the
value from frmSetEmpHours.txtDate. If I fill in this parameter box and
click OK it returns the dataset that I expected.

How can I get the query to get the date value from the form rather
than popping up an extra dialog box?

Thanks,

Tim

 

[Rick Brandt]

I have a SELECT query:
PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
SELECT Employees.EmployeeID, [ProductionDate] AS Expr1,
Employees.Department, Employees.Shift, Shift.ShiftHours
FROM Shift INNER JOIN (Department INNER JOIN Employees ON
Department.Department=Employees.Department) ON
Shift.Shift=Employees.Shift
WHERE ((([ProductionDate])=forms!frmSetEmpHours!txtDate) And
((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

I have frmSetEmpHours w/
txtProductionDate: unbound text field - where user enters date
cboDepartment: bound
cboShift: bound

I want to pull all a recordset of all Employees from a specific
department/shift combination and add a date field on the fly to all
the entries.

I thought that I could use the unbound txtDate from the form to
capture the date input and funnel that into Expr1 to accomplish this.
Well it works...sort of.

When I fill out the txtDate, cboDept, and cboShift and click the
cmdRunQuery button Access pops up a parameter dialog box
"EnterParameter Value" caption: ProductionDate instead of taking the
value from frmSetEmpHours.txtDate. If I fill in this parameter box and
click OK it returns the dataset that I expected.

How can I get the query to get the date value from the form rather
than popping up an extra dialog box?

Thanks,

Tim

Means the form reference is incorrect. Check your spelling.

 

[tbrogdon]

Means the form reference is incorrect. Check your spelling.

Hi Rick,

That's what I thought as well but I've checked it several times. Could
it possibly be anything else?

Thanks,

Tim

 

[Rick Brandt]

Hi Rick,

That's what I thought as well but I've checked it several times. Could
it possibly be anything else?

Anything's possible I guess, but I can't recall any other reason to get that
behavior.

 

[tbrogdon]

I've been staring till I'm blind at this thing...but I think I'll just
retype the SQL and see if I find it that way. I've tried copying and
pasting the references instead of typing them but it just doesn't seem
to help. Anyway, thank you and I believe you're right...I just can't
find the bugger.

Thanks,

Tim

 

[tbrogdon]

Rick,

I've re-checked my sql again and can't find the typo.

I input txtProdDate, cboDept, and cboShift into frmSetEmpHours and
then click my cmdRunQuery button. Is it possible that I need to pass
the value from txtDate to a variable and run the query from VBA? I'm
just grasping at straws now - I know.

Thanks,

Tim

 

[Douglas J. Steele]

If the prompt is for [ProductionDate], that implies that you don't have a
field named ProductionDate in any of the tables that in the query. The fact
that the reference to ProductionDate in the SQL comes up as Expr1 reinforces
that.

If what you're trying to do is use the value of txtDate in your query, you
want

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate As
[ProductionDate],
Employees.Department, Employees.Shift, Shift.ShiftHours
FROM Shift INNER JOIN (Department INNER JOIN Employees ON
Department.Department=Employees.Department) ON
Shift.Shift=Employees.Shift
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

 

[tbrogdon]

Thank you Doug!

That got it. And now, by looking at the QBE, I have a solid reference
for the future.

Thanks again,

Tim