Dear John,
Well, when it goes BLOTTO, I'd be willing to try just about anything. Never
seen that be a problem before. Doesn't it just assign some dumb thing for a
column name? Never paid much attention to that, I just say, "Oh yeah, I
screwed up and forgot to alias the calculated column." Then I go fix it.
But to error on that? Not in my experience. But it wouldn't take much
effort to find out. There's a first time for everything. I vote to go for
it!
For Simon, this means to try:
SELECT [Arrival at participating hospital date],
[Arrival at participating hospital time],
Format([Arrival at participating hospital date] +
[Arrival at participating hospital time],
"mm/dd/yyyy hh:mm:ss") AS XXX
FROM [Inclusion Criteria]
Please let us know if this changes anything. Thanks!
John, by all means, look in on my stuff, and speak your mind. I'll be kind
in return. I don't expect anything adverse from you, and won't be offended
unless you try really, really hard!
Tom Ellison
John Spencer said:
Tom,
Is it possible that the lack of an alias on the calculated field could be
causing the malfunction?
Spencer
(No, I'm not stalking you. I tend to follow your postings, since I often
learn something)
Tom Ellison said:
Dear Simon:
I repeat your query, formated for my reading preference:
SELECT [Arrival at participating hospital date],
[Arrival at participating hospital time],
Format([Arrival at participating hospital date] +
[Arrival at participating hospital time],
"mm/dd/yyyy hh:mm:ss")
FROM [Inclusion Criteria]
There's no subquery here. Your system has gone BLOTTO. Seriously
WHACKED.
OK, I've had my tantrum now.
Please pare it down and find the piece that is offending. Also, try it
on a different computer if possible.
For example:
SELECT [Arrival at participating hospital date],
[Arrival at participating hospital time],
[Arrival at participating hospital date] +
[Arrival at participating hospital time]
FROM [Inclusion Criteria]
Tom Ellison
Tom,
The following is what I typed into the query:
SELECT [Arrival at participating hospital date], [Arrival at
participating
hospital time], Format([Arrival at participating hospital date] +
[Arrival at
participating hospital time], "mm/dd/yyyy hh:mm:ss")
FROM [Inclusion Criteria]
I get a syntax error, so I know I am doing something wrong, I just don't
know what.
The error states "the syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses.
Thanks a lot.
Simon
:
Dear Simon:
The first and essential step is to find out how the date and time
portions
can be combined. If they are done the way I suspect, you can just add
them
together. Test this with a query like this:
SELECT SomeDt, SomeTm, Format(SomeDt + SomeTm, "mm/dd/yyyy hh:mm:ss")
FROM YourTable
Do these look correct?
Use your actual table and column names for SomeDt, SomeTm, and
YourTable.
Tom Ellison
Tom,
Thank you very much for clearing that up. According to your
suggestion,
how
would I write the query to do what you suggest?
Thanks,
Simon
:
Dear Simon:
It is usually a serious mistake to store date and time in separate
columns.
You will have to reassemble it every time you want to do serious
work.
In calculating intervals, the DateDiff function is invaluable.
Please
refer
to the online help to read up on that.
The interval between 11:59 PM one day and 12:01 AM the next day is
two
minutes. The point is, the interval is very much a function of both
date
and time. In almost every case, keeping them together is essential
to
being
able to use them.
You could temporarily build a query that reassembles these pairs of
columns
into one, and use that query to access this data. Such a solution
then
enables you to use the rich set of date/time functions to perform
all
sorts
of important work. In the long run, revamping the mis-constructed
tables
is
the more efficient solution.
In this case, it is almost always easier to deconstruct the value
into
separate date and time when desired than it is to be constantly
reconstructing them.
Tom Ellison
I have two dates with respective times. The first date being [Onset
date
of
ischemic symptoms] with a time field of [Onset time of ischemic
symptoms].
I
then have another date called [Arrival at participating Hospital
date]
with a
time field of [Arrival at participating Hospital time]. The date
and
time
of
onset occur first, followed by the date and time of arrival.
For example:
[Onset date of ischemic symptoms] is 10/20/2005 and [Onset time
of
ischemic
symptoms] is 9:30
[Arrival at participating Hospital date] is 10/20/2005 and
[Arrival at
participating Hospital time] is 11:30.
I need to calculate the difference between them. I have been using
the
following format until now:
Time Difference: Format([Arrival at participating Hospital
time]-[Onset
time
of ischemic symptoms],"Short Time")
Using the above mentioned format, I get a difference between the
above
mentioned times of 2:00, which is correct.
However, because sometimes the two times are on different days, I
get
skewed
results. For example:
[Onset date of ischemic symptoms] is 5/2/2005 and [Onse time of
ischemic
symptoms] is 22:00
[Arrival at participating Hospital date] is 5/3/2005 and [Arrival
at
participating Hospital time] is 18:13.
The result I get is 3:47, when the real result should be 20:13.
I need the difference to be given in the number of hours between
the
two.
Thank you.
