J
Joe
Hi All !
I cannot get the DATE from a FORM to put as a criteria .
This is my SQL code :
I cannot get the DATE from a FORM to put as a criteria .
This is my SQL code :
J
Joe
INSERT INTO SFC_table ( pn, labour_tobeat, labor_actual, labor_avg, clock_number, full_info, wo, facility )
SELECT Results_Table_Sorted_QRY.pn, ([MoyenneDeSumOfLabor]+[MinDeSumOfLabor])/2 AS tobeat, Results_Table_Sorted_QRY.SumOfLabor, SFC_results_For_Report_By_PN_And_Facility_QRY.MoyenneDeSumOfLabor, Results_Table_Sorted_QRY.clockno, [Results_Table_Sorted_QRY.wo] & " / " & Results_Table_Sorted_QRY.facility & " " & [Board_Profiles.Desc] AS full_info, Results_Table_Sorted_QRY.Wo, Results_Table_Sorted_QRY.facility
FROM (Results_Table_Sorted_QRY LEFT JOIN SFC_results_For_Report_By_PN_And_Facility_QRY ON (Results_Table_Sorted_QRY.pn=SFC_results_For_Report_By_PN_And_Facility_QRY.pn) AND (Results_Table_Sorted_QRY.facility=SFC_results_For_Report_By_PN_And_Facility_QRY.facility) AND (Results_Table_Sorted_QRY.SumOfQty=SFC_results_For_Report_By_PN_And_Facility_QRY.SumOfQty)) LEFT JOIN Board_Profiles ON Results_Table_Sorted_QRY.pn=Board_Profiles.PN
WHERE Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524And Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!todate;
SELECT Results_Table_Sorted_QRY.pn, ([MoyenneDeSumOfLabor]+[MinDeSumOfLabor])/2 AS tobeat, Results_Table_Sorted_QRY.SumOfLabor, SFC_results_For_Report_By_PN_And_Facility_QRY.MoyenneDeSumOfLabor, Results_Table_Sorted_QRY.clockno, [Results_Table_Sorted_QRY.wo] & " / " & Results_Table_Sorted_QRY.facility & " " & [Board_Profiles.Desc] AS full_info, Results_Table_Sorted_QRY.Wo, Results_Table_Sorted_QRY.facility
FROM (Results_Table_Sorted_QRY LEFT JOIN SFC_results_For_Report_By_PN_And_Facility_QRY ON (Results_Table_Sorted_QRY.pn=SFC_results_For_Report_By_PN_And_Facility_QRY.pn) AND (Results_Table_Sorted_QRY.facility=SFC_results_For_Report_By_PN_And_Facility_QRY.facility) AND (Results_Table_Sorted_QRY.SumOfQty=SFC_results_For_Report_By_PN_And_Facility_QRY.SumOfQty)) LEFT JOIN Board_Profiles ON Results_Table_Sorted_QRY.pn=Board_Profiles.PN
WHERE Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524And Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!todate;
J
John Spencer
Do you get a syntax error?
Do you get no results or the wrong results?
Do you get prompted for Forms!Coordinator_Screen!todate?
You might try declaring the parameter type at the beginning of the query to
ensure your input is being interpreted correctly.
PARAMETERS Forms!Coordinator_Screen!todate DateTime
, Forms!Coordinator_Screen!modifiable524 Text (255);
INSERT INTO SFC_table ( pn, labour_tobeat, labor_actual, labor_avg,
clock_number, full_info, wo, facility )
SELECT Results_Table_Sorted_QRY.pn,
([MoyenneDeSumOfLabor]+[MinDeSumOfLabor])/2 AS tobeat,
Results_Table_Sorted_QRY.SumOfLabor,
SFC_results_For_Report_By_PN_And_Facility_QRY.MoyenneDeSumOfLabor,
Results_Table_Sorted_QRY.clockno, [Results_Table_Sorted_QRY.wo] & " / " &
Results_Table_Sorted_QRY.facility & " " & [Board_Profiles.Desc] AS full_info,
Results_Table_Sorted_QRY.Wo, Results_Table_Sorted_QRY.facility
FROM (Results_Table_Sorted_QRY LEFT JOIN
SFC_results_For_Report_By_PN_And_Facility_QRY ON
(Results_Table_Sorted_QRY.pn=SFC_results_For_Report_By_PN_And_Facility_QRY.pn)
AND
(Results_Table_Sorted_QRY.facility=SFC_results_For_Report_By_PN_And_Facility_QRY.facility)
AND
(Results_Table_Sorted_QRY.SumOfQty=SFC_results_For_Report_By_PN_And_Facility_QRY.SumOfQty))
LEFT JOIN Board_Profiles ON Results_Table_Sorted_QRY.pn=Board_Profiles.PN
WHERE Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524
And Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!todate;
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Do you get no results or the wrong results?
Do you get prompted for Forms!Coordinator_Screen!todate?
You might try declaring the parameter type at the beginning of the query to
ensure your input is being interpreted correctly.
PARAMETERS Forms!Coordinator_Screen!todate DateTime
, Forms!Coordinator_Screen!modifiable524 Text (255);
INSERT INTO SFC_table ( pn, labour_tobeat, labor_actual, labor_avg,
clock_number, full_info, wo, facility )
SELECT Results_Table_Sorted_QRY.pn,
([MoyenneDeSumOfLabor]+[MinDeSumOfLabor])/2 AS tobeat,
Results_Table_Sorted_QRY.SumOfLabor,
SFC_results_For_Report_By_PN_And_Facility_QRY.MoyenneDeSumOfLabor,
Results_Table_Sorted_QRY.clockno, [Results_Table_Sorted_QRY.wo] & " / " &
Results_Table_Sorted_QRY.facility & " " & [Board_Profiles.Desc] AS full_info,
Results_Table_Sorted_QRY.Wo, Results_Table_Sorted_QRY.facility
FROM (Results_Table_Sorted_QRY LEFT JOIN
SFC_results_For_Report_By_PN_And_Facility_QRY ON
(Results_Table_Sorted_QRY.pn=SFC_results_For_Report_By_PN_And_Facility_QRY.pn)
AND
(Results_Table_Sorted_QRY.facility=SFC_results_For_Report_By_PN_And_Facility_QRY.facility)
AND
(Results_Table_Sorted_QRY.SumOfQty=SFC_results_For_Report_By_PN_And_Facility_QRY.SumOfQty))
LEFT JOIN Board_Profiles ON Results_Table_Sorted_QRY.pn=Board_Profiles.PN
WHERE Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524
And Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!todate;
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
J
Joe
It returns no data.
I suspect that date format from the form but I tried everything and nothing
is working.
By running the SQL code and putting a date at the pop-up , it's working.
I suspect that date format from the form but I tried everything and nothing
is working.
By running the SQL code and putting a date at the pop-up , it's working.
John Spencer said:
J
John Spencer
It looks as if you might be running into a problem with international dates.
See International Dates in Access at:
http://allenbrowne.com/ser-36.html
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
See International Dates in Access at:
http://allenbrowne.com/ser-36.html
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
J
Joe
This is what I tried :
In my criteria (SQL) in my QRY:
....WHERE
Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524 And
Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!SQL_Date;
In my form : (SQL_Date declared as DATE)
SQL_Date = Format(DateValue(Now()), "mm\/dd\/yyyy")
I'm getting no results...
In my criteria (SQL) in my QRY:
....WHERE
Results_Table_Sorted_QRY.clockno=Forms!Coordinator_Screen!modifiable524 And
Results_Table_Sorted_QRY.op_offDate < Forms!Coordinator_Screen!SQL_Date;
In my form : (SQL_Date declared as DATE)
SQL_Date = Format(DateValue(Now()), "mm\/dd\/yyyy")
I'm getting no results...
John Spencer said:
J
John Spencer
So did you declare the parameter As I suggested earlier?
If not you can try that or you can try
Results_Table_Sorted_QRY.op_offDate < CDate(Forms!Coordinator_Screen!SQL_Date)
It is possible that Access is treating the value of
Forms!Coordinator_Screen!SQL_Date as a division statement and is generating a
number that is so small that it is considered to be a time on December 30,
1899. So you won't get any records returned.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
If not you can try that or you can try
Results_Table_Sorted_QRY.op_offDate < CDate(Forms!Coordinator_Screen!SQL_Date)
It is possible that Access is treating the value of
Forms!Coordinator_Screen!SQL_Date as a division statement and is generating a
number that is so small that it is considered to be a time on December 30,
1899. So you won't get any records returned.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
J
Joe
Hi John ,
I tried both and doesn't work.
When putting CData , got the error '94' invalid use of NULL.
I tried both and doesn't work.
When putting CData , got the error '94' invalid use of NULL.
John Spencer said:
J
John Spencer
Invalid use of null error indicates that there is no value for
Forms!Coordinator_Screen!SQL_Date
Which probably means at least one of the following is true
-- you are referring to the wrong control or wrong form
-- you have mistyped the name of the form or control
-- the form Coordinator_Screen is not open in form view
-- there is no value in the control on the form
-- the control is on a subform that is on a main form.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Forms!Coordinator_Screen!SQL_Date
Which probably means at least one of the following is true
-- you are referring to the wrong control or wrong form
-- you have mistyped the name of the form or control
-- the form Coordinator_Screen is not open in form view
-- there is no value in the control on the form
-- the control is on a subform that is on a main form.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
J
Joe
I simplified the code :
I simplified the code to make it easier to troubleshoot , still doesn't
work. Here is the code:
(from my query name "test")
SELECT Results.Wo, Results.op_offDate
FROM Results
WHERE Results.op_offDate < Forms!testform!SQL_Date;
(from a command button on a form to do my test. The name of the form is
testform)
Private Sub Commande0_Click()
Dim SQL_Date As Date
SQL_Date = Now()
DoCmd.OpenQuery ("test")
End Sub
***When I replace "Forms!Test!SQL_Date" on my SQL code by "now()" , it's
working correctly. Why...
I tried CDate , CVDate , Format(date,xx) , etc...
Help !!!
I simplified the code to make it easier to troubleshoot , still doesn't
work. Here is the code:
(from my query name "test")
SELECT Results.Wo, Results.op_offDate
FROM Results
WHERE Results.op_offDate < Forms!testform!SQL_Date;
(from a command button on a form to do my test. The name of the form is
testform)
Private Sub Commande0_Click()
Dim SQL_Date As Date
SQL_Date = Now()
DoCmd.OpenQuery ("test")
End Sub
***When I replace "Forms!Test!SQL_Date" on my SQL code by "now()" , it's
working correctly. Why...
I tried CDate , CVDate , Format(date,xx) , etc...
Help !!!
John Spencer said:
J
John Spencer
Try the following test query.
SELECT Top 10 Results.Wo, Results.op_offDate
, Forms!testform!SQL_Date as CheckDate
FROM Results
Is there a value in CheckDate?
If there is a value, is it right-aligned?
Does it look like a date?
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
SELECT Top 10 Results.Wo, Results.op_offDate
, Forms!testform!SQL_Date as CheckDate
FROM Results
Is there a value in CheckDate?
If there is a value, is it right-aligned?
Does it look like a date?
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
J
Joe
Found the problem.
Have to pass through a text box or combo box in the form.
Cannot pass through a variable in the code of the form.
Have to pass through a text box or combo box in the form.
Cannot pass through a variable in the code of the form.
Joe said:I simplified the code :
I simplified the code to make it easier to troubleshoot , still doesn't
work. Here is the code:
(from my query name "test")
SELECT Results.Wo, Results.op_offDate
FROM Results
WHERE Results.op_offDate < Forms!testform!SQL_Date;
(from a command button on a form to do my test. The name of the form is
testform)
Private Sub Commande0_Click()
Dim SQL_Date As Date
SQL_Date = Now()
DoCmd.OpenQuery ("test")
End Sub
***When I replace "Forms!Test!SQL_Date" on my SQL code by "now()" , it's
working correctly. Why...
I tried CDate , CVDate , Format(date,xx) , etc...
Help !!!
John Spencer said: