DateAdd problem

[mbynoe via AccessMonster.com]

Please...help.....My query freezes when I try to execute the following query..
..

INSERT INTO workingnew ( meter_number, prod_date_time, meas_dth, meas_mcf,
BTU )
SELECT dbo_meterset_volume.meter_number, dbo_meterset_volume.prod_date_time,
[meas_dth]/1000 AS DTH, [meas_mcf]/1000 AS MCF, IIf([meas_mcf]<0.0001," 0",+
[meas_dth]/[meas_mcf]*1000) AS BTU
FROM dbo_meterset_volume
GROUP BY dbo_meterset_volume.meter_number, dbo_meterset_volume.prod_date_time,
[meas_dth]/1000, [meas_mcf]/1000, IIf([meas_mcf]<0.0001," 0",+[meas_dth]/
[meas_mcf]*1000)
HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time) Between (Select
DateAdd("d", 1, Max(prod_date_time)) From workingnew) And ( date() ) ) );


I want to be able to click a button and get the current information based off
of the max date.

Thanks

 

[Duane Hookom]

I'm not sure if this is your issue but you have an expression that might
return either text or a number:
IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
Try change the " 0" to just 0:
IIf([meas_mcf]<0.0001,0,[meas_dth]/[meas_mcf]*1000) AS BTU

Text is text and numbers is numbers.

 

[raskew via AccessMonster.com]

Hi -
..."IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000)"

What is that about? If something = true you give it a text value of "0",
else '+' and an apparently numerical value.

Probably need to look there.

Bob

Please...help.....My query freezes when I try to execute the following query..
..

INSERT INTO workingnew ( meter_number, prod_date_time, meas_dth, meas_mcf,
BTU )
SELECT dbo_meterset_volume.meter_number, dbo_meterset_volume.prod_date_time,
[meas_dth]/1000 AS DTH, [meas_mcf]/1000 AS MCF, IIf([meas_mcf]<0.0001," 0",+
[meas_dth]/[meas_mcf]*1000) AS BTU
FROM dbo_meterset_volume
GROUP BY dbo_meterset_volume.meter_number, dbo_meterset_volume.prod_date_time,
[meas_dth]/1000, [meas_mcf]/1000, IIf([meas_mcf]<0.0001," 0",+[meas_dth]/
[meas_mcf]*1000)
HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time) Between (Select
DateAdd("d", 1, Max(prod_date_time)) From workingnew) And ( date() ) ) );

I want to be able to click a button and get the current information based off
of the max date.

Thanks

 

[mbynoe via AccessMonster.com]

Thanks Duane!

However, the query works fine if I take out the DateAdd portion....it only
freezes when I add this function in.

I'm not sure if this is your issue but you have an expression that might
return either text or a number:
IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
Try change the " 0" to just 0:
IIf([meas_mcf]<0.0001,0,[meas_dth]/[meas_mcf]*1000) AS BTU

Text is text and numbers is numbers.

Please...help.....My query freezes when I try to execute the following query..
..

[quoted text clipped - 16 lines]

Thanks

 

[Duane Hookom]

HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time) Between (Select
Max(prod_date_time) From workingnew) +1 And date() ) );
--
Duane Hookom
Microsoft Access MVP

Thanks Duane!

However, the query works fine if I take out the DateAdd portion....it only
freezes when I add this function in.

I'm not sure if this is your issue but you have an expression that might
return either text or a number:
IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
Try change the " 0" to just 0:
IIf([meas_mcf]<0.0001,0,[meas_dth]/[meas_mcf]*1000) AS BTU

Text is text and numbers is numbers.

Please...help.....My query freezes when I try to execute the following query..
..

[quoted text clipped - 16 lines]

Thanks

 

[mbynoe via AccessMonster.com]

Thanks Duane for your help!

HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time) Between (Select
Max(prod_date_time) From workingnew) +1 And date() ) );

Thanks Duane!

[quoted text clipped - 14 lines]

 

[mbynoe via AccessMonster.com]

Hello...is it possible to use a DLookUp instead of a DateAdd...on the
following statement?

HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time) Between (Select
Max(prod_date_time) From workingnew) +1 And date() ) );

Thanks Duane!

[quoted text clipped - 14 lines]