-
Hi,
Does anyone know anything about Access 97?
I'm using an Access report as part of a small front end application.
Now, on my report there is a text box for total mileage.
I want this control to show the sum of 8 fields in each record for every record with a date preceeding the one the report is currently displaying.
On my VB form I have managed to do this by placing a data control (dtaTotMil) on the form and giving it a recordsource of:
dtaTotMil.RecordSource = "SELECT Sum(Up428 + Up435 + Up445 + Up453 + Ov417 + Ov420 + Ov425 + Ov436) AS [TotMil] FROM tblMain WHERE [WeekEndingSun] <= #" & frmFind.datSelDate & "#;"
Bit of a mouthfull I know but it does the job.
Does anyone know how to do this in Access.
I don't care how I have to go about it as long as I can get the correct information into my text box.
This is the very last thing I have to do before I can deploy my application so any suggestions would be much appreciated.
Best Regards,
Rob Brown.
-
Since I do not know how your report is bound to your data, I will assume that it is bound to a query. If the fields Up428 Up435 Up445 Up453 Ov417 Ov420 Ov425 Ov436 are in this query, I would simply add another field that will do the calculation for you with an Iff statement.
But, it would probably be easier to create a new query with the sql statement:
SELECT Sum(Up428+Up435+Up445+Up453+Ov417+Ov420+Ov425+Ov436) AS TotMil FROM tblMain WHERE (((tblMain.WeekEndingSun)=[Prompt for Date]));
Then, Create a new report and use this as a subreport.
-
Create a query in access with the fields you require on the report.
Create a form with your search field
Create a function in a module to store the date in
i.e.
Code:
Public function SearchDate()
SearchDate = frmFind.datSelDate
end function
In the query you have created insert SearchDate() in the criteria section of the field where you want to find the date.
Hope this works
