Annual Census

[Leo]

I need to get annual census of the patients taken care of at a center. The
table of patient has date of admission and date of discharge. In the query
based on this table if I enter the criteria of date of discharge as Is Null
Or >#12/31/2008# it gives me the annual census for the year 2009. (Since it
lists all the patients admitted so far to the center and eliminates all those
who have been discharged as of 12/31/2008 but includes all the patients
discharged this year).

But I need the user input as to which year of census is needed.

I entered in the criteria box of the querry for the field of discharge date
when I enter
[Please enter: Is Null Or >#mm/dd/yyyy#] the query opens and asks for
the user input. But when the I respond by entering
Is Null Or >#12/31/2008#
It flashes error message as type mismatch.

What should I enter, since it is a date field and does not recognize other
characters added through user input?

Please help.
Thanking you
Leo

 

[Leo]

Leo:

You don't need to enter 'Is Null' at the parameter prompt at all, simply the
date. Nor you need to enter the # date delimiter characters, just a value
such as 12/31/2008. Change the criteria on the discharge date column to
something like:

[Please enter discharge date:]
Is Null

on two separate rows. This will translate to a WHERE clause in the SQL of:

WHERE [discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL

This will be fine if there are no other criteria in the WHERE clause, but if
there are any others you need to force the OR operation to evaluate
independently of them, so enclose the expression in parentheses:

WHERE ([discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL)

There is a further point to take into account, however, which is that the
parameter should be declared as date/time to avoid it being mistakenly
interpreted as an arithmetical expression. You can do this in query design
view via the parameters dialogue, or in SQL view by simply adding the
parameter declaration to the start of the query, which would then look
something like this:

PARAMETERS [Please enter discharge date:] DATETIME;
SELECT *
FROM [YourTable]
WHERE ([discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL);

Ken Sheridan
Stafford, England

I need to get annual census of the patients taken care of at a center. The
table of patient has date of admission and date of discharge. In the query
based on this table if I enter the criteria of date of discharge as Is Null
Or >#12/31/2008# it gives me the annual census for the year 2009. (Since it
lists all the patients admitted so far to the center and eliminates all those
who have been discharged as of 12/31/2008 but includes all the patients
discharged this year).

But I need the user input as to which year of census is needed.

I entered in the criteria box of the querry for the field of discharge date
when I enter
[Please enter: Is Null Or >#mm/dd/yyyy#] the query opens and asks for
the user input. But when the I respond by entering
Is Null Or >#12/31/2008#
It flashes error message as type mismatch.

What should I enter, since it is a date field and does not recognize other
characters added through user input?

Please help.
Thanking you
Leo

--
Message posted via AccessMonster.com


Thank you so much. Will try. Looks like it should work.

Thanks again
Leo

 

[Leo]

Leo:

You don't need to enter 'Is Null' at the parameter prompt at all, simply the
date. Nor you need to enter the # date delimiter characters, just a value
such as 12/31/2008. Change the criteria on the discharge date column to
something like:

[Please enter discharge date:]
Is Null

on two separate rows. This will translate to a WHERE clause in the SQL of:

WHERE [discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL

This will be fine if there are no other criteria in the WHERE clause, but if
there are any others you need to force the OR operation to evaluate
independently of them, so enclose the expression in parentheses:

WHERE ([discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL)

There is a further point to take into account, however, which is that the
parameter should be declared as date/time to avoid it being mistakenly
interpreted as an arithmetical expression. You can do this in query design
view via the parameters dialogue, or in SQL view by simply adding the
parameter declaration to the start of the query, which would then look
something like this:

PARAMETERS [Please enter discharge date:] DATETIME;
SELECT *
FROM [YourTable]
WHERE ([discharge date] = [Please enter discharge date:]
OR [discharge date] IS NULL);

Ken Sheridan
Stafford, England

I need to get annual census of the patients taken care of at a center. The
table of patient has date of admission and date of discharge. In the query
based on this table if I enter the criteria of date of discharge as Is Null
Or >#12/31/2008# it gives me the annual census for the year 2009. (Since it
lists all the patients admitted so far to the center and eliminates all those
who have been discharged as of 12/31/2008 but includes all the patients
discharged this year).

But I need the user input as to which year of census is needed.

I entered in the criteria box of the querry for the field of discharge date
when I enter
[Please enter: Is Null Or >#mm/dd/yyyy#] the query opens and asks for
the user input. But when the I respond by entering
Is Null Or >#12/31/2008#
It flashes error message as type mismatch.

What should I enter, since it is a date field and does not recognize other
characters added through user input?

Please help.
Thanking you
Leo

--
Message posted via AccessMonster.com


Thank you so much. Will try. Looks like it should work.

Thanks again
Leo

It did work! Thanks again!!
Leo