dates

G

Georgie047

This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
 
K

KARL DEWEY

In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=#1/1/2008# <=#6/1/2008#
Click to expand...
--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
 
J

John Spencer

TotalDays:
DateDiff("d",[Admission Date],[Discharge Date])

TotalDays between Admission Date and End of Period or Discharge Date
which ever comes first

DateDiff("d",[Admission Date],
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date])

Between Admission date or Begin Date and Discharge Date or End Date
depending on whether or not Admission Date is after Begin Date and
Discharge Date is before End Date.

DateDiff("d",
IIF([Admission Date] is Null or
[Admission Date] < [forms]![review forms]![begin date]
,[forms]![review forms]![begin date], [Admission Date])
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date])



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
G

Georgie047

to answer back quickly to your question, I want it to tell me the total
number of days occupied from 1/1/2000 and 6/1/2008 (and it does) but then I
want it to tell me the total number of days was occupied from
1/1/2008-6/1/2008 , this year only,(and this does not work). I am in the
meantime trying your suggestion
--
Thanks, Linda


bhicks11 via AccessMonster.com said:
Works for me (replace misc with your table name):

SELECT misc.Admiss, misc.Discharge, NZ([misc].[discharge],Date())-[misc].
[admiss] AS total INTO misc
FROM misc;

What is the trouble you are having with it?

Bonnie
http://www.dataplus-svc.com

This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
 
G

Georgie047

Karl, these date change how will that work? Thanks for answering
--
Thanks, Linda


KARL DEWEY said:
In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=#1/1/2008# <=#6/1/2008#
Click to expand...
--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
 
G

Georgie047

John, do I use all of the expressions or did you give me expressions for each
subject. I think the last one will be my answer, I will try it.
--
Thanks, Linda


John Spencer said:
TotalDays:
DateDiff("d",[Admission Date],[Discharge Date])

TotalDays between Admission Date and End of Period or Discharge Date
which ever comes first

DateDiff("d",[Admission Date],
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date])

Between Admission date or Begin Date and Discharge Date or End Date
depending on whether or not Admission Date is after Begin Date and
Discharge Date is before End Date.

DateDiff("d",
IIF([Admission Date] is Null or
[Admission Date] < [forms]![review forms]![begin date]
,[forms]![review forms]![begin date], [Admission Date])
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date])



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
 
K

KARL DEWEY

In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=CVDate([Enter start date]) <=CVDate([Enter end date])
Click to expand...

--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
Karl, these date change how will that work? Thanks for answering
--
Thanks, Linda


KARL DEWEY said:
In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=#1/1/2008# <=#6/1/2008#
Click to expand...
--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
Click to expand...
 
G

Georgie047

Karl this works, somewhat. When I put in the start date and end date I get
the results of those days. What I am triying to get is Example: a resident
is admitted 1/1/2000 and stays until 6/1/2008. I need two answers.
1. How long was he there from 1/1/2000 to 6/1/2008? THIS WORKS
2. How long was he there from 1/1/2008 to 9/1/2008. this year THIS DOES NOT
WORK
-- Thanks for your help
Thanks, Linda


KARL DEWEY said:
In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=CVDate([Enter start date]) <=CVDate([Enter end date])
Click to expand...

--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
Karl, these date change how will that work? Thanks for answering
--
Thanks, Linda


KARL DEWEY said:
In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=#1/1/2008# <=#6/1/2008#
--
KARL DEWEY
Build a little - Test a little


:

This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I gave you several different expressions, since I was not sure what you
wanted.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
K

KARL DEWEY

To do what you want will require entering three dates. How will you express
them?

Post your SQL that is working.

--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
Karl this works, somewhat. When I put in the start date and end date I get
the results of those days. What I am triying to get is Example: a resident
is admitted 1/1/2000 and stays until 6/1/2008. I need two answers.
1. How long was he there from 1/1/2000 to 6/1/2008? THIS WORKS
2. How long was he there from 1/1/2008 to 9/1/2008. this year THIS DOES NOT
WORK
-- Thanks for your help
Thanks, Linda


KARL DEWEY said:
In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=CVDate([Enter start date]) <=CVDate([Enter end date])
Click to expand...

--
KARL DEWEY
Build a little - Test a little


Georgie047 said:
Karl, these date change how will that work? Thanks for answering
--
Thanks, Linda


:

In design view these fields and criteria --
Admission date Discharge date Total Days: [Discharge date]-[Admission
date]+1
=#1/1/2008# <=#6/1/2008#
--
KARL DEWEY
Build a little - Test a little


:

This is a hard one. I have been struggling for a while PLEASE PLEASE HELP
I have a query that has two fields admission date and discharge date. I
have a criteria that searches the dates: between begin date and end date.
Here is an example:
Admission date: 1/1/2000
Discharge date: 6/1/2008
Between begin date 1/1/2008 and end date 6/1/2008
What I want it to do is tell me the number of days occupied giving me the
accumalated total days (and I need that) 1/1/2000 through 6/1/2008 (and it
works)
Problem: I also need a way that I can have it give me just the total days
occupied from 1/1/2008 to 6/1/2008.
here is my query
field: admission date
criteria first row: between[forms]![review forms]![begin date] and
[forms]![review forms]![end date]
criteris second row: Is null

field: Total days occupied: NZ([discharge date],Date ())-[admission date]
Click to expand...
Click to expand...
Click to expand...
 
L

Lord Kelvan

i think he wants something like

field criteria
Admission date
Discharge date year([discharge date]) = year(date()) or is null
Total Days: [Discharge date]-[Admission date]
total days in year: iif([Discharge date] is null, Date()-
CDate(("01/01/" & Year(Date()))),[discharge date]-CDate(("01/01/" &
Year(Date()))))

where total days is TOTAL DAYS reagrdless and total days in year is
the total days in this year

sql would look something like

select [admission date],[discharge date],[Discharge date]-[Admission
date] as [total days], iif([Discharge date] is null, Date()-
CDate(("01/01/" & Year(Date()))),[discharge date]-CDate(("01/01/" &
Year(Date())))) as [total days in year]
from thequery
where [discharge date] is null
OR year([discharge date]) = year(date());

alternativally if you want all people regardless of what year they
were discharged

field
Admission date
Discharge date
Total Days: [Discharge date]-[Admission date]
total days in year: iif([Discharge date] is null, Date()-
CDate(("01/01/" & Year(Date()))),iif(year([discharge date]) =
year(date()),[discharge date]-CDate(("01/01/" & Year(Date()))),null))

sql would look something like

select [admission date],[discharge date],[Discharge date]-[Admission
date] as [total days], iif([Discharge date] is null, Date()-
CDate(("01/01/" & Year(Date()))),iif(year([discharge date]) =
year(date()),[discharge date]-CDate(("01/01/" & Year(Date()))),null))
as [total days in year]
from thequery;

hope this helps

Regards
Kelvan
 
G

Georgie047

This might explain is what I am trying to do.
When I put in the start date and end date I get
the results of those days. What I am triying to get is
Example:
a resident is admitted 1/1/2000 and stays until 6/1/2008. I need two answers.
1. How long was he there from 1/1/2000 to 6/1/2008? THIS WORKS
2. How long was he there from 1/1/2008 to 9/1/2008. this year THIS DOES NOT
WORK

I do what to thank you in advance for this has been very challenging for
me. I call it my "little monster", but I'm not giving up! So thanks for the
suggestion. I am going to try your last expression and see what I can do.Do
you think that one should work? Together with all the advise I getting, I'm
going to give it a try Once again THANKS
-- Thanks for your help
Thanks, Linda
 
G

Georgie047

John, no matter what I do, I keep getting this error message. any
suggestions. I also have try the above suggestions and get the same answer.
THANK You very much for your time
--
Thanks, Linda


Georgie047 said:
I try the last expression. I got this error message: "the expression you
entered is misssing a closing parenthesis, bracket (]), or vertical bar(I).
This is the last step that I have to do to get this program up and running.
I will keep "pushing" once again thanks
--
Thanks, Linda


Georgie047 said:
This might explain is what I am trying to do.
When I put in the start date and end date I get
the results of those days. What I am triying to get is
Example:
a resident is admitted 1/1/2000 and stays until 6/1/2008. I need two answers.
1. How long was he there from 1/1/2000 to 6/1/2008? THIS WORKS
2. How long was he there from 1/1/2008 to 9/1/2008. this year THIS DOES NOT
WORK

I do what to thank you in advance for this has been very challenging for
me. I call it my "little monster", but I'm not giving up! So thanks for the
suggestion. I am going to try your last expression and see what I can do.Do
you think that one should work? Together with all the advise I getting, I'm
going to give it a try Once again THANKS
-- Thanks for your help
Thanks, Linda
Click to expand...
Click to expand...
 
J

John Spencer

Looks as if I missed some closing parentheses. There should always be the
same number of left and right parentheses in the expression. I apparently
missed the right or closing parentheses in expressions two and three.

TotalDays:
DateDiff("d",[Admission Date],[Discharge Date])

TotalDays between Admission Date and End of Period or Discharge Date which
ever comes first

DateDiff("d",[Admission Date],
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date]))

Between Admission date or Begin Date and Discharge Date or End Date depending
on whether or not Admission Date is after Begin Date and Discharge Date is
before End Date.

DateDiff("d",
IIF([Admission Date] is Null or
[Admission Date] < [forms]![review forms]![begin date]
,[forms]![review forms]![begin date], [Admission Date])
IIF([Discharge Date]>[forms]![review forms]![end date] or
[Discharge Date] is null
, [forms]![review forms]![end date],[Discharge Date]))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
G

Georgie047

SELECT TBLRESIDENTINFO.facility, TBLRESIDENTINFO.[Socialsecurity#], [Unit
table].unittype, [Unit table].unitnumber, [Unit table].types, [Unit
table].[date admitted to unit], [Unit table].[date discharge from unit],
Nz([date discharge from unit],Date())-[date admitted to unit] AS [Total
remitt days occupied], [Unit table].[Total days in a year]
FROM TBLRESIDENTINFO INNER JOIN [Unit table] ON
TBLRESIDENTINFO.[Socialsecurity#] = [Unit table].[Socialsecurity#]
WHERE ((([Unit table].unittype)="ILU") AND (([Unit table].[date discharge
from unit]) Between [forms]![view reports]![begin date] And [forms]![view
reports]![end date])) OR ((([Unit table].unittype)="ILU") AND (([Unit
table].[date discharge from unit]) Is Null));


JOHN AND ALL OTHERS- I HAVE TRIED EVERYTHING SUGGESTED.
HERE IS MY SQL FOR MY QUERY. I AM AT WITS END AND HAVE LOST COUNTLESS HOURS
OF SLEEP. WOULD YOU PLEASE TAKE A LOOK AT THIS AND SEE WHAT I AM DOING WRONG
ONE THING PROVEN, I WILL BE THE FIRST TO ADMITT I AM NOT PROFESSIONAL AT
THIS. I CERTAINLY AM DOING SOMTHING WRONG. I DO SEND THANKS TO ALL FOR THE
HELP ALREADY GIVEN.



--
Thanks, Linda


Georgie047 said:
John, no matter what I do, I keep getting this error message. any
suggestions. I also have try the above suggestions and get the same answer.
THANK You very much for your time
--
Thanks, Linda


Georgie047 said:
I try the last expression. I got this error message: "the expression you
entered is misssing a closing parenthesis, bracket (]), or vertical bar(I).
This is the last step that I have to do to get this program up and running.
I will keep "pushing" once again thanks
--
Thanks, Linda


Georgie047 said:
This might explain is what I am trying to do.
When I put in the start date and end date I get
the results of those days. What I am triying to get is
Example:
a resident is admitted 1/1/2000 and stays until 6/1/2008. I need two answers.
1. How long was he there from 1/1/2000 to 6/1/2008? THIS WORKS
2. How long was he there from 1/1/2008 to 9/1/2008. this year THIS DOES NOT
WORK

I do what to thank you in advance for this has been very challenging for
me. I call it my "little monster", but I'm not giving up! So thanks for the
suggestion. I am going to try your last expression and see what I can do.Do
you think that one should work? Together with all the advise I getting, I'm
going to give it a try Once again THANKS
-- Thanks for your help
Thanks, Linda

--
Thanks, Linda


:

I gave you several different expressions, since I was not sure what you
wanted.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Georgie047 wrote:
John, do I use all of the expressions or did you give me expressions for each
subject. I think the last one will be my answer, I will try it.
Click to expand...
Click to expand...
Click to expand...
 
L

Lord Kelvan

SELECT TBLRESIDENTINFO.facility, TBLRESIDENTINFO.[Socialsecurity#],
[Unit
table].unittype, [Unit table].unitnumber, [Unit table].types, [Unit
table].[date admitted to unit], [Unit table].[date discharge from
unit],
Nz([date discharge from unit],Date())-[date admitted to unit] AS
[Total
remitt days occupied], [Unit table].[Total days in a year]
FROM TBLRESIDENTINFO INNER JOIN [Unit table] ON
TBLRESIDENTINFO.[Socialsecurity#] = [Unit table].[Socialsecurity#]
WHERE ([Unit table].unittype="ILU" AND [Unit table].[date discharge
from unit] Between [forms]![view reports]![begin date] And [forms]!
[view reports]![end date]) OR ([Unit table].unittype="ILU" AND [Unit
table].[date discharge from unit] Is Null);

you had some weird bracketing it seemed to be ok but try the above

Regards
Kelvan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using an IIF Statement to calculate discharge date 4
Annual Census 2
eliminate data subset from query 2
days of occupancy 1
Compare dates in two seperate records 7
How do I calculate the total number of days? 1
Motel Register - Displaying records between dates 7
"Data Has Been Changed" error with MSSQL 2005 backend using left j 1

Top