Access Report Problem

[Rob Brown]

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.

[LeAnne]

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.

[Gary.Lowe]

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.

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