Update specific record based on max date and max time.

[InventoryQueryGuy]

I have created a query that finds a record based on max date and max time,
where the "signoutQuantity" is subtracted from the "Inventory" list accross
two different tables. I know this is not recommended but it is the only way I
can go about this project.
It seems as though this query works on and off, sometimes updating 1 row (as
needed) and sometimes updating 0 rows (useless!). Any suggestions as to why?
I know its not that i'm entering invalid data so there must be some other
underlying cause. Included below are my two queries:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.Inventory =
T.Inventory+S.ReturnQuantity
WHERE S.[DateModified] = (SELECT MAX([DateModified]) FROM ToolCribSignOut)
and S.[TimeModified] = (SELECT MAX([TimeModified]) FROM ToolCribSignOut);

and:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.Inventory =
T.Inventory-S.SignOutQuantity
WHERE S.[DateModified] = (SELECT MAX([DateModified]) FROM ToolCribSignOut)
and S.[TimeModified] = (SELECT MAX([TimeModified]) FROM ToolCribSignOut);


The queries don't both run, they are dependant on what ever form is being
filled out, then run after the form is closed so the record is saved.

 

[KARL DEWEY]

I think you may have a problem in having a separate field for the time from
the date. You should have a single DateTime field for your check-in.