Add null value to date field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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??
 
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
 
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
 
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.


Michel Walsh said:
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

InventoryQueryGuy said:
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??
Click to expand...
Click to expand...
 
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


InventoryQueryGuy said:
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.


Michel Walsh said:
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??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top