Help with time difference query

[red skelton via AccessMonster.com]

Hi everyone,
Can anyone help me? I have a query that is giving me a problem and I cant
figure out why. I am using the same DateDiff format three times in the query
and it only works two of the three times.

My fields look like this;

OR Scheduled Time OR Start Time Time in OR Surgery Start

0700 0650 0730
0830

Im using the datediff expression DateDiff("n",[OR Scheduled Time],[OR Start]
) but receiving -1007590 from my query. The same DateDiff exp works fine
between (OR Start Time - Time in OR) and Time in OR - Surgery Start). I dont
know if it is because the OR Scheduled Time is sometimes later then the OR
Start time but even when its not, I still get a bad number in this field.
Any other infromation I'm leaving out, please let me know. Any help would
be greatly appreciated.

VR,

Red

 

[John Spencer]

I suspect that you have a date and time stored in one of the fields instead
of just the time. Or perhaps it is the other way around, all the fields but
one have a date and time stored in the field..

Try the formula
DateDiff("n",TimeValue([OR Scheduled Time]),TimeValue([OR Start Time]))

That will strip off the date component if it exists.

 

[red skelton via AccessMonster.com]

John,
Thanks for your quick response. Doing some checking into the table and you
are absolutly right with your assumption, the OR Scheduled Time is a text
field. The problem is, I cant change this field to a Date/Time field as
there is txt entered into this field. For example, the first instance that a
time is entered into this field is 0700. If this room is scheduled to be
used mutiple times during the day, the next entry will be TF1 because there
is not a hard time that the room can be used again since the first procedure
might take longer then expected so the TF1 is a placeholder for the next
procedure scheduled into the room after the first one is done. (I hope this
make sense) . When I use the DateDiff you entered below, all I get is Error
in the field. I suspect that it is because it is a txt field vs Date/Time
field. Any suggestions on how to make it work?

Thanks for your help.

Red

I suspect that you have a date and time stored in one of the fields instead
of just the time. Or perhaps it is the other way around, all the fields but
one have a date and time stored in the field..

Try the formula
DateDiff("n",TimeValue([OR Scheduled Time]),TimeValue([OR Start Time]))

That will strip off the date component if it exists.

Hi everyone,
Can anyone help me? I have a query that is giving me a problem and I cant

[quoted text clipped - 24 lines]

Red

 

[John Spencer]

TimeValue should convert a text string to a time, BUT it can't do that if
the text string doesn't conform to one of the accepted patterns for time.
That means you need a colon separator and if you aren't using a 24 hour
clock times in the afternoon/evening must have PM appended to the end.

You can make the expression a bit more complicated by using the IsDate
function to check the field and then if it has a valid datetime do the
calculation.

IIF(IsDate([OR Scheduled Time]),DateDiff("n",TimeValue([OR Scheduled
Time]),TimeValue([OR Start Time])), Null)

That will return the time difference in minutes unless if [OR Scheduled
Time] can be interpreted as a datetime. Otherwise it returns Null (Blank).
If you want Zero returned instead then type 0 in place of the Null.

Your example had "0700" as the time. This cannot be interpreted as a time
while "07:00" can be (7:00 AM).

You will have to solve the data entry problem or put something in place to
handle times without a colon. That gets more complex as you will to check
the length of the input, the presence of 1 colon or 2 colons if someone
decides to enter the seconds, etc.

My preference would be to make [OR Scheduled Time] a datetime field and ADD
another field to hold the "non-time" values. Then you enter data into one
or the other depending on the situation.

John,
Thanks for your quick response. Doing some checking into the table and
you
are absolutly right with your assumption, the OR Scheduled Time is a text
field. The problem is, I cant change this field to a Date/Time field as
there is txt entered into this field. For example, the first instance
that a
time is entered into this field is 0700. If this room is scheduled to be
used mutiple times during the day, the next entry will be TF1 because
there
is not a hard time that the room can be used again since the first
procedure
might take longer then expected so the TF1 is a placeholder for the next
procedure scheduled into the room after the first one is done. (I hope
this
make sense) . When I use the DateDiff you entered below, all I get is
Error
in the field. I suspect that it is because it is a txt field vs Date/Time
field. Any suggestions on how to make it work?

Thanks for your help.

Red

I suspect that you have a date and time stored in one of the fields
instead
of just the time. Or perhaps it is the other way around, all the fields
but
one have a date and time stored in the field..

Try the formula
DateDiff("n",TimeValue([OR Scheduled Time]),TimeValue([OR Start Time]))

That will strip off the date component if it exists.

Hi everyone,
Can anyone help me? I have a query that is giving me a problem and I
cant

[quoted text clipped - 24 lines]

Red

 

[red skelton via AccessMonster.com]

John, When you said 0700 wasn't a recognized time format, all I needed to do
was change my time to reflect the colon and the query works fine.

Thanks Again,
Red

TimeValue should convert a text string to a time, BUT it can't do that if
the text string doesn't conform to one of the accepted patterns for time.
That means you need a colon separator and if you aren't using a 24 hour
clock times in the afternoon/evening must have PM appended to the end.

You can make the expression a bit more complicated by using the IsDate
function to check the field and then if it has a valid datetime do the
calculation.

IIF(IsDate([OR Scheduled Time]),DateDiff("n",TimeValue([OR Scheduled
Time]),TimeValue([OR Start Time])), Null)

That will return the time difference in minutes unless if [OR Scheduled
Time] can be interpreted as a datetime. Otherwise it returns Null (Blank).
If you want Zero returned instead then type 0 in place of the Null.

Your example had "0700" as the time. This cannot be interpreted as a time
while "07:00" can be (7:00 AM).

You will have to solve the data entry problem or put something in place to
handle times without a colon. That gets more complex as you will to check
the length of the input, the presence of 1 colon or 2 colons if someone
decides to enter the seconds, etc.

My preference would be to make [OR Scheduled Time] a datetime field and ADD
another field to hold the "non-time" values. Then you enter data into one
or the other depending on the situation.

John,
Thanks for your quick response. Doing some checking into the table and

[quoted text clipped - 37 lines]