select distinctrow using date on form

[terri]

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[Brian]

Put a reference to the form & control name into the query. Try this
(warning: air code - I have not tested it, so you may have to adjust it a
little):

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate]
from [DRGtable1] where [DRGtable1].[drgstartdate]
<=#[Forms]![NameOfYourForm]![admitdate]# and [DRGtable1].[drgenddate]

=#[Forms]![NameOfYourForm]![admitdate]#;

You can build it using the Access query builder. Just enter this in the
criteria for drgstartdate: <=[Forms]![NameOfYourForm]![admitdate]

and this in the criteria for drgenddate:

=[Forms]![NameOfYourForm]![admitdate]

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[Ken Sheridan]

Terri:

Firstly a few points to tidy up the SQL statement:

1. You shouldn't need the DISTINCTROW option. Its really a hangover from
the days when Access couldn’t handle subqueries.

2. As you are returning rows from just one table you don't need to qualify
the column names with the table name.

3. As your table and column names don't include any spaces or other special
characters you don't need to wrap them in brackets.

As for correlating the combo box with the text box you've almost answered
your own question when you say 'where the admit date is between the
[drgstartdate] and the [drgenddate]' as that's very close to what you need in
the SQL statement.

As the parameter is a date/time one its quite important to declare it as
date/time data type. Otherwise Access might misinterpret it as an
arithmetical expression not a date.

You might want the combo box to list its items in some order, so you can add
an ORDER BY clause. For this example I've sorted by strDRGdescription, but
you can change this to whatever you prefer.

So putting that all together the combo box's RowSource property would be
like this:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate BETWEEEN drgstartdate AND drgenddate
ORDER BY strDRGdescription;

To get the combo box to list just the rows where the date range includes the
date entered in the admitdate control you need to requery the combo box in
the AfterUpdate event procedure of the admitdate text box with:

Me.YourComboBox.Requery

You'll need to change the name of the form and combo box in the above to
your actual ones of course.

If you are not familiar with entering code in event procedures this is how
its done:

Select the admitdate control in form design view and open its properties
sheet if its not already open. Then select the After Update event property
in the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the line of code between these two existing lines.

One final caveat:

The SQL statement as written above assumes that the values in the
drgstartdate and drgenddate columns are all dates with a zero time of day
(there's not really any such thing as a date value per se in Access, only
date/time values). If there is any possibility that this might not be the
case (and it could well not be without you being aware of it) the SQL
statement might not return some relevant rows. You can prevent this by
writing it slightly differently:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate >= drgstartdate
AND Forms!YourForm!admitdate < DATEADD("d",1,drgenddate)
ORDER BY strDRGdescription;

This in effect looks for rows where admit date is on or after the
drgstartdate and before the day following the drgenddate. If you are not
absolutely confident that these columns contain only dates with a zero time
of day, then use the latter SQL statement to be sure of missing nothing.

Ken Sheridan
Stafford, England

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[terri]

it now gives me the message "syntax error in date in query expression" ???

--
terri

Put a reference to the form & control name into the query. Try this
(warning: air code - I have not tested it, so you may have to adjust it a
little):

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate]
from [DRGtable1] where [DRGtable1].[drgstartdate]
<=#[Forms]![NameOfYourForm]![admitdate]# and [DRGtable1].[drgenddate]

=#[Forms]![NameOfYourForm]![admitdate]#;

You can build it using the Access query builder. Just enter this in the
criteria for drgstartdate: <=[Forms]![NameOfYourForm]![admitdate]

and this in the criteria for drgenddate:

=[Forms]![NameOfYourForm]![admitdate]

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[terri]

it gives me the message "syntax error in parameter clause"

i don't know if i have to post to each reply or not...I tried both
suggestions and am still stuck

and neither one will show me the drop down list.
--
terri

Terri:

Firstly a few points to tidy up the SQL statement:

1. You shouldn't need the DISTINCTROW option. Its really a hangover from
the days when Access couldn’t handle subqueries.

2. As you are returning rows from just one table you don't need to qualify
the column names with the table name.

3. As your table and column names don't include any spaces or other special
characters you don't need to wrap them in brackets.

As for correlating the combo box with the text box you've almost answered
your own question when you say 'where the admit date is between the
[drgstartdate] and the [drgenddate]' as that's very close to what you need in
the SQL statement.

As the parameter is a date/time one its quite important to declare it as
date/time data type. Otherwise Access might misinterpret it as an
arithmetical expression not a date.

You might want the combo box to list its items in some order, so you can add
an ORDER BY clause. For this example I've sorted by strDRGdescription, but
you can change this to whatever you prefer.

So putting that all together the combo box's RowSource property would be
like this:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate BETWEEEN drgstartdate AND drgenddate
ORDER BY strDRGdescription;

To get the combo box to list just the rows where the date range includes the
date entered in the admitdate control you need to requery the combo box in
the AfterUpdate event procedure of the admitdate text box with:

Me.YourComboBox.Requery

You'll need to change the name of the form and combo box in the above to
your actual ones of course.

If you are not familiar with entering code in event procedures this is how
its done:

Select the admitdate control in form design view and open its properties
sheet if its not already open. Then select the After Update event property
in the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the line of code between these two existing lines.

One final caveat:

The SQL statement as written above assumes that the values in the
drgstartdate and drgenddate columns are all dates with a zero time of day
(there's not really any such thing as a date value per se in Access, only
date/time values). If there is any possibility that this might not be the
case (and it could well not be without you being aware of it) the SQL
statement might not return some relevant rows. You can prevent this by
writing it slightly differently:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate >= drgstartdate
AND Forms!YourForm!admitdate < DATEADD("d",1,drgenddate)
ORDER BY strDRGdescription;

This in effect looks for rows where admit date is on or after the
drgstartdate and before the day following the drgenddate. If you are not
absolutely confident that these columns contain only dates with a zero time
of day, then use the latter SQL statement to be sure of missing nothing.

Ken Sheridan
Stafford, England

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[Ken Sheridan]

Terri:

There are no hard and fast rules about replying, but if the posts your are
replying to differ significantly in their coverage then its probably netter
to reply separately to each.

I can't see any error in the Parameters clause as I posted it. If your form
name contains spaces or other special characters then wrap it in brackets,
e.g.

Forms![Your Form]!admitdate

This goes for column names too. If in doubt use the brackets.

Make sure there are no spaces in other spaces, e.g. don't use:

Forms! [Your Form]! admitdate

Make sure that the parameters clause is terminated with a semi-colon.

Apart from the above I can't think where else you might have gone wrong.

When posting questions it helps if you give the names of all objects to
which you refer, e.g. the form name , the combo box name etc. That way we
can use the real names in any sample code when we reply.

Ken Sheridan
Stafford, England

it gives me the message "syntax error in parameter clause"

i don't know if i have to post to each reply or not...I tried both
suggestions and am still stuck

and neither one will show me the drop down list.
--
terri

Terri:

Firstly a few points to tidy up the SQL statement:

1. You shouldn't need the DISTINCTROW option. Its really a hangover from
the days when Access couldn’t handle subqueries.

2. As you are returning rows from just one table you don't need to qualify
the column names with the table name.

3. As your table and column names don't include any spaces or other special
characters you don't need to wrap them in brackets.

As for correlating the combo box with the text box you've almost answered
your own question when you say 'where the admit date is between the
[drgstartdate] and the [drgenddate]' as that's very close to what you need in
the SQL statement.

As the parameter is a date/time one its quite important to declare it as
date/time data type. Otherwise Access might misinterpret it as an
arithmetical expression not a date.

You might want the combo box to list its items in some order, so you can add
an ORDER BY clause. For this example I've sorted by strDRGdescription, but
you can change this to whatever you prefer.

So putting that all together the combo box's RowSource property would be
like this:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate BETWEEEN drgstartdate AND drgenddate
ORDER BY strDRGdescription;

To get the combo box to list just the rows where the date range includes the
date entered in the admitdate control you need to requery the combo box in
the AfterUpdate event procedure of the admitdate text box with:

Me.YourComboBox.Requery

You'll need to change the name of the form and combo box in the above to
your actual ones of course.

If you are not familiar with entering code in event procedures this is how
its done:

Select the admitdate control in form design view and open its properties
sheet if its not already open. Then select the After Update event property
in the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the line of code between these two existing lines.

One final caveat:

The SQL statement as written above assumes that the values in the
drgstartdate and drgenddate columns are all dates with a zero time of day
(there's not really any such thing as a date value per se in Access, only
date/time values). If there is any possibility that this might not be the
case (and it could well not be without you being aware of it) the SQL
statement might not return some relevant rows. You can prevent this by
writing it slightly differently:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate >= drgstartdate
AND Forms!YourForm!admitdate < DATEADD("d",1,drgenddate)
ORDER BY strDRGdescription;

This in effect looks for rows where admit date is on or after the
drgstartdate and before the day following the drgenddate. If you are not
absolutely confident that these columns contain only dates with a zero time
of day, then use the latter SQL statement to be sure of missing nothing.

Ken Sheridan
Stafford, England

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[terri]

First let me thank you for your help...even though i still can't get it to
work.
you've been very patient and i can understand what you're saying.
i made three combo boxes on the form.
here is what i have:
combobox named CoderDRG1

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
WHERE Forms![1 Inpt Case Data Entry Form]!admitdate >= drgstartdate AND
Forms![Inpt Case Data Entry Form]!admitdate < DATEADD("d",1,drgenddate) ORDER
BY strDRG;

this one asks for parameter "Forms![1 Inpt Case Data Entry From]!admitdate"
-------
and this combo box named CoderDRG2

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
Where Forms![1 Inpt Case Data Entry Form]!admitdate between drgstartdate and
drgenddate ORDER BY strdrg;

it doesn't do anything..no drop down, no error message
----

and the third combo box named CoderDRG3

PARAMETERS Forms![1 Inpt Case Data Entry Form]![admitdate] DATETIME;SELECT
[strdrg], [strdrgdescription], [intweight],[drgstartdate], [drgenddate] FROM
[DRGtable1] Where Forms![1 Inpt Case Data Entry Form]![admitdate] between
[drgstartdate] and [drgenddate] ORDER BY [strdrg];

it doesn't do anything either.

i'm about ready to make two forms...one for this drg year and one for
next..ha!

--
terri

Terri:

There are no hard and fast rules about replying, but if the posts your are
replying to differ significantly in their coverage then its probably netter
to reply separately to each.

I can't see any error in the Parameters clause as I posted it. If your form
name contains spaces or other special characters then wrap it in brackets,
e.g.

Forms![Your Form]!admitdate

This goes for column names too. If in doubt use the brackets.

Make sure there are no spaces in other spaces, e.g. don't use:

Forms! [Your Form]! admitdate

Make sure that the parameters clause is terminated with a semi-colon.

Apart from the above I can't think where else you might have gone wrong.

When posting questions it helps if you give the names of all objects to
which you refer, e.g. the form name , the combo box name etc. That way we
can use the real names in any sample code when we reply.

Ken Sheridan
Stafford, England

it gives me the message "syntax error in parameter clause"

i don't know if i have to post to each reply or not...I tried both
suggestions and am still stuck

and neither one will show me the drop down list.
--
terri

Terri:

Firstly a few points to tidy up the SQL statement:

1. You shouldn't need the DISTINCTROW option. Its really a hangover from
the days when Access couldn’t handle subqueries.

2. As you are returning rows from just one table you don't need to qualify
the column names with the table name.

3. As your table and column names don't include any spaces or other special
characters you don't need to wrap them in brackets.

As for correlating the combo box with the text box you've almost answered
your own question when you say 'where the admit date is between the
[drgstartdate] and the [drgenddate]' as that's very close to what you need in
the SQL statement.

As the parameter is a date/time one its quite important to declare it as
date/time data type. Otherwise Access might misinterpret it as an
arithmetical expression not a date.

You might want the combo box to list its items in some order, so you can add
an ORDER BY clause. For this example I've sorted by strDRGdescription, but
you can change this to whatever you prefer.

So putting that all together the combo box's RowSource property would be
like this:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate BETWEEEN drgstartdate AND drgenddate
ORDER BY strDRGdescription;

To get the combo box to list just the rows where the date range includes the
date entered in the admitdate control you need to requery the combo box in
the AfterUpdate event procedure of the admitdate text box with:

Me.YourComboBox.Requery

You'll need to change the name of the form and combo box in the above to
your actual ones of course.

If you are not familiar with entering code in event procedures this is how
its done:

Select the admitdate control in form design view and open its properties
sheet if its not already open. Then select the After Update event property
in the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the line of code between these two existing lines.

One final caveat:

The SQL statement as written above assumes that the values in the
drgstartdate and drgenddate columns are all dates with a zero time of day
(there's not really any such thing as a date value per se in Access, only
date/time values). If there is any possibility that this might not be the
case (and it could well not be without you being aware of it) the SQL
statement might not return some relevant rows. You can prevent this by
writing it slightly differently:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate >= drgstartdate
AND Forms!YourForm!admitdate < DATEADD("d",1,drgenddate)
ORDER BY strDRGdescription;

This in effect looks for rows where admit date is on or after the
drgstartdate and before the day following the drgenddate. If you are not
absolutely confident that these columns contain only dates with a zero time
of day, then use the latter SQL statement to be sure of missing nothing.

Ken Sheridan
Stafford, England

:

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[Ken Sheridan]

Terri:

Patience is my middle name. My father wanted to call me Prudence, but my
mother thought it sounded too butch >

In the first one you have two different names for the form, [1 Inpt Case
Data Entry Form] and [Inpt Case Data Entry Form], which accounts for the
prompt for the parameter. It looks like the one missing the 1 is wrong.

The combo boxes won't show anything in their lists until you enter a date in
the admitdate control and the combo box is requeried. As you are using three
combo boxes you'll need three lines of code in admitdate's AfterUpdate event
procedure:

Me.CoderDRG1.Requery
Me.CoderDRG2.Requery
Me.CoderDRG3.Requery

If you want the combo boxes to show all items regardless of date before you
enter a date in admitdate then you need to amend the SQL statements to test
for NULL, e.g. for CoderDRG1's RowSource:

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;
SELECT strdrg, strdrgdescription, intweight, drgstartdate, drgenddate
FROM DRGtable1
WHERE (Forms![1 Inpt Case Data Entry Form]!admitdate >= drgstartdate
AND Forms![1 Inpt Case Data Entry Form]!admitdate < DATEADD("d",1,drgenddate))
OR Forms![1 Inpt Case Data Entry Form]!admitdate IS NULL
ORDER BY strDRG;

The parentheses around the expression containing the AND operation are
important as its necessary to force this to evaluate independently of the OR
operation.

Ken Sheridan
Stafford, England

First let me thank you for your help...even though i still can't get it to
work.
you've been very patient and i can understand what you're saying.
i made three combo boxes on the form.
here is what i have:
combobox named CoderDRG1

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
WHERE Forms![1 Inpt Case Data Entry Form]!admitdate >= drgstartdate AND
Forms![Inpt Case Data Entry Form]!admitdate < DATEADD("d",1,drgenddate) ORDER
BY strDRG;

this one asks for parameter "Forms![1 Inpt Case Data Entry From]!admitdate"
-------
and this combo box named CoderDRG2

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
Where Forms![1 Inpt Case Data Entry Form]!admitdate between drgstartdate and
drgenddate ORDER BY strdrg;

it doesn't do anything..no drop down, no error message
----

and the third combo box named CoderDRG3

PARAMETERS Forms![1 Inpt Case Data Entry Form]![admitdate] DATETIME;SELECT
[strdrg], [strdrgdescription], [intweight],[drgstartdate], [drgenddate] FROM
[DRGtable1] Where Forms![1 Inpt Case Data Entry Form]![admitdate] between
[drgstartdate] and [drgenddate] ORDER BY [strdrg];

it doesn't do anything either.

i'm about ready to make two forms...one for this drg year and one for
next..ha!

--
terri

Terri:

There are no hard and fast rules about replying, but if the posts your are
replying to differ significantly in their coverage then its probably netter
to reply separately to each.

I can't see any error in the Parameters clause as I posted it. If your form
name contains spaces or other special characters then wrap it in brackets,
e.g.

Forms![Your Form]!admitdate

This goes for column names too. If in doubt use the brackets.

Make sure there are no spaces in other spaces, e.g. don't use:

Forms! [Your Form]! admitdate

Make sure that the parameters clause is terminated with a semi-colon.

Apart from the above I can't think where else you might have gone wrong.

When posting questions it helps if you give the names of all objects to
which you refer, e.g. the form name , the combo box name etc. That way we
can use the real names in any sample code when we reply.

Ken Sheridan
Stafford, England

it gives me the message "syntax error in parameter clause"

i don't know if i have to post to each reply or not...I tried both
suggestions and am still stuck

and neither one will show me the drop down list.
--
terri


:

Terri:

Firstly a few points to tidy up the SQL statement:

1. You shouldn't need the DISTINCTROW option. Its really a hangover from
the days when Access couldn’t handle subqueries.

2. As you are returning rows from just one table you don't need to qualify
the column names with the table name.

3. As your table and column names don't include any spaces or other special
characters you don't need to wrap them in brackets.

As for correlating the combo box with the text box you've almost answered
your own question when you say 'where the admit date is between the
[drgstartdate] and the [drgenddate]' as that's very close to what you need in
the SQL statement.

As the parameter is a date/time one its quite important to declare it as
date/time data type. Otherwise Access might misinterpret it as an
arithmetical expression not a date.

You might want the combo box to list its items in some order, so you can add
an ORDER BY clause. For this example I've sorted by strDRGdescription, but
you can change this to whatever you prefer.

So putting that all together the combo box's RowSource property would be
like this:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate BETWEEEN drgstartdate AND drgenddate
ORDER BY strDRGdescription;

To get the combo box to list just the rows where the date range includes the
date entered in the admitdate control you need to requery the combo box in
the AfterUpdate event procedure of the admitdate text box with:

Me.YourComboBox.Requery

You'll need to change the name of the form and combo box in the above to
your actual ones of course.

If you are not familiar with entering code in event procedures this is how
its done:

Select the admitdate control in form design view and open its properties
sheet if its not already open. Then select the After Update event property
in the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the line of code between these two existing lines.

One final caveat:

The SQL statement as written above assumes that the values in the
drgstartdate and drgenddate columns are all dates with a zero time of day
(there's not really any such thing as a date value per se in Access, only
date/time values). If there is any possibility that this might not be the
case (and it could well not be without you being aware of it) the SQL
statement might not return some relevant rows. You can prevent this by
writing it slightly differently:

PARAMETERS Forms!YourForm!admitdate DATETIME;
SELECT strdrg, strDRGdescription, intWeight, drgstartdate, drgenddate
FROM DRGtable1
WHERE Forms!YourForm!admitdate >= drgstartdate
AND Forms!YourForm!admitdate < DATEADD("d",1,drgenddate)
ORDER BY strDRGdescription;

This in effect looks for rows where admit date is on or after the
drgstartdate and before the day following the drgenddate. If you are not
absolutely confident that these columns contain only dates with a zero time
of day, then use the latter SQL statement to be sure of missing nothing.

Ken Sheridan
Stafford, England

:

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[John W. Vinson]

First let me thank you for your help...even though i still can't get it to
work.
you've been very patient and i can understand what you're saying.
i made three combo boxes on the form.
here is what i have:
combobox named CoderDRG1

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
WHERE Forms![1 Inpt Case Data Entry Form]!admitdate >= drgstartdate AND
Forms![Inpt Case Data Entry Form]!admitdate < DATEADD("d",1,drgenddate) ORDER
BY strDRG;

this one asks for parameter "Forms![1 Inpt Case Data Entry From]!admitdate"

The "From" is suspicious here: is that a typo in posting?

The query will work IF you have a main form (not a subform) named
[1 Inpt Case Data Entry Form] with a control on it named admitdate, and said
form is open at the time that the query is run. Is that in fact the case?

 

[terri]

ok..i almost have it ... i think

i had three combo boxes on the form i was testing...not that i needed three..
i'm not very good at trying to convey what i'm doing in writing am i? sorry...
but i do understand what you mean by requery the three in the admit date if
i did have more than one

anyway...this is what i have now:

Parameters Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME; SELECT
strdrg, strdrgdescription, intweight, drgstartdate, drgenddate FROM DRGtable1
WHERE (Forms![1 Inpt Case Data Entry Form]!admitdate>=drgstartdate AND
Forms![1 Inpt Case Data Entry Form]!admitdate<DATEADD("d",1,drgenddate)) OR
Forms![1 Inpt Case Data Entry Form]!admitdate IS NULL ORDER BY strDRG;

but...it doesn't show me a drop down list for the users to pick from.
and if i type in a DRG, it tells me "The text you entered isn't an item in
the list. Select an item from the list, or enter text that matches one of
the listed items."
But there's no list.

now this works just fine
select distinctrow [DRGtable1].[strdrg],[DRGtable1].[strDRGdescription] FROM
DRGtable1
it's the "where" part that i'm not getting right.

 

[terri]

yes that was a typo...
it is the main form...not a subform
and the admitdate is on this form.

 

[Ken Sheridan]

Terri:

In that case it should work, assuming you have also put the code in the
event procedure.

Ken Sheridan
Stafford, England

yes that was a typo...
it is the main form...not a subform
and the admitdate is on this form.
--
terri

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!

 

[terri]

This is what worked:

PARAMETERS Forms![1 Inpt Case Data Entry Form]!admitdate DATETIME;
SELECT strdrg, strdrgdescription, intweight, drgstartdate, drgenddate
FROM DRGtable1
WHERE (Forms![1 Inpt Case Data Entry Form]!admitdate >= drgstartdate
AND Forms![1 Inpt Case Data Entry Form]!admitdate < DATEADD("d",1,drgenddate))
OR Forms![1 Inpt Case Data Entry Form]!admitdate IS NULL
ORDER BY strDRG;

i was getting cross-eyed looking at all this and decided to give it a rest.
then went back in, erased everything and typed it again.
so i had to have had a typo in there somewhere.

thank you soooo much Ken..
you are a dream!!!!!!

--
terri

Terri:

In that case it should work, assuming you have also put the code in the
event procedure.

Ken Sheridan
Stafford, England

yes that was a typo...
it is the main form...not a subform
and the admitdate is on this form.
--
terri

I have a form with a combo box that selects the drg from a table...works just
fine:

select distinctrow
[DRGtable1].[strdrg],[DRGtable1].[strDRGdescription],[DRGtable1].[intWeight],[DRGtable1].[drgstartdate],[DRGtable1].[drgenddate] from [DRGtable1];

Now i want that combo box to select the correct drg based on a text box
the user fills in called [admitdate] where the admit date is between
the [drgstartdate] and the [drgenddate].

reason is there will be two drgs with the same number like 470, but for
oct 1 2007 through sept 30 2008, 470 is weighted at 5..and for the next
year it's weighted at say 6.

i'm coming up on Oct 1st where these weights will change...can you help me?
i'm eating too much chocolate trying to do this!