[RESOLVED] INSERT INTO Report

[Mcritt]

Really could use some help on this one...
Is it possible to INSERT data into a report on microsoft access?

I have the report already created and data gets moved into the table that links with the report and it seems to show the data, but I would like to add two more pieces of data to it but it seems not to be working.

I would like to add the min and max time to the report which is taken from another table called MainDataLog.

Connect.Execute "INSERT INTO Shift_Totals (" & _
"MaxTime ," & _
"MinTime  ," & _
"Select    ," & _
"MIN([TIME]) , " & _
"MAX([TIME]), " & _
"FROM MainDataLog"

The error I am getting is syntax error in insert into statement

[Chubby]

Really could use some help on this one...
Is it possible to INSERT data into a report on microsoft access?

I have the report already created and data gets moved into the table that links with the report and it seems to show the data, but I would like to add two more pieces of data to it but it seems not to be working.

I would like to add the min and max time to the report which is taken from another table called MainDataLog.

Connect.Execute "INSERT INTO Shift_Totals (" & _
"MaxTime ," & _
"MinTime  ," & _
"Select    ," & _
"MIN([TIME]) , " & _
"MAX([TIME]), " & _
"FROM MainDataLog"

The error I am getting is syntax error in insert into statement

This is a table Yes ??

You are missing a closing bracket on the SQL String
Also your Min & Max were the wrong way around:
Should read something like:

Connect.Execute "INSERT INTO Shift_Totals (" & _
"MinTime," & _
"MaxTime)" & _
"Select " & _
"MIN([TIME]) , " & _
"MAX([TIME]) " & _
"FROM MainDataLog"

Chubby.

[DKenny]

You don't need an INSERT statement.

For you MIN date (same procedure for your MAX date)
1/ Add a textbox to the report
2/ Change the "Control Source" for the TextBox to

SELECT MIN([TIME]) FROM MainDataLog

[Mcritt]

DKenny, I tried what you said and it gives me syntax error (missing operator)in query expression 'First([Select MIN([Time])From MainDataLog]). Not sure where the 'first' came from because I never typed it in, and it seems to be putting extra square brackets around everything.
I typed it in like this Select MIN ([Time]) FROM MainDataLog

Unfortuatly chubby method won't work beacuse I have data there and if I add that code in, it places the min and max time in a seperate column(like it should) but on another row at the buttom of the table so the dates always come p with nothing in them...

[Mcritt]

anyone else have a suggestion for me? I could really use some help.... I still seem to be having trouble building the expression in access

[DKenny]

God I hate Access.
There doesn't seem to be any way to achieve this with TextBoxes. It can be done, however with Lables.

In this example I added to 2 labels and put juck data in their captions. I called them "lblMinPlaceholder" and "lblMaxPlaceholder" respectively.
Then in the _Open event code for the report I put the following code.

I hope that this will give you what you need....

Private Sub Report_Open(Cancel As Integer)
Dim rsDate As ADODB.Recordset
 
    Set rsDate = New ADODB.Recordset
    
    rsDate.Open "Select Min([Time]) from MainDataLog", CurrentProject.Connection
        
    Me.lblMinPlaceholder.Caption = rsDate.Fields(0).Value
    
    rsDate.Close
    
    rsDate.Open "Select Max([Time]) from MainDataLog", CurrentProject.Connection
    
    Me.lblMaxPlaceHolder.Caption = rsDate.Fields(0).Value
    
    rsDate.Close
    Set rsDate = Nothing
End Sub

[Mcritt]

Thanks for all the help...
I added the code posted the access report vba, didn't work at first but went to tools references and enabled activeX... works like a charm....thanks again