Search on dates

DELETED ORIGINAL COMMENT

I think I was thinking of the input form and not the text box to display the range selected, which is what I THINK you were meaning.
 
Last edited:
does the same with two boxes
named 'begdate' and enddate' as in the statements

you know this never gets to a command button
it prompts on opening for the begdate then for the enddate

put a command button on but no where to set event code
 
Here's what is in the criteria of 'edate' in the query

Code:
Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]

Here is what is in the control source for the text boxes

Code:
="From: " & Forms!FrmDates.begdate & "to" & Forms!Frmdates.enddate

what I;m puzzled by is using FrmDates in either since I am not using that form - using RptInvoice
 
My understanding is the you are using the Forms FrmDates to collect the dates from the user. Then you have a command button that will runt he report.

The form use remain olpen while the report is running. This way the query and report can pull data from the form.

Example:

Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]


This is exactly what the Report Dialog example do that I linked to in a previous reply.
 
Hope you don't mind, but I imported your little search form FrmRptCustomers, changed the appropriate paths then I changed IN THE QUERY

Code:
Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]

in the 'edate' criteria to -
Between [Forms]![FrmRptCustomers].[begdate] And [Forms]![FrmRptCustomers].[enddate]

That works as far as getting the data to the report. (I took your advice and changed to a report)

However, I still can not get the two dates to appear in one or two text boxes on the report.
I have tried naming them begdate and enddate, tried edited control source select statements as you had, and that only causes it to prompt a second time for dates.
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.
 
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.

Actually, I was not pulling the data from the table. You would have to use the Dlookup to do that frma text box.


I was trying to have you the data from the form into the textb ox using the control source just like the query is doing.

To pull the date into a text box on the form, you the same form references as the query. If the query uses:

Code:
... Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]


The Control source for the text box on the report will be something like:

Code:
="From: " &  Forms]![FrmDates].[begdate] & " to " &  [Forms]![FrmDates].[enddate]

You might have to format the dates like this:

Code:
="From: " &  Format(Forms]![FrmDates].[begdate], "MM/DD/YYYY") & " to " &  Format([Forms]![FrmDates].[enddate],"MM/DD/YYYY")
 
I had that in the control source but did not have brackets around the field for some odd reason.

WORKS

I appreciate your patience and help so much.

I do this seldom just to make a program for a family member from time to time so I don't do it enough to retain things in my old brain.
I always know there are great people on this forum who will take their important time to help.
Again, thank.
this gets me down to two obstacles and I'll hopefully be finished.

Bob Larsen and Paul have been tremendous help in the past. So that is 3 of you I owe.
 
Ok guys
another question

I want to save/append the results / data from this report to a table to maintain an invoice history..
Since I am using a query based on input criteria to populate this report, I can't use it as an append query. Trying to copy it as an append query and running it on exit from the report, I have to put in the parameters again.

Is there a way around having to re-input the parameters ?
 
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.
 
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.
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.
 
I copied the query I am using for the report and made the copy an append query.
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.
 
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.
Then you have something wrong with the query. It is telling you that it can't find something when it does that.

Can you post the SQL for both queries here?
 
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.

Thought of taking out the criteria in the fields in the append query but then it would have no way of retrieving the data from the search form.
 
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.

It is a good idea to use the same query with the same criteria. Post the SQL from both so we can hopefully spot the problem. If you do not do that, then we cannot have any clue as to what may be a problem.
 
Ok, I will post later today - have my other pc off and it's 2:30am and I've got to get up in a short while.
Thanks and I'll get it posted
 
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.


This is what I tried for append

Code:
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]));
 
Last edited:
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.

How about the append query? Post what you hve done to convert it, if you would be so kind.
 
sorry, added to the other
append query -

Code:
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]));
 
Last edited:

Users who are viewing this thread

Back
Top Bottom