Wrong date/time format - can't query

[RipperT]

I have a table that is written to programmatically that has fields for
INDATE and OUTDATE. In my stupidity, the line of code that records the date
for these fields is: !INDATE = Now, instead of: !INDATE = Date
The date is recorded in General format a la: 1/12/2008 3:01:57 PM. AFAIK,
there is no way to query the table for a days worth of records, unless the
query asks for them down to the second, which is impossible. Is there some
way to write the query that will extract based on only the days in either of
these fields? Also, can I write an update query or some code that will
truncate the time off these fields to leave only the date? (There are about
5000 records). If I can accomplish this, I plan to create separate fields
for the time as I should have in the first place. Live and learn!

Rip

 

[John W. Vinson]

I have a table that is written to programmatically that has fields for
INDATE and OUTDATE. In my stupidity, the line of code that records the date
for these fields is: !INDATE = Now, instead of: !INDATE = Date

Well, both are useful at times.

The date is recorded in General format a la: 1/12/2008 3:01:57 PM.

No, it's not. It's RECORDED as a Date/Time value, 39459.6263541667 to be
exact, since that time is that many days after midnight, December 30, 1899 -
the base point for dates. The format just controls what's *displayed*, not
wht's stored.

AFAIK,
there is no way to query the table for a days worth of records, unless the
query asks for them down to the second, which is impossible. Is there some
way to write the query that will extract based on only the days in either of
these fields?
Sure:

= [Enter date:] AND < DateAdd("d", 1, [Enter date:]

as a criterion on Indate will get all records during that day.

Also, can I write an update query or some code that will
truncate the time off these fields to leave only the date? (There are about
5000 records).

Yes; you can update the field to Datevalue([Indate]). Don't do it though, see
below.

If I can accomplish this, I plan to create separate fields
for the time as I should have in the first place. Live and learn!

I'd disagree. If you need both the date and time, keep them in the SAME field.
You can display just the date or just the time as needed; you can easily
search using the technique above, or using other of the builtin date/time
functions. If you store them separately, you'll have one field actually
containing 39459.00000000000, corresponding to #1/12/2008 12:00:00AM#, and
another field containing 0.6263541667, corresponding to #12/30/1899
3:01:57PM#. Sorting times or dates into order will be *SIMPLER* if you keep
the data in one field; you just need to learn how to work with them as a
combined point in time.

 

[RipperT]

Obviously, I need to learn not to assume so much! Many thanx.

R.

I have a table that is written to programmatically that has fields for
INDATE and OUTDATE. In my stupidity, the line of code that records the
date
for these fields is: !INDATE = Now, instead of: !INDATE = Date

Well, both are useful at times.

The date is recorded in General format a la: 1/12/2008 3:01:57 PM.

No, it's not. It's RECORDED as a Date/Time value, 39459.6263541667 to be
exact, since that time is that many days after midnight, December 30,
1899 -
the base point for dates. The format just controls what's *displayed*, not
wht's stored.

AFAIK,
there is no way to query the table for a days worth of records, unless the
query asks for them down to the second, which is impossible. Is there some
way to write the query that will extract based on only the days in either
of
these fields?
Sure:

= [Enter date:] AND < DateAdd("d", 1, [Enter date:]

as a criterion on Indate will get all records during that day.

Also, can I write an update query or some code that will
truncate the time off these fields to leave only the date? (There are
about
5000 records).

Yes; you can update the field to Datevalue([Indate]). Don't do it though,
see
below.

If I can accomplish this, I plan to create separate fields
for the time as I should have in the first place. Live and learn!

I'd disagree. If you need both the date and time, keep them in the SAME
field.
You can display just the date or just the time as needed; you can easily
search using the technique above, or using other of the builtin date/time
functions. If you store them separately, you'll have one field actually
containing 39459.00000000000, corresponding to #1/12/2008 12:00:00AM#, and
another field containing 0.6263541667, corresponding to #12/30/1899
3:01:57PM#. Sorting times or dates into order will be *SIMPLER* if you
keep
the data in one field; you just need to learn how to work with them as a
combined point in time.

 

[RipperT]

Here is what I have; it does not return anything:

SELECT tblInmates.LstName, tblLockHistory.InmateId,
tblLockHistory.DateTimeIn, tblLockHistory.DateTimeOut,
tblLockHistory.HousingUnit, tblLockHistory.CellNo, tblLockHistory.Bunk,
tblLockHistory.InUser, tblLockHistory.OutUser
FROM tblInmates INNER JOIN tblLockHistory ON tblInmates.InmateId =
tblLockHistory.InmateId
WHERE (((tblLockHistory.DateTimeIn)=[Enter date:] And
(tblLockHistory.DateTimeIn)<DateAdd("d",1,[Enter date:])));

If I put in an exact date & time (cut and paste from the table), then it
returns that record, but if I enter just a date that I know exists in the
table, it returns nothing. Does it need tweaking?

Thanks,

Rip

I have a table that is written to programmatically that has fields for
INDATE and OUTDATE. In my stupidity, the line of code that records the
date
for these fields is: !INDATE = Now, instead of: !INDATE = Date

Well, both are useful at times.

The date is recorded in General format a la: 1/12/2008 3:01:57 PM.

No, it's not. It's RECORDED as a Date/Time value, 39459.6263541667 to be
exact, since that time is that many days after midnight, December 30,
1899 -
the base point for dates. The format just controls what's *displayed*, not
wht's stored.

AFAIK,
there is no way to query the table for a days worth of records, unless the
query asks for them down to the second, which is impossible. Is there some
way to write the query that will extract based on only the days in either
of
these fields?
Sure:

= [Enter date:] AND < DateAdd("d", 1, [Enter date:]

as a criterion on Indate will get all records during that day.

Also, can I write an update query or some code that will
truncate the time off these fields to leave only the date? (There are
about
5000 records).

Yes; you can update the field to Datevalue([Indate]). Don't do it though,
see
below.

If I can accomplish this, I plan to create separate fields
for the time as I should have in the first place. Live and learn!

I'd disagree. If you need both the date and time, keep them in the SAME
field.
You can display just the date or just the time as needed; you can easily
search using the technique above, or using other of the builtin date/time
functions. If you store them separately, you'll have one field actually
containing 39459.00000000000, corresponding to #1/12/2008 12:00:00AM#, and
another field containing 0.6263541667, corresponding to #12/30/1899
3:01:57PM#. Sorting times or dates into order will be *SIMPLER* if you
keep
the data in one field; you just need to learn how to work with them as a
combined point in time.

 

[John W. Vinson]

If I put in an exact date & time (cut and paste from the table), then it
returns that record, but if I enter just a date that I know exists in the
table, it returns nothing. Does it need tweaking?

Yes. As written the DateTimeIn must exactly match the date entered with the
Enter Date: prompt, since you're using = as the operator. Just change the =
(exactly equal) to >= (greater than or equal) so that any time *BETWEEN*
midnight at the start of the day and midnight the next day will be found (as I
suggested in my original reply).

SELECT tblInmates.LstName, tblLockHistory.InmateId,
tblLockHistory.DateTimeIn, tblLockHistory.DateTimeOut,
tblLockHistory.HousingUnit, tblLockHistory.CellNo, tblLockHistory.Bunk,
tblLockHistory.InUser, tblLockHistory.OutUser
FROM tblInmates INNER JOIN tblLockHistory ON tblInmates.InmateId =
tblLockHistory.InmateId
WHERE (((tblLockHistory.DateTimeIn) >= [Enter date:] And
(tblLockHistory.DateTimeIn)<DateAdd("d",1,[Enter date:])));

 

[RipperT]

Many thanks.

R

If I put in an exact date & time (cut and paste from the table), then it
returns that record, but if I enter just a date that I know exists in the
table, it returns nothing. Does it need tweaking?

Yes. As written the DateTimeIn must exactly match the date entered with
the
Enter Date: prompt, since you're using = as the operator. Just change the
=
(exactly equal) to >= (greater than or equal) so that any time *BETWEEN*
midnight at the start of the day and midnight the next day will be found
(as I
suggested in my original reply).

SELECT tblInmates.LstName, tblLockHistory.InmateId,
tblLockHistory.DateTimeIn, tblLockHistory.DateTimeOut,
tblLockHistory.HousingUnit, tblLockHistory.CellNo, tblLockHistory.Bunk,
tblLockHistory.InUser, tblLockHistory.OutUser
FROM tblInmates INNER JOIN tblLockHistory ON tblInmates.InmateId =
tblLockHistory.InmateId
WHERE (((tblLockHistory.DateTimeIn) >= [Enter date:] And
(tblLockHistory.DateTimeIn)<DateAdd("d",1,[Enter date:])));