Query not doing anything.

[InventoryQueryGuy]

Can you see what might be going on?
After the calculation to reassign MinQuantity, I would like to set LeadStart
as a null value. The Where part of the statement I would like to function for
when there is not a null value in leadstart and for when Inventory is greater
than MinQuantity.

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())
And T.LeadStart=""
WHERE (((T.LeadStart)<>[NZ] And T.Inventory>T.MinQuantity));

 

[[MVP] S.Clark]

In order to set LeadStart to Null after setting MinQty, you would have to
complete the first, then run a 2nd query to perform the nulling.

(T.LeadStart="") <> (T.LeadStart Is Null)

Empty string is not equal to Null

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[InventoryQueryGuy]

Ok thanks Steve, I didn't know that.

How can I go about only nulling those records where the update previously
took place because if i null the whole column my overall purpose of the query
will be lost!!

Cheers.

In order to set LeadStart to Null after setting MinQty, you would have to
complete the first, then run a 2nd query to perform the nulling.

(T.LeadStart="") <> (T.LeadStart Is Null)

Empty string is not equal to Null

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Can you see what might be going on?
After the calculation to reassign MinQuantity, I would like to set
LeadStart
as a null value. The Where part of the statement I would like to function
for
when there is not a null value in leadstart and for when Inventory is
greater
than MinQuantity.

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())
And T.LeadStart=""
WHERE (((T.LeadStart)<>[NZ] And T.Inventory>T.MinQuantity));