DC Date

[LMB]

Using Access 2000. We have a flat database with patients, therapies etc... (I didn't make it but am the only one left working in our department who knows or is interested in how to do anything with access). I am really sorry but it's going to stay that way because it will soon be replaced by a new system the hospital is purchasing but for now I have to make a change. Right now I have this query.

SELECT [RT-FLOW].[RM#], [RT-FLOW].[BD#], [RT-FLOW].[LAST NAME], [RT-FLOW].THERAPY, [RT-FLOW].FREQUENCY, [RT-FLOW].[ORDER DATE], [Order Date]+3 AS [Tag Date], [RT-FLOW].[Order Time]
FROM [RT-FLOW] WHERE ((([Order Date]+3)=[Enter Date in */**/**** Format]));

Every day the supervisor checks to see who is in this query for today and "tags" the chart. For the past 15 years it has been ok to work with just tagging by date but now we have to tag at exactly 5 days to the hour. So I understand I need to add a "time ordered" field but I don't know how to get the time and date to calculate exactly 5 days or is there a fieldtype that has date and time together? I looked at the format properties for time/date in my table and general date shows 6/19/1994 5:24:23 PM.................Would this be what I would use then make a query?



Thanks,
Linda

 

[John Vinson]

Using Access 2000. We have a flat database with patients, therapies etc... (I didn't make it but am the only one left working in our department who knows or is interested in how to do anything with access). I am really sorry but it's going to stay that way because it will soon be replaced by a new system the hospital is purchasing but for now I have to make a change. Right now I have this query.

SELECT [RT-FLOW].[RM#], [RT-FLOW].[BD#], [RT-FLOW].[LAST NAME], [RT-FLOW].THERAPY, [RT-FLOW].FREQUENCY, [RT-FLOW].[ORDER DATE], [Order Date]+3 AS [Tag Date], [RT-FLOW].[Order Time]
FROM [RT-FLOW] WHERE ((([Order Date]+3)=[Enter Date in */**/**** Format]));

Every day the supervisor checks to see who is in this query for today and "tags" the chart. For the past 15 years it has been ok to work with just tagging by date but now we have to tag at exactly 5 days to the hour. So I understand I need to add a "time ordered" field but I don't know how to get the time and date to calculate exactly 5 days or is there a fieldtype that has date and time together? I looked at the format properties for time/date in my table and general date shows 6/19/1994 5:24:23 PM.................Would this be what I would use then make a query?

A Date/Time field (regardless of format) is actually stored as a
number - a count of days and fractions of a day (times). All
date/times have date and time together, by their nature; you can't
separate them.

Could you explain what you mean by "tag at exactly 5 days to the
hour"? What is the starting point ([Order Date])? What time on that
date? What do you want to return? All records whose Order Date field
is 5 days 0 hours 0 minutes and 0 seconds earlier than the date/time
entered? All records previous to that time?

Please explain.

John W. Vinson[MVP]

 

[LMB]

Using Access 2000. We have a flat database with patients, therapies etc... (I didn't make it but am the only one left working in our department who knows or is interested in how to do anything with access). I am really sorry but it's going to stay that way because it will soon be replaced by a new system the hospital is purchasing but for now I have to make a change. Right now I have this query.

SELECT [RT-FLOW].[RM#], [RT-FLOW].[BD#], [RT-FLOW].[LAST NAME], [RT-FLOW].THERAPY, [RT-FLOW].FREQUENCY, [RT-FLOW].[ORDER DATE], [Order Date]+3 AS [Tag Date], [RT-FLOW].[Order Time]
FROM [RT-FLOW] WHERE ((([Order Date]+3)=[Enter Date in */**/**** Format]));

Every day the supervisor checks to see who is in this query for today and "tags" the chart. For the past 15 years it has been ok to work with just tagging by date but now we have to tag at exactly 5 days to the hour. So I understand I need to add a "time ordered" field but I don't know how to get the time and date to calculate exactly 5 days or is there a fieldtype that has date and time together? I looked at the format properties for time/date in my table and general date shows 6/19/1994 5:24:23 PM.................Would this be what I would use then make a query?

A Date/Time field (regardless of format) is actually stored as a
number - a count of days and fractions of a day (times). All
date/times have date and time together, by their nature; you can't
separate them.

Could you explain what you mean by "tag at exactly 5 days to the
hour"? What is the starting point ([Order Date])? What time on that
date? What do you want to return? All records whose Order Date field
is 5 days 0 hours 0 minutes and 0 seconds earlier than the date/time
entered? All records previous to that time?

Please explain.

John W. Vinson[MVP]

Hi John,

Yes, ([order date]) is the starting point. After writing all of this out, I think all I need is a time field added to the table and then added to the query and instead of +3 I'll use + 4 in my equation and the date field will still be used to identify who the list of names is....but I've posted my explanation the best I can below.

Tag date is the date the chart is tagged and the doctor is reminded that the therapy will be discontinued in 24 hours if he doesn't re-order. If the treatment was ordered on 1/1/05 at 2:00 am, it will be tagged on 1/5/05 at 2:00 am then discontinued on 1/6/05 at 2:00 am if there is no re-order. So I need a query that the supervisor can run for each day which will list all the patients names who are due to be tagged from midnight to midnight which will list exactly 5 days to the minute the pt. name, date and time the tag should be placed.

I'll base a label report on the query
and it will say something like this..
Dear Doctor, So and So's Aerosol
treatments will expire on 1/6/05 at 2:00 am.
If you would So and So to continue receiving
this therapy, please write an order to continue this therapy.

Thanks,
Linda

 

[John Vinson]

Tag date is the date the chart is tagged and the doctor is reminded that the therapy will be discontinued in 24 hours if he doesn't re-order. If the treatment was ordered on 1/1/05 at 2:00 am, it will be tagged on 1/5/05 at 2:00 am then discontinued on 1/6/05 at 2:00 am if there is no re-order. So I need a query that the supervisor can run for each day which will list all the patients names who are due to be tagged from midnight to midnight which will list exactly 5 days to the minute the pt. name, date and time the tag should be placed.

I'll base a label report on the query
and it will say something like this..
Dear Doctor, So and So's Aerosol
treatments will expire on 1/6/05 at 2:00 am.
If you would So and So to continue receiving
this therapy, please write an order to continue this therapy.

Your Order Date field should be changed to an OrderDateTime field, I'd
suggest; so it would actually contain #1/1/05 02:00am#.

On your Report you could have a textbox with a Control Source of

=DateAdd("h", 120, [OrderDateTime])

with a Format property of "m/dd/yy \a\t hh:nn am/pm"

to automatically calculate the expiration date and time 120 hours
(five days) after the stored time.

John W. Vinson[MVP]

 

[LMB]

Your Order Date field should be changed to an OrderDateTime field, I'd
suggest; so it would actually contain #1/1/05 02:00am#.

On your Report you could have a textbox with a Control Source of

=DateAdd("h", 120, [OrderDateTime])

with a Format property of "m/dd/yy \a\t hh:nn am/pm"

to automatically calculate the expiration date and time 120 hours
(five days) after the stored time.

John W. Vinson[MVP]

John,

Does access automatically enter the time behind the scenes. I changed to a general date format from a short date format in the table but I don't see the time in the field, only the date when I add a new record. How will we enter the time in?

Thanks,
Linda

 

[John Vinson]

Does access automatically enter the time behind the scenes. I changed to a general date format from a short date format in the table but I don't see the time in the field, only the date when I add a new record. How will we enter the time in?

A "General Date" format will automatically put in a time of midnight,
and suppress the display of the time portion. If you type in

2/4/04 11:20

then that's what you'll get, and you will see the time.

A Date/Time value is actually stored as a Double Float number, a count
of days and fractions of a day since midnight, December 30, 1899. It
can be formatted any way you like - with the named formats such as
"General Date" or "Long Date", or you can use a custom format such as
dd-mmm-yyyy hh:nn to see "04-Feb-2004 11:20".

If you set the Default property of a date/time field to Date() it will
enter today's date with a zero fractional portion, that is, midnight
at the beginning of that day. If you default it to Now() it will store
the current date and time from your system clock.

John W. Vinson[MVP]

 

[LMB]

Thanks John. I think I have it.

Linda

Does access automatically enter the time behind the scenes. I changed to a general date format from a short date format in the table but I don't see the time in the field, only the date when I add a new record. How will we enter the time in?

A "General Date" format will automatically put in a time of midnight,
and suppress the display of the time portion. If you type in

2/4/04 11:20

then that's what you'll get, and you will see the time.

A Date/Time value is actually stored as a Double Float number, a count
of days and fractions of a day since midnight, December 30, 1899. It
can be formatted any way you like - with the named formats such as
"General Date" or "Long Date", or you can use a custom format such as
dd-mmm-yyyy hh:nn to see "04-Feb-2004 11:20".

If you set the Default property of a date/time field to Date() it will
enter today's date with a zero fractional portion, that is, midnight
at the beginning of that day. If you default it to Now() it will store
the current date and time from your system clock.

John W. Vinson[MVP]