Update query that does not update 1

[access345]

I have the following query that does not update:

UPDATE TblPAQ3280 SET TblPAQ3280.InitialInspectionTT = [IntialInspectionTO]-[IntialInspectionTI]
WHERE (((TblPAQ3280.SerialNumber)=[Enter Serial Number]));

What I am trying to do is get the value of the record IntialInspectionTO and subtract it from the record IntialInspectionTI and place that value in IntialInspectionTT by the serial number

 

[AlexCuse]

Have you looked at the data type of your serial number field? Your query looks sound, although I'm not sure your calculation of total time is going to work.

Are your time in/time out fields a standard time or are they some kind of integer that will allow easy addition/subtraction?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[lespaul]

additionally, why store it in the table when you can calculate when you need it in a query?

Storing a calculated field breaks normalization rules, see fundamentals document below for more information on normalization.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[access345]

I have the number stored as hh:mm:ss. And If storing the calculated number breaks normalization rules would I store the formula to calculate total time in a report?

 

[AlexCuse]

A report or query would work well. Have a look at the DateDiff function for calculating the time difference.

HTH,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[access345]

I looked at the DateDiff function and this is what I came up with :

UPDATE TblPAQ3280 SET TblPAQ3280.InitialInspectionTT = DateDiff("h",[InitialInspectionTI],[InitialInspectionTO])
WHERE (((TblPAQ3280.SerialNumber)=[Enter Serial Number]));

I don't get an error when I execute this query , but the query returns a value of 00:00:00

 

[AlexCuse]

And what are the values in TimeIn/TimeOut?

If it ends up being under a minute, you may need to return minutes from DateDiff and divide by sixty to get fractions of an hour.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[access345]

Time In =9:51:31 Time Out =10:11:37

 

[AlexCuse]

have you tried returning minutes?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PHV]

And this ?
UPDATE TblPAQ3280
SET InitialInspectionTT = CDate([InitialInspectionTO]-[InitialInspectionTI])
WHERE SerialNumber = [Enter Serial Number]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

PHV you solved it!