Dear Kim:
First of all, does the query work correctly without the criteria? Please
try:
SELECT [qryBariatricCases-10CPTCodes-K].CASENUM,
[qryBariatricCases-10CPTCodes-K].Admit_Date,
[qryBariatricCases-10CPTCodes-K].DisCharge_Date,
[qryBariatricCases-10CPTCodes-K].CPT, [DisCharge_Date]-[Admit_Date] AS
[Length of stay]
FROM [qryBariatricCases-10CPTCodes-K]
ORDER BY [DisCharge_Date]-[Admit_Date] DESC;
Next, may I suggest (for the ease of my eyes) using an alias. This doesn't
change the query functionally. Or, better yet, since there's only one table
for the whole thing, no table references at all outside the FORM clause (I
know you didn't put them there, but Access did this for/to you). So now,
for my reference, we have:
SELECT CASENUM, Admit_Date, DisCharge_Date, CPT,
[DisCharge_Date]-[Admit_Date] AS [Length of stay]
FROM [qryBariatricCases-10CPTCodes-K]
WHERE ((([DisCharge_Date]-[Admit_Date])>=[Enter length of stay]))
ORDER BY [DisCharge_Date]-[Admit_Date] DESC;
We don't need all those parens (you didn't do it, Access did it to you):
SELECT CASENUM, Admit_Date, DisCharge_Date, CPT,
[DisCharge_Date] - [Admit_Date] AS [Length of stay]
FROM [qryBariatricCases-10CPTCodes-K]
WHERE [DisCharge_Date] - [Admit_Date] >= [Enter length of stay]
ORDER BY [DisCharge_Date] - [Admit_Date] DESC;
Now that's something worth my time to read! Whew! (Again, not your fault).
Now, one final note that is off the subject of your request. In making
column names for your table, you have done a good job in not putting spaces
or other junk inside (the "_" is fine!). But in aliasing the column [Length
of stay] you have broken that rule. May I please be forgiven for
recommending [LengthOfStay]?
Now for the meat. The columns [DisCharge_Date] and [Admit_Date]: of which
datatype are they? Are they Date/Time? Do they contain a time component in
addition to a date component?
Now, what do you think would be the value of [DisCharge_Date] - [Admit_Date]
when [DisCharge_Date] is January 9, 2006 at 3.30 PM and [Admit_Date] is
January 7, 2006 at 8:20 AM? How about 2 days, 7 hours, 10 minutes. Is that
a stay of 2 days? No it is more. But if it were from January 7, 2006 at
3:30 PM till January 9, 2006 at 8:20 AM it would be less than 2 days by 7
hours and 10 minutes. Is there a chance this is what is happening that
doesn't correspond to what you want?
Think about it and come back. How are we doing?
Tom Ellison
kimc said:
Here you go.
SELECT [qryBariatricCases-10CPTCodes-K].CASENUM,
[qryBariatricCases-10CPTCodes-K].Admit_Date,
[qryBariatricCases-10CPTCodes-K].DisCharge_Date,
[qryBariatricCases-10CPTCodes-K].CPT, [DisCharge_Date]-[Admit_Date] AS
[Length of stay]
FROM [qryBariatricCases-10CPTCodes-K]
WHERE ((([DisCharge_Date]-[Admit_Date])>=[Enter length of stay]))
ORDER BY [DisCharge_Date]-[Admit_Date] DESC;
Tom Ellison said:
Dear Kim:
To see what is going on, and to be able to reply in text with a query
that
will do what you want, please post the SQL View text of the query you
have,
in its entirety. Working from the Design View is limited, and does not
show
the context of all that is happening. Besides, I haven't worked in
Design
View in years, mostly for the same reasons I just stated.
Afterward, you will be able to past in the text of what I propose, and go
to
Design View and see just how the same thing would be done there. That
will
be just as instructive to you as to how it can be done there as giving
you a
much longer explanation here in this newsgroup.
Tom Ellison
kimc said:
Sorry, it's a weird problem. This is what the calculated field looks
like:
Length of Stay: [DisCharge_Date]-[Admit_Date]
When I try to put a parameter like >=[Enter length of stay]
it isn't giving me all my data when I enter a number. It seems like
such
a
simple parameter, I'm not sure what is going wrong.
Kim
:
Dear Kim:
It seems likely there is one fact wrong in your post.
"This new field then gives me a number."
I believe that, somehow, it is giving you a string. If you compare a
string
with a number, strange things happen.
If you will please post the code that does this, we can examine that
and
probably find where it is going wrong, and suggest a way to fix this.
Tom Ellison
I'm trying to make a simple parameter query. I have a calculated
field
that
subtracts two fields that contain dates. This new field then gives
me
a
number. This part works fine. When I try to use a parameter like
=[Enter
length of stay] it works fine, until I type in the number 10 in the
parameter. When I use the number 10, it shows the results for
numbers
less
than 10. When I type in a single digit number, it works fine. What
could
I
be doing wrong? It seems so simple so I'm not sure what's the
problem.
Many thanks!
KimC
