Add null value to date field.

[Guest]

I'm looking to replace the date in T.LeadStart with a null value after this
query is run:

UPDATE ToolData AS T SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation] = '" &
T.ToolCribDesignation & "' AND [DateModified] >= DateSerial(Year(Date()),
Month(Date()) -3, Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
WHERE T.LeadStart <> NZ;

Any ideas??

 

[Michel Walsh]

Hi,

UPDATE .... SET minQty=NULL ... WHERE NOT LeadStart IS NULL


Note that IS NULL is in two words.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


ah, from you second post, I thinks you are better to make it:

UPDATE ToolData AS T
SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation]
= '" &
T.ToolCribDesignation & "' AND [DateModified] >=
DateSerial(Year(Date()),
Month(Date()) -3,
Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
, T.LeadStart=NULL
WHERE NOT T.LeadStart IS NULL;


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hey man, You Rock!!

One last question perhaps you could help on? In the Where clause how would I
add "and T.Inventory > T.MinQuantity?"


Cheers.

Hi,


ah, from you second post, I thinks you are better to make it:

UPDATE ToolData AS T
SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation]
= '" &
T.ToolCribDesignation & "' AND [DateModified] >=
DateSerial(Year(Date()),
Month(Date()) -3,
Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
, T.LeadStart=NULL
WHERE NOT T.LeadStart IS NULL;


Hoping it may help,
Vanderghast, Access MVP

I'm looking to replace the date in T.LeadStart with a null value after
this
query is run:

UPDATE ToolData AS T SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation] = '" &
T.ToolCribDesignation & "' AND [DateModified] >= DateSerial(Year(Date()),
Month(Date()) -3,
Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
WHERE T.LeadStart <> NZ;

Any ideas??

 

[Michel Walsh]

Hi,


if T.MinQuantity is a field, not a computed minimum:



WHERE (NOT T.LeadStart IS NULL) AND T.Inventory > T.MinQuantity



while if it is a computed minimum, the 'query' is probably not updateable.



Hoping it may help,
Vanderghast, Access MVP

Hey man, You Rock!!

One last question perhaps you could help on? In the Where clause how would
I
add "and T.Inventory > T.MinQuantity?"


Cheers.

Hi,


ah, from you second post, I thinks you are better to make it:

UPDATE ToolData AS T
SET T.MinQuantity =

(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation]
= '" &
T.ToolCribDesignation & "' AND [DateModified] >=
DateSerial(Year(Date()),
Month(Date()) -3,
Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
, T.LeadStart=NULL
WHERE NOT T.LeadStart IS NULL;


Hoping it may help,
Vanderghast, Access MVP

in
message news:[email protected]...

I'm looking to replace the date in T.LeadStart with a null value after
this
query is run:

UPDATE ToolData AS T SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation] = '" &
T.ToolCribDesignation & "' AND [DateModified] >=
DateSerial(Year(Date()),
Month(Date()) -3,
Day(Date()))")/90)*1.2*datediff('d',[T.LeadStart],Date())
WHERE T.LeadStart <> NZ;

Any ideas??