Criteria Problem

S

Shell

I made a function based on a query. There is a criteria on the query. The
criteria is set on a form. When I input criteria on the form and then run the
function, it always shows " one of parameters has no definition". But when I
input the criteria directly into design mode of the query and save the query,
the function runs very well. Would you please tell me how to solve this
problem if I want to put the criteria on a form?

Thanks

Shell
 
W

Wayne-I-M

Put this in the criteria row of the query

[Forms]![FormNameHere]![ControlNameHere]

The control name is the name of text box (or other) on the form that you use
to input the criteria

Hope this helps
 
L

Larry Daugherty

Please give us the complete details of what you have done so that we
might help you.

Please include the code of the function you have created.
 
S

Shell

The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders]) AS Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN AS 2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER JOIN Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function GetEstimateRequirementA(EstimatePN As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function

Would you please help check what's wrong with it?

Shell
 
L

Larry Daugherty

In your first IIF(), IIf([Qty] Is Null,0,[Qty])
I don't see where [qty] originates. If that's the value on the form
then you need to refer to the form and the control where QTY is
located: forms!MyForm!txtQTY

HTH
--
-Larry-
--

Shell said:
The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders]) AS Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN AS 2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER JOIN Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function GetEstimateRequirementA(EstimatePN As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function

Would you please help check what's wrong with it?

Shell



Larry Daugherty said:
Please give us the complete details of what you have done so that we
might help you.

Please include the code of the function you have created.

--
-Larry-
--

situation
still keeps and
save
Click to expand...
Click to expand...
 
S

Shell

The qty is refered to "Monthly Orders".

Thanks

Shell Hu

Larry Daugherty said:
In your first IIF(), IIf([Qty] Is Null,0,[Qty])
I don't see where [qty] originates. If that's the value on the form
then you need to refer to the form and the control where QTY is
located: forms!MyForm!txtQTY

HTH
--
-Larry-
--

Shell said:
The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders]) AS Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN AS 2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER JOIN Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function GetEstimateRequirementA(EstimatePN As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function

Would you please help check what's wrong with it?

Shell



Larry Daugherty said:
Please give us the complete details of what you have done so that we
might help you.

Please include the code of the function you have created.

--
-Larry-
--

Yes! I kept the form open while the query runs. But the situation
still keeps
the same.
Shell

:

The form must remain open while the query runs.

HTH
--
-Larry-
--

I made a function based on a query. There is a criteria on the
query. The
criteria is set on a form. When I input criteria on the form and
then run the
function, it always shows " one of parameters has no
definition".
But when I
input the criteria directly into design mode of the query and
save
the query,
the function runs very well. Would you please tell me how to
solve
this
problem if I want to put the criteria on a form?

Thanks

Shell
Click to expand...
Click to expand...
Click to expand...
 
S

Shell

Monthly_Estimate2 can run without any problem. Only the function based on
Monthly_Estimate2 can not run.

Shell Hu

Larry Daugherty said:
In your first IIF(), IIf([Qty] Is Null,0,[Qty])
I don't see where [qty] originates. If that's the value on the form
then you need to refer to the form and the control where QTY is
located: forms!MyForm!txtQTY

HTH
--
-Larry-
--

Shell said:
The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders]) AS Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN AS 2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER JOIN Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function GetEstimateRequirementA(EstimatePN As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function

Would you please help check what's wrong with it?

Shell



Larry Daugherty said:
Please give us the complete details of what you have done so that we
might help you.

Please include the code of the function you have created.

--
-Larry-
--

Yes! I kept the form open while the query runs. But the situation
still keeps
the same.
Shell

:

The form must remain open while the query runs.

HTH
--
-Larry-
--

I made a function based on a query. There is a criteria on the
query. The
criteria is set on a form. When I input criteria on the form and
then run the
function, it always shows " one of parameters has no
definition".
But when I
input the criteria directly into design mode of the query and
save
the query,
the function runs very well. Would you please tell me how to
solve
this
problem if I want to put the criteria on a form?

Thanks

Shell
Click to expand...
Click to expand...
Click to expand...
 
L

Larry Daugherty

I don't think I can help you. You may get help sooner if you start a
new thread and post the code for your queries in your initial post.

HTH
--
-Larry-
--

Shell said:
Monthly_Estimate2 can run without any problem. Only the function based on
Monthly_Estimate2 can not run.

Shell Hu

Larry Daugherty said:
In your first IIF(), IIf([Qty] Is Null,0,[Qty])
I don't see where [qty] originates. If that's the value on the form
then you need to refer to the form and the control where QTY is
located: forms!MyForm!txtQTY

HTH
--
-Larry-
--

Shell said:
The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders])
Click to expand...
AS
Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN
Click to expand...
AS
2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER
Click to expand...
JOIN
Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function
Click to expand...
GetEstimateRequirementA(EstimatePN
As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function

Would you please help check what's wrong with it?

Shell



:

Please give us the complete details of what you have done so
Click to expand...
that
we
might help you.

Please include the code of the function you have created.

--
-Larry-
--

Yes! I kept the form open while the query runs. But the situation
still keeps
the same.
Shell

:

The form must remain open while the query runs.

HTH
Click to expand...
on
the
query. The
criteria is set on a form. When I input criteria on the
Click to expand...
form
and
then run the
function, it always shows " one of parameters has no
definition".
But when I
input the criteria directly into design mode of the
Click to expand...
query
and
save
the query,
the function runs very well. Would you please tell me how to
solve
this
problem if I want to put the criteria on a form?

Thanks

Shell
Click to expand...
Click to expand...
Click to expand...
 
Top