time fields in query

  • Thread starter Thread starter bindurajeesh
  • Start date Start date
B

bindurajeesh

How does one write update and/or delete and/or append queries using data in
time format when the field in the access table is date/time with medium time?
 
How does one write update and/or delete and/or append queries using data in
time format when the field in the access table is date/time with medium time?
Click to expand...

The format is *absolutely irrelevant* to what's stored.

A Date/Time value is stored as a Double Float count of days and fractions of a
day (times) since midnight, December 30, 1899. <checking>

5/16/2008 6:34:01 PM = 39584.7736226852

Your table field might contain just a date - e.g. 39584.000000000 is 5/16/08;
or it might just contain a time, e.g. 0.7736226852 is 6:34:01 PM, actually on
December 30, 1899; it might have both, but you can set the format to display
just the date, just the time, or both. You can even have the same table value
displayed with different formats, say in multiple textboxes on a form or
report.

What is the source of your time value? What is the nature of the data in your
table? Do you want to store the current time (which implies the current date
as well), or a generic time such as 5:00pm (date not specified, therefore
actually stored as 12/30/1899)?
 
I have created a dao recordset which is a select query of records from the
table that I want to update, delete, and append. from that recordset i
capture the time from two fields, timein and timeout and then place that data
in variables to use for comparison. once i find the record that i want to
delete i have the code run a delete query but because the variable holds
10:15:00 AM it can not find the record in the table to delete because the
table has data that is as follows 10:15 AM. Also my update query does not
update and my append does not append for same reasons. Should I cint the
data in the time variable and then use that in the queries? I would give you
actual code but I am home and the code is at work.
 
I have created a dao recordset which is a select query of records from the
table that I want to update, delete, and append. from that recordset i
capture the time from two fields, timein and timeout and then place that data
in variables to use for comparison. once i find the record that i want to
delete i have the code run a delete query but because the variable holds
10:15:00 AM it can not find the record in the table to delete because the
table has data that is as follows 10:15 AM. Also my update query does not
update and my append does not append for same reasons. Should I cint the
data in the time variable and then use that in the queries? I would give you
actual code but I am home and the code is at work.
Click to expand...

If you capture the time into a value dim'd as a Variant, you should be able to
find it by searching for that value. If you are running the search by
converting the value to String you may get a mismatch, because the stored time
value might be a few microseconds off - you can only search to the second.

What are the criteria in your update and delete queries? It might be best if
you did in fact post your actual code.
 
Back
Top