Hi again everyone,

I'm stumped again.

I have two reports that my query is looking at.
One is:
1. ProgramActByDay
And the other is:
2. ProgramByProductByDay

What I'm trying to make it do is is to find a cretin program ID then look at a different report and use criteria to populate the sales,

Maybe this will make since

IIF ([Program_ID.ProgramActByDay] = 1667100
Then go to a different report titled: ProgramByProductByDay

And look for
All Program ID = 0
All Product ID = 38648800
All Locale = en_US
Only calculate the data for the current month
Then total all the sales
And put it in the sales field

Does this make Sense?

IIF ([Program_ID] = 1667100

Then go to query
ProgramByProductByDay

Field->Program ID = 0
Field->Product ID = 38648800
Field->Locale = en_US
Field->Report_Date = Current Month


Then total all the sales in the Sales Field

Then put the total from the sales back into the
ProgramActByDay query,

Hi,

   This sounds like it is the On Click event of a button, and that the user can select Program_ID. If that is the case, and you have a combo box bound to Program_ID, then you can add your conditional logic like this if you are just trying to trap the one Program_ID:

Dim iProgram_ID As Integer 'I am assuming
Dim sDocName As String
Dim sLink As String

iProgram_ID = Me.Program_ID

sLink = "[Program_ID] = " & 0 & " AND [Product_ID] = " & 3864880 & _
        " AND [Locale] = 'en_US'"

If iProgram_ID = 1667100 Then
   sDocName = "ProgramByProductByDay"
Else
   sDocName = "YourOtherReportName"
End If

DoCmd.OpenReport sDocName

I would create a hidden field on your form with the button to launch this report called CurMonth, and put this in the Recordsource: DatePart("m", Date()). Then in your query behind the report, add this line in the criteria section: [Forms]![frmYourFormName].[CurMonth] This will pull data only from the current month. There is a way to do it in the query with fields, but I can't work in Access to figure it out now. This will work.

Then create the calculations on the report. Like add a textbox to the header or footer and enter this in it: =Sum([txtSummedFieldName])

Does this help?

mmcdonal

Sorry, add sLink to the DoCmd.OpenReport sDocName statement in the WHERE clause section.

mmcdonal

What if I dont have a form

You would attach the code to the event that is running the query. How are you doing that?

mmcdonal

I was trying to put it in a Modules, or should I create a form and then hide it

You could put this function in a module. How are you going to trigger it?

mmcdonal