date add

[mbynoe via AccessMonster.com]

Hello,

How can set up in a query: Yesterdays work_mcf added to today's MCF
subtracted by today's with_mcf? I have the following query:

INSERT INTO workmcf ( meter_number, prod_date_time, work_mcf )
SELECT w1.meter_number, w1.prod_date_time, (Select Sum ((w.work_mcf + w.
meas_mcf) - with_mcf) from workingnew w Where w.prod_date_time <= w1.
prod_date_time and w1.meter_number = w.meter_number and w.
prod_date_time>DateAdd ("d",-2, start)) AS work_mcf
FROM workingnew AS w1
WHERE (((w1.meter_number) in (Select meter_number from InjectedMeter))) AND (
((w1.prod_date_time between [start] AND [end])))
ORDER BY w1.meter_number, w1.prod_date_time;

Thank you!

 

[Jeff Boyce]

What happens when you run that query?

What happens if you remove the INSERT INTO portion and just run the query as
a select query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mbynoe via AccessMonster.com]

Thanks Jeff - when I run the query it calculates, however, the totals are
wrong. When I took out the INSERT, it's blank.

What happens when you run that query?

What happens if you remove the INSERT INTO portion and just run the query as
a select query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 13 lines]

Thank you!

 

[Jeff Boyce]

So, you are saying that the query as a select query (and not an
append/INSERT) query, returns nothing? I don't see how that can be, since
as part of the INSERT, it IS returning values.

See what happens if you create a new query, from scratch, but only to select
(and compute) the values you are interested in.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff - when I run the query it calculates, however, the totals are
wrong. When I took out the INSERT, it's blank.

What happens when you run that query?

What happens if you remove the INSERT INTO portion and just run the query
as
a select query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 13 lines]

Thank you!