Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]
="From: " & Forms!FrmDates.begdate & "to" & Forms!Frmdates.enddate
Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]
The difference I can see is that you were pulling data direct from a table where I am pulling from a query. But that should make no difference.
Something simple I am missing.
... Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]
="From: " & Forms]![FrmDates].[begdate] & " to " & [Forms]![FrmDates].[enddate]
="From: " & Format(Forms]![FrmDates].[begdate], "MM/DD/YYYY") & " to " & Format([Forms]![FrmDates].[enddate],"MM/DD/YYYY")
Use a form for input.Is there a way around having to re-input the parameters ?
WHY? If you leave the form open, you still have the form and can refer to it in the append query. Just don't close it until you have run the append query.Bob,
I'm using a search form to input the parameters for the report such as date, unit number.
Problem is, if I try to run an append query on exiting the report, I have to re-input the parameters.
Then you have something wrong with the query. It is telling you that it can't find something when it does that.Every time I try to run it, since it has the criteria in it, it prompts for the info again, even though the search form is still open.
The query that populates the report works fine. I input the unitno and the date the work was performed(wdate). Both of those I have displaying on the report also. It does some calculations based on those two parameters.
Once the report is printed, I just want to append all the data on the report to a table.
Just thought the easiest way would be the same select query as an append query, of course, a different name.
SELECT TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter, Sum(TblMain.miles) AS SumOfmiles, Sum(TblMain.partscost) AS SumOfpartscost, Sum(TblMain.perdiem) AS SumOfperdiem, Sum(TblMain.workhours) AS SumOfworkhours, Sum(TblMain.lodging) AS SumOflodging, ([Sumofperdiem])*45 AS Totperdiem, ([Sumofpartscost])*1.6 AS TotPartscost, ([sumofworkhours])*95 AS Totwrkhrs, ([sumofmiles]+[Sumoflodging]+[Totperdiem]+[TotPartscost]+[Totwrkhrs]) AS Total
FROM TblMain
GROUP BY TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter
HAVING (((TblMain.unitno) Like [Forms]![frmRptUIDInv].[FrmUID]) AND ((TblMain.wdate) Like [Forms]![frmRptUIDInv].[pdate]));
INSERT INTO TblInvHistory ( empname, office, aremanager, unitno, unitserno, aerialno, wdate, costcenter, miles, partscost, perdiem, workhours, lodging, Totperdiem, Totpartscost, Totwrkhrs, InvTotal )
SELECT TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter, Sum(TblMain.miles) AS SumOfmiles, Sum(TblMain.partscost) AS SumOfpartscost, Sum(TblMain.perdiem) AS SumOfperdiem, Sum(TblMain.workhours) AS SumOfworkhours, Sum(TblMain.lodging) AS SumOflodging, ([Sumofperdiem])*45 AS Totperdiem, ([Sumofpartscost])*1.6 AS TotPartscost, ([sumofworkhours])*95 AS Totwrkhrs, ([sumofmiles]+[Sumoflodging]+[Totperdiem]+[TotPartscost]+[Totwrkhrs]) AS Total
FROM TblMain
GROUP BY TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter
HAVING (((TblMain.unitno) Like [Forms]![frmRptUIDInv].[FrmUID]) AND ((TblMain.wdate) Like [Forms]![frmRptUIDInv].[pdate]));
Heck, here it is. Plus I can sleep on it
Code:SELECT TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter, Sum(TblMain.miles) AS SumOfmiles, Sum(TblMain.partscost) AS SumOfpartscost, Sum(TblMain.perdiem) AS SumOfperdiem, Sum(TblMain.workhours) AS SumOfworkhours, Sum(TblMain.lodging) AS SumOflodging, ([Sumofperdiem])*45 AS Totperdiem, ([Sumofpartscost])*1.6 AS TotPartscost, ([sumofworkhours])*95 AS Totwrkhrs, ([sumofmiles]+[Sumoflodging]+[Totperdiem]+[TotPartscost]+[Totwrkhrs]) AS Total FROM TblMain GROUP BY TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter HAVING (((TblMain.unitno) Like [Forms]![frmRptUIDInv].[FrmUID]) AND ((TblMain.wdate) Like [Forms]![frmRptUIDInv].[pdate]));
This works perfectly for the report.
INSERT INTO TblInvHistory ( empname, office, aremanager, unitno, unitserno, aerialno, wdate, costcenter, miles, partscost, perdiem, workhours, lodging, Totperdiem, Totpartscost, Totwrkhrs, InvTotal )
SELECT TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter, Sum(TblMain.miles) AS SumOfmiles, Sum(TblMain.partscost) AS SumOfpartscost, Sum(TblMain.perdiem) AS SumOfperdiem, Sum(TblMain.workhours) AS SumOfworkhours, Sum(TblMain.lodging) AS SumOflodging, ([Sumofperdiem])*45 AS Totperdiem, ([Sumofpartscost])*1.6 AS TotPartscost, ([sumofworkhours])*95 AS Totwrkhrs, ([sumofmiles]+[Sumoflodging]+[Totperdiem]+[TotPartscost]+[Totwrkhrs]) AS Total
FROM TblMain
GROUP BY TblMain.empname, TblMain.office, TblMain.aremanager, TblMain.unitno, TblMain.unitserno, TblMain.aerialno, TblMain.wdate, TblMain.costcenter
HAVING (((TblMain.unitno) Like [Forms]![frmRptUIDInv].[FrmUID]) AND ((TblMain.wdate) Like [Forms]![frmRptUIDInv].[pdate]));