Would this Be an IIF Statement or?

[Corey]

Hi again everyone,

I'm new to VBA but willing to try

I have two tables that my query is looking at.

1. tblProgramActByDay
And the other is:
2. tblProgramByProductByDay

I’m trying to tell the query to
A: Look in the Program ID Field
B: if the Program ID Field = 1667100
C: Go to the table tblProgramByProductByDay

---- In that table look for the criteria---

The Field Program ID must = 0
The Field Product ID must = 38648800
The Field Locale must = en_US
The Field Report_Date must = The Current Month

If it does

Total the amount in the Sales Field from the table tblProgramByProductByDay

If not

Take the total sales for the current month from the table tblProgramActByDay

[NeoPa]

I'm sure help will be forthcoming if you post in here which fields are from which tables.

[Corey]

A: Look in the Program ID Field "From tblProgramActByDay Table"
B: if the Program ID Field = 1667100 "From tblProgramActByDay Table"

C: Go to the table tblProgramByProductByDay

---- In that table look for the criteria---

The Field Program ID must = 0 "From tblProgramByProductByDay Table"
The Field Product ID must = 38648800 "From tblProgramByProductByDay Table"
The Field Locale must = en_US "From tblProgramByProductByDay Table"
The Field Report_Date must = The Current Month "From tblProgramByProductByDay Table"

[NeoPa]

Is there a relationship between the tables? Such that a field (or set of fields) in tblProgramActByDay must be equal to a field (or set of fields) in tblProgramProductByDay?

[MMcCarthy]

Try this:

SELECT [Program ID], Sum(nz([Sales],0)) As TotalSales
FROM tblProgramByProductByDay
WHERE [Program ID] = 0
AND [Product ID] = 38648800
AND [Locale] = 'en_US '
AND Month(Report_Date) = Month(Date()
AND Year(Report_Date) = Year(Date();
UNION
SELECT [Program ID], Sum(nz([Sales],0)) As TotalSales
FROM tblProgramActByDay
WHERE [Program ID] <> 1667100
GROUP BY [Program ID];