High and Low Dates in Combo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a multipart question:

I have cascading combos that narrow each element down. No big woop there,
but one of the options in second combo is "Date Range" and so when someone
clicks on the third combo, it will bring up input parameter for "Start Date"
and "End Date" and then loads the items available for that range in the combo.

So far, ok. But now when I click the botton to generate report, I need to
pass that range along without having them enter it again, so here is the
question and your help would be appreciated:

1. Can you store the input values recieved during parameter prompts into
something to be passed later: Example: someVar = [parameter]?

2. Alternatively, is there any way I can get the lowest date (oldest) from
the combo list and set that as my start and the highest date (newest) from
the combo and set that as my end?

Any help you give me would be appreciated, thank you.
 
Michael

If you have a form on which you've selected "input values", why not have the
query on which your report is based "look" to that form for its parameters?

If that's too complex, consider writing some dynamic SQL that "builds" a SQL
statement by considering each of the selected combobox values. You could
base the report on a standard query, but open it with a "WHERE" clause,
build as described.

Good luck

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in this
situation believe it or not, there is a good reason why it won't work, I
would love to send you the code to look at but it would take up most of this
screen and then some. Let me see if I can break it down a bit to the area I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and "Certified")
Combo 2: cmb_QueryOptions (static list of different choices for Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source of Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report passes a
SQL with the additional where of combo 3 data to the report record source and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for from and to
dates, so when you click on combo 3 you get those input boxes and then it
loads the date in that range

I want to be able to somehow remember and store those inputed dates for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards using DTP
but really don't want to use that unless I must.

thank you.
Michael


Jeff Boyce said:
Michael

If you have a form on which you've selected "input values", why not have the
query on which your report is based "look" to that form for its parameters?

If that's too complex, consider writing some dynamic SQL that "builds" a SQL
statement by considering each of the selected combobox values. You could
base the report on a standard query, but open it with a "WHERE" clause,
build as described.

Good luck

Jeff Boyce
Microsoft Office/Access MVP

Michael said:
I have a multipart question:

I have cascading combos that narrow each element down. No big woop there,
but one of the options in second combo is "Date Range" and so when someone
clicks on the third combo, it will bring up input parameter for "Start
Date"
and "End Date" and then loads the items available for that range in the
combo.

So far, ok. But now when I click the botton to generate report, I need to
pass that range along without having them enter it again, so here is the
question and your help would be appreciated:

1. Can you store the input values recieved during parameter prompts into
something to be passed later: Example: someVar = [parameter]?

2. Alternatively, is there any way I can get the lowest date (oldest) from
the combo list and set that as my start and the highest date (newest) from
the combo and set that as my end?

Any help you give me would be appreciated, thank you.
Click to expand...
Click to expand...
 
Michael

"...for later use to pass to the report..." implies that the form goes away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command button, add
code that dynamically generates a SQL WHERE clause, and code that opens the
report, with the WHERE clause limiting the recordset on which the report is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Actually no, the form isn't going anywhere, the form stays regardless of the
report or anything, it can be reused.

What I mean is that how to recycle the data that was inputed in response to
the prompts for generating the reports condition without prompting them
again. There has to be a way to reuse the data more than once after it has
been prompted and I need to know how to do THAT.

any help would be appreciated, sorry about the delay, I got the message
early this morning but couldn't log into the site for some reason.

Michael


Jeff Boyce said:
Michael

"...for later use to pass to the report..." implies that the form goes away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command button, add
code that dynamically generates a SQL WHERE clause, and code that opens the
report, with the WHERE clause limiting the recordset on which the report is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Michael said:
Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in this
situation believe it or not, there is a good reason why it won't work, I
would love to send you the code to look at but it would take up most of
this
screen and then some. Let me see if I can break it down a bit to the area
I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and "Certified")
Combo 2: cmb_QueryOptions (static list of different choices for
Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source of
Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report passes a
SQL with the additional where of combo 3 data to the report record source
and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for from and
to
dates, so when you click on combo 3 you get those input boxes and then it
loads the date in that range

I want to be able to somehow remember and store those inputed dates for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards using DTP
but really don't want to use that unless I must.

thank you.
Michael
Click to expand...
Click to expand...
 
Michael

If you are being prompted for the selection criteria more than one (or even
once), your query is not "looking" to the form for it's criteria, or your
report is using parameters not included in the query.

Re-check both the report and the report's underlying query (?queries?) and
ensure that the only parameter they use is provided as something like:

Forms!YourForm!YourControl.

It may also be that you have a base query and need to use the form to
generate a "WHERE" clause used, in opening the report, to limit the records.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Michael said:
Jeff,

Actually no, the form isn't going anywhere, the form stays regardless of
the
report or anything, it can be reused.

What I mean is that how to recycle the data that was inputed in response
to
the prompts for generating the reports condition without prompting them
again. There has to be a way to reuse the data more than once after it has
been prompted and I need to know how to do THAT.

any help would be appreciated, sorry about the delay, I got the message
early this morning but couldn't log into the site for some reason.

Michael


Jeff Boyce said:
Michael

"...for later use to pass to the report..." implies that the form goes
away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command button,
add
code that dynamically generates a SQL WHERE clause, and code that opens
the
report, with the WHERE clause limiting the recordset on which the report
is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Michael said:
Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in this
situation believe it or not, there is a good reason why it won't work,
I
would love to send you the code to look at but it would take up most of
this
screen and then some. Let me see if I can break it down a bit to the
area
I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and
"Certified")
Combo 2: cmb_QueryOptions (static list of different choices for
Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source of
Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report
passes a
SQL with the additional where of combo 3 data to the report record
source
and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for from
and
to
dates, so when you click on combo 3 you get those input boxes and then
it
loads the date in that range

I want to be able to somehow remember and store those inputed dates for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards using
DTP
but really don't want to use that unless I must.

thank you.
Michael
Click to expand...
Click to expand...
Click to expand...
 
Jeff

I appreciate your help, I do but I guess you are not seeing what I am
asking, if you read my post one before last one, you will see that combo 3 is
being populated by prompting the user for two dates to create the range list
in the combo. When they click on report, I want it to use the same two
parameters early prompted without doing it again.

Michael

Jeff Boyce said:
Michael

If you are being prompted for the selection criteria more than one (or even
once), your query is not "looking" to the form for it's criteria, or your
report is using parameters not included in the query.

Re-check both the report and the report's underlying query (?queries?) and
ensure that the only parameter they use is provided as something like:

Forms!YourForm!YourControl.

It may also be that you have a base query and need to use the form to
generate a "WHERE" clause used, in opening the report, to limit the records.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Michael said:
Jeff,

Actually no, the form isn't going anywhere, the form stays regardless of
the
report or anything, it can be reused.

What I mean is that how to recycle the data that was inputed in response
to
the prompts for generating the reports condition without prompting them
again. There has to be a way to reuse the data more than once after it has
been prompted and I need to know how to do THAT.

any help would be appreciated, sorry about the delay, I got the message
early this morning but couldn't log into the site for some reason.

Michael


Jeff Boyce said:
Michael

"...for later use to pass to the report..." implies that the form goes
away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command button,
add
code that dynamically generates a SQL WHERE clause, and code that opens
the
report, with the WHERE clause limiting the recordset on which the report
is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in this
situation believe it or not, there is a good reason why it won't work,
I
would love to send you the code to look at but it would take up most of
this
screen and then some. Let me see if I can break it down a bit to the
area
I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and
"Certified")
Combo 2: cmb_QueryOptions (static list of different choices for
Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source of
Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report
passes a
SQL with the additional where of combo 3 data to the report record
source
and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for from
and
to
dates, so when you click on combo 3 you get those input boxes and then
it
loads the date in that range

I want to be able to somehow remember and store those inputed dates for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards using
DTP
but really don't want to use that unless I must.

thank you.
Michael
Click to expand...
Click to expand...
Click to expand...
 
Michael

If the report is prompting for the parameters, the query on which the report
is based is not seeing the values you entered on the form. Please post the
SQL statement of the query on which your report is based.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael said:
Jeff

I appreciate your help, I do but I guess you are not seeing what I am
asking, if you read my post one before last one, you will see that combo 3
is
being populated by prompting the user for two dates to create the range
list
in the combo. When they click on report, I want it to use the same two
parameters early prompted without doing it again.

Michael

Jeff Boyce said:
Michael

If you are being prompted for the selection criteria more than one (or
even
once), your query is not "looking" to the form for it's criteria, or your
report is using parameters not included in the query.

Re-check both the report and the report's underlying query (?queries?)
and
ensure that the only parameter they use is provided as something like:

Forms!YourForm!YourControl.

It may also be that you have a base query and need to use the form to
generate a "WHERE" clause used, in opening the report, to limit the
records.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Michael said:
Jeff,

Actually no, the form isn't going anywhere, the form stays regardless
of
the
report or anything, it can be reused.

What I mean is that how to recycle the data that was inputed in
response
to
the prompts for generating the reports condition without prompting them
again. There has to be a way to reuse the data more than once after it
has
been prompted and I need to know how to do THAT.

any help would be appreciated, sorry about the delay, I got the message
early this morning but couldn't log into the site for some reason.

Michael


:

Michael

"...for later use to pass to the report..." implies that the form goes
away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command
button,
add
code that dynamically generates a SQL WHERE clause, and code that
opens
the
report, with the WHERE clause limiting the recordset on which the
report
is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in
this
situation believe it or not, there is a good reason why it won't
work,
I
would love to send you the code to look at but it would take up most
of
this
screen and then some. Let me see if I can break it down a bit to the
area
I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and
"Certified")
Combo 2: cmb_QueryOptions (static list of different choices for
Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source
of
Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report
passes a
SQL with the additional where of combo 3 data to the report record
source
and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for
from
and
to
dates, so when you click on combo 3 you get those input boxes and
then
it
loads the date in that range

I want to be able to somehow remember and store those inputed dates
for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards
using
DTP
but really don't want to use that unless I must.

thank you.
Michael
Click to expand...
Click to expand...
Click to expand...
 
Jeff,

Since I was on a timeline, I went ahead and used two DTP controls to get the
start and end dates instead of prompting and then passing their values to the
sql statement for the report.

I guess we just weren't clicking on what i was trying to achieve and I would
post the code but there is alot of it that are interlinked and it would take
up way too much space. I do however appreciate all your help and I am
grateful for your time.

thanks,
Michael


Jeff Boyce said:
Michael

If the report is prompting for the parameters, the query on which the report
is based is not seeing the values you entered on the form. Please post the
SQL statement of the query on which your report is based.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael said:
Jeff

I appreciate your help, I do but I guess you are not seeing what I am
asking, if you read my post one before last one, you will see that combo 3
is
being populated by prompting the user for two dates to create the range
list
in the combo. When they click on report, I want it to use the same two
parameters early prompted without doing it again.

Michael

Jeff Boyce said:
Michael

If you are being prompted for the selection criteria more than one (or
even
once), your query is not "looking" to the form for it's criteria, or your
report is using parameters not included in the query.

Re-check both the report and the report's underlying query (?queries?)
and
ensure that the only parameter they use is provided as something like:

Forms!YourForm!YourControl.

It may also be that you have a base query and need to use the form to
generate a "WHERE" clause used, in opening the report, to limit the
records.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff,

Actually no, the form isn't going anywhere, the form stays regardless
of
the
report or anything, it can be reused.

What I mean is that how to recycle the data that was inputed in
response
to
the prompts for generating the reports condition without prompting them
again. There has to be a way to reuse the data more than once after it
has
been prompted and I need to know how to do THAT.

any help would be appreciated, sorry about the delay, I got the message
early this morning but couldn't log into the site for some reason.

Michael


:

Michael

"...for later use to pass to the report..." implies that the form goes
away.
It may not be necessary to close the form to open the report.

One approach is to use a form to gather criteria, add a command
button,
add
code that dynamically generates a SQL WHERE clause, and code that
opens
the
report, with the WHERE clause limiting the recordset on which the
report
is
based.

For this to work, you'd have to leave the form open.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff,

Thanks for the reply and I know EXACTLY what you are saying but in
this
situation believe it or not, there is a good reason why it won't
work,
I
would love to send you the code to look at but it would take up most
of
this
screen and then some. Let me see if I can break it down a bit to the
area
I
need help:

Form: Custom_Reports
Combo 1: cmb_Category (2 static choices of "Regular" and
"Certified")
Combo 2: cmb_QueryOptions (static list of different choices for
Regular
vs. Certified)
Example: Document Date, Date Range, Document Name ...
Selecting any item feeds a dynamic SQL into the data source
of
Combo3
Combo 3: Dynamically loads the data based on its SQL from Combo2
Selecting the criteria from Combo 3 and then click Report
passes a
SQL with the additional where of combo 3 data to the report record
source
and
we are done

However, when combo 2 is Date Range, it passes a SQL with [] for
from
and
to
dates, so when you click on combo 3 you get those input boxes and
then
it
loads the date in that range

I want to be able to somehow remember and store those inputed dates
for
later use to pass to the report, is it more clear now?

hopefully, cause I could use your help, I am now leaning towards
using
DTP
but really don't want to use that unless I must.

thank you.
Michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top