From parameter query not returning data

  • Thread starter Thread starter tbrogdon
  • Start date Start date
T

tbrogdon

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]![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
(Workstation.WorkstationID=ProductOperation.WorkstationID) AND
(Production.ProductionID=ProductOperation.ProductionID)) ON
Shift.Shift=Production.Shift
WHERE (((Production.ProductionDate)=Forms!frmProdReview!txtDate) And
((Department.Department)=Forms!frmProdReview!cboDept) And
((Shift.Shift)=Forms!frmProdReview!cboShift));
 
What's actually stored in ProductionDate: a date, or a date and time?

If it's a date and time, use

WHERE (Production.ProductionDate BETWEEN Forms!frmProdReview!txtDate And
DateAdd("d", 1, Forms!frmProdReview!txtDate)) AND
(Department.Department=Forms!frmProdReview!cboDept) And
(Shift.Shift=Forms!frmProdReview!cboShift);

How are you typing the date into txtDate? Regardless of what your regional
settings may be, you cannot use dd/mm/yyyy format.
 
Hi Tim -

Look up PARAMETERS Declaration in the help file. Are the dates in your
tables in date/time datatype. The first control name (forms![frmProdReview]!
[txtDate]) is confusing. You've specified SHORT as the data type, which
represents an integer. Change it to DateTime and remove the (255) from
specified Text controls, they aren't part of the declaration.

Please post back as to whether those changes did the trick.

Bob

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]![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
(Workstation.WorkstationID=ProductOperation.WorkstationID) AND
(Production.ProductionID=ProductOperation.ProductionID)) ON
Shift.Shift=Production.Shift
WHERE (((Production.ProductionDate)=Forms!frmProdReview!txtDate) And
((Department.Department)=Forms!frmProdReview!cboDept) And
((Shift.Shift)=Forms!frmProdReview!cboShift));
Click to expand...
 
What's actually stored in ProductionDate: a date, or a date and time?
Click to expand...

Hi Guys.

It's just a date. I'm using mm/dd/yyyy.

And I changed the PARAMETERS to:


PARAMETERS forms![frmProdReview]![txtDate] DateTime, forms!
[frmProdReview]![cboDept] Text, forms![frmProdReview]![cboDept] Text;

It's still not working. The control forms![frmProdReview]![txtDate] is
a text box to accept a Date. Is this an incorrect use?

Now if I run only the query instead of opening the form (which is not
the idea) it brings up a dialog box asking for the Date parameter.

I didn't make the changes to the WHERE clause yet because I wanted to
let you kow that I'm only looking for the Date.

Thanks for your help,

Tim
 
I didn't make the changes to the WHERE clause yet because I wanted to
let you kow that I'm only looking for the Date.
Click to expand...


I don't think my statement above is clear. I wanted to let you know
that I'm currently just trying to troubleshoot your comments
concerning my use of txtDate.

Sorry for the confusion,

Tim
 
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
 
Back
Top