SQL parameter query not returning values

I have an unbound form frmProdReview with an OK command button set to
OpenQuery and a Cancel command button. On the form I have 3 controls
txtDate, cboShift, and cboDept.

When I click the "OK" button the query runs. It returns a datasheet
that contains no data only the field names I have requested. Below is
the SQL. Any ideas?

Thanks,

Tim

PARAMETERS forms![frmProdReview]![txtDate] Short, forms!
[frmProdReview]![cboShift] Text ( 255 ), forms![frmProdReview]!
[cboDept] Text ( 255 );
SELECT DISTINCTROW Production.ProductionDate, Department.Department,
Shift.Shift, Workstation.WorkstationName, Parts.PartID,
ProductOperation.Operator1, ProductOperation.Operator2,
ProductOperation.QuantityRun
FROM Shift INNER JOIN (((Department INNER JOIN Production ON
Department.Department=Production.Department) INNER JOIN Workstation ON
Department.Department=Workstation.Department) INNER JOIN (Parts INNER
JOIN ProductOperation ON Parts.PartID=ProductOperation.PartID) ON
(Workstation.WorkstationID=ProductOperation.WorkstationID) AND
(Production.ProductionID=ProductOperation.ProductionID)) ON
Shift.Shift=Production.Shift
WHERE (((Production.ProductionDate)=Forms!frmProdReview!txtDate));

Re: SQL parameter query not returning values

tbrogdon@gmail.com wrote:
> I have an unbound form frmProdReview with an OK command button set to
> OpenQuery and a Cancel command button. On the form I have 3 controls
> txtDate, cboShift, and cboDept.
>
> When I click the "OK" button the query runs. It returns a datasheet
> that contains no data only the field names I have requested. Below is
> the SQL. Any ideas?
>
> Thanks,
>
> Tim
>
> PARAMETERS forms![frmProdReview]![txtDate] Short, forms!
> [frmProdReview]![cboShift] Text ( 255 ), forms![frmProdReview]!
> [cboDept] Text ( 255 );
> SELECT DISTINCTROW Production.ProductionDate, Department.Department,
> Shift.Shift, Workstation.WorkstationName, Parts.PartID,
> ProductOperation.Operator1, ProductOperation.Operator2,
> ProductOperation.QuantityRun
> FROM Shift INNER JOIN (((Department INNER JOIN Production ON
> Department.Department=Production.Department) INNER JOIN Workstation ON
> Department.Department=Workstation.Department) INNER JOIN (Parts INNER
> JOIN ProductOperation ON Parts.PartID=ProductOperation.PartID) ON
> (Workstation.WorkstationID=ProductOperation.WorkstationID) AND
> (Production.ProductionID=ProductOperation.ProductionID)) ON
> Shift.Shift=Production.Shift
> WHERE (((Production.ProductionDate)=Forms!frmProdReview!txtDate));

Is Production.ProductionDate a date value?

Date fields are surrounded by #
"Production.ProductionDate=#" & Forms!frmProdReview!txtDate & "#"

Re: SQL parameter query not returning values

On 29 nov, 17:39, tbrog...@gmail.com wrote:
> I have an unbound form frmProdReview with an OK command button set to
> OpenQuery and a Cancel command button. On the form I have 3 controls
> txtDate, cboShift, and cboDept.
>
> When I click the "OK" button the query runs. It returns a datasheet
> that contains no data only the field names I have requested. Below is
> the SQL. Any ideas?
>
> Thanks,
>
> Tim
>
> PARAMETERS forms![frmProdReview]![txtDate] Short, forms!
> [frmProdReview]![cboShift] Text ( 255 ), forms![frmProdReview]!
> [cboDept] Text ( 255 );
> SELECT DISTINCTROW Production.ProductionDate, Department.Department,
> Shift.Shift, Workstation.WorkstationName, Parts.PartID,
> ProductOperation.Operator1, ProductOperation.Operator2,
> ProductOperation.QuantityRun
> FROM Shift INNER JOIN (((Department INNER JOIN Production ON
> Department.Department=Production.Department) INNER JOIN Workstation ON
> Department.Department=Workstation.Department) INNER JOIN (Parts INNER
> JOIN ProductOperation ON Parts.PartID=ProductOperation.PartID) ON
> (Workstation.WorkstationID=ProductOperation.WorkstationID) AND
> (Production.ProductionID=ProductOperation.ProductionID)) ON
> Shift.Shift=Production.Shift
> WHERE (((Production.ProductionDate)=Forms!frmProdReview!txtDate));

What is the result of the query when you copy it to a query design
window (in SQL design). Of course with the parameters filled in?

Re: SQL parameter query not returning values

> What is the result of the query when you copy it to a query design
> window (in SQL design). Of course with the parameters filled in?- Hide quoted text -
>


I made the following changes to the WHERE clause: #
"Production.ProductionDate=#" & Forms!frmProdReview!txtDate & "#"

and now it returns values (thank you) - however -

it returns all records from the selected Date (in this case
11/27/2007) but also returns records from 11/16/2007. I have records
from 11/14 - 11/27 as test records.

Tim

Re: SQL parameter query not returning values

tbrogdon@gmail.com wrote:

>>What is the result of the query when you copy it to a query design
>>window (in SQL design). Of course with the parameters filled in?- Hide quoted text -
>>
>
>
>
> I made the following changes to the WHERE clause: #
> "Production.ProductionDate=#" & Forms!frmProdReview!txtDate & "#"
>
> and now it returns values (thank you) - however -
>
> it returns all records from the selected Date (in this case
> 11/27/2007) but also returns records from 11/16/2007. I have records
> from 11/14 - 11/27 as test records.
>
> Tim
>
>
Here's some code from an example on Parameters
' Define the parameters clause.
strParm = "PARAMETERS [Employee Title] TEXT; "

' Define an SQL statement with the parameters
' clause.
strSql = strParm & "SELECT LastName, FirstName, " _

& "EmployeeID " _
& "FROM Employees " _
& "WHERE Title =[Employee Title];"

I noticed this line
WHERE Title =[Employee Title];

Where do you use Parameters in your Select statement? Do you even need
to use the Parameters declaration? I see no reference to shift or
department in your SQL.

PARAMETERS forms![frmProdReview]![txtDate] Short, forms!
[frmProdReview]![cboShift] Text ( 255 ), forms![frmProdReview]!
[cboDept] Text ( 255 );

You could have something like
"Where Production.ProductionDate=#" & _
Forms!frmProdReview!txtDate & "# And " & _
"Shift = " & [frmProdReview]![cboShift] & " And " & _
"Dept = " & forms![frmProdReview]![cboDept]
instead. Of course you need to remember that dates are surrounded by #,
text values by " or ' and numbers aren't surrounded by anything.

Re: SQL parameter query not returning values

> Where do you use Parameters in your Select statement? Do you even need
> to use the Parameters declaration? I see no reference to shift or
> department in your SQL.
>

I changed mt SQL to the following and it seems to be working well.
It's returning what I was expecting at least (notice the
trepidation :-)

PARAMETERS forms![frmProdReview]![txtDate] DateTime, forms!
[frmProdReview]![cboDept] Text (255), forms![frmProdReview]![cboDept]
Text ( 255 );
SELECT DISTINCTROW ProductOperation.ProductionID,
Production.ProductionDate, Department.Department, Shift.Shift,
Workstation.WorkstationName, Parts.PartID, ProductOperation.Operator1,
ProductOperation.Operator2, ProductOperation.QuantityRun
FROM Shift INNER JOIN (((Department INNER JOIN Production ON
Department.Department=Production.Department) INNER JOIN Workstation ON
Department.Department=Workstation.Department) INNER JOIN (Parts INNER
JOIN ProductOperation ON Parts.PartID=ProductOperation.PartID) ON
(Production.ProductionID=ProductOperation.ProductionID) AND
(Workstation.WorkstationID=ProductOperation.WorkstationID)) ON
Shift.Shift=Production.Shift
WHERE (((Production.ProductionDate)=forms!frmProdReview!txtDate) And
((Department.Department)=forms!frmProdReview!cboDept) And
((Shift.Shift)=forms!frmProdReview!cboShift) And
(("Production.ProductionDate=#" & Forms!frmProdReview!txtDate &
"#")<>False));

I'm not very skilled with programming (working on it) but I sure would
like to know how the code snippet you sent me works for an alternative
if you have the opportunity.

> Here's some code from an example on Parameters
> ' Define the parameters clause.
> strParm = "PARAMETERS [Employee Title] TEXT; "
>
> ' Define an SQL statement with the parameters
> ' clause.
> strSql = strParm & "SELECT LastName, FirstName, " _
>
> & "EmployeeID " _
> & "FROM Employees " _
> & "WHERE Title =[Employee Title];"
>
> I noticed this line
> WHERE Title =[Employee Title];