Specify date criteria for subform data

[Sammie]

I need help to specify date criteria in a subform without using a parameter
query. The date criteria would be a fixed date that the user could change
from time to time. My desired subform criteria is
OrderDate between #1/1/08# and #1/1/2010#
I don't need to change the ending date.
This works fine as criteria on the query, but I want the user to be able to
change it, and the user can't access the query.

How can I change the date #1/1/08# from outside of the query? I would
prefer to change the date on the main form. The main form data doesn’t
contain an OrderDate field. I don’t want the user to have to change this
date every time, as they would in a parameter query. I simply cannot figure
out how to get a user-specified date into the subform query. I cannot link
it on the subform properties link master-link child because the OrderDate
doesn’t exist on the main form. My subforms link just fine just based on
ProductID. When I add a date field to the subform query to define the
beginning date, my main form either doesn’t calculate properly, or the
subform is blank, or the data becomes un-editable.

I am developing an inventory application based on the Inventory Control
template available for Access. I have merged it into my existing application
which has purchase and sales forms similar to those found in the Northwind
sample database. Inventory calculates correctly on my products form using a
purchases subform and a sales subform linked on productID. Perhaps I don't
have the user-specified beginning date field in the proper table. Right now I
have it on the Product record, on which the main form is based.

 

[John W. Vinson]

How can I change the date #1/1/08# from outside of the query? I would
prefer to change the date on the main form. The main form data doesn’t
contain an OrderDate field. I don’t want the user to have to change this
date every time, as they would in a parameter query. I simply cannot figure
out how to get a user-specified date into the subform query.

One idea would be to use an *unbound* textbox on the mainform; set its
DefaultValue property to #1/1/2008#. If the mainform is named MyForm and the
textbox txtStartdate, you can use [Forms]![MyForm]![txtStartdate] as a
parameter in the subform's recordsource query, and the query will simply read
the value from the form.

 

[Sammie]

Thanks for that, John. I got the unbound text box to work - it was helpful to
know about the default property. It doesn't work in my subform's query,
however. I tried to create a new field in the query
StartDate: [Forms]![Inventory]![UnboundDate]
and it returns a blank!
What am I doing wrong?

--
Thanks.
Sammie Access 2003

How can I change the date #1/1/08# from outside of the query? I would
prefer to change the date on the main form. The main form data doesn’t
contain an OrderDate field. I don’t want the user to have to change this
date every time, as they would in a parameter query. I simply cannot figure
out how to get a user-specified date into the subform query.

One idea would be to use an *unbound* textbox on the mainform; set its
DefaultValue property to #1/1/2008#. If the mainform is named MyForm and the
textbox txtStartdate, you can use [Forms]![MyForm]![txtStartdate] as a
parameter in the subform's recordsource query, and the query will simply read
the value from the form.

 

[John W. Vinson]

Thanks for that, John. I got the unbound text box to work - it was helpful to
know about the default property. It doesn't work in my subform's query,
however. I tried to create a new field in the query
StartDate: [Forms]![Inventory]![UnboundDate]
and it returns a blank!
What am I doing wrong?

I'd use the forms reference, not as a calculated field, but as part of a
criterion on the subform's datefield:

= [Forms]![Inventory]![UnboundDate] AND < {the other date}

 

[Sammie]

Thank you, John. OK, that works on my form. Now to report on it. How do I
pass the unbound date value from my form to the report based on the form? In
design view, I saved my form as a report. I keep getting the following
parameter prompt when I try to open the report:
enter paramater value
=[Forms]![inventory]![unbounddate]
I want to pass the value automatically rather than entering it each time.

--
Thanks.
Sammie Access 2003

Thanks for that, John. I got the unbound text box to work - it was helpful to
know about the default property. It doesn't work in my subform's query,
however. I tried to create a new field in the query
StartDate: [Forms]![Inventory]![UnboundDate]
and it returns a blank!
What am I doing wrong?

I'd use the forms reference, not as a calculated field, but as part of a
criterion on the subform's datefield:

= [Forms]![Inventory]![UnboundDate] AND < {the other date}

 

[John W. Vinson]

Thank you, John. OK, that works on my form. Now to report on it. How do I
pass the unbound date value from my form to the report based on the form? In
design view, I saved my form as a report. I keep getting the following
parameter prompt when I try to open the report:
enter paramater value
=[Forms]![inventory]![unbounddate]
I want to pass the value automatically rather than entering it each time.

You can do so a couple of ways; the simplest by far is to put a command button
on the form to open the report, and leave the form open while the report is
printing (or previewing). If you close the form and open the report separately
you'll get prompted - but you shouldn't if the form is still open.

 

[Sammie]

Perfect!
Thank you.
--
Thanks.
Sammie Access 2003

Thank you, John. OK, that works on my form. Now to report on it. How do I
pass the unbound date value from my form to the report based on the form? In
design view, I saved my form as a report. I keep getting the following
parameter prompt when I try to open the report:
enter paramater value
=[Forms]![inventory]![unbounddate]
I want to pass the value automatically rather than entering it each time.

You can do so a couple of ways; the simplest by far is to put a command button
on the form to open the report, and leave the form open while the report is
printing (or previewing). If you close the form and open the report separately
you'll get prompted - but you shouldn't if the form is still open.

 

[Sammie]

My inventory form and report(based on table ProductsSold) are working
perfectly. A new problem has appeared: when I use other forms based on
queries based on the ProductsSold table, I get the same parameter prompt:
enter paramater value
[Forms]![inventory]![unbounddate]
None of my forms is based on the table; all are based on queries based on
the table. However, the subforms on my inventory form have their own queries.
--
Thanks.
Sammie Access 2003

Thank you, John. OK, that works on my form. Now to report on it. How do I
pass the unbound date value from my form to the report based on the form? In
design view, I saved my form as a report. I keep getting the following
parameter prompt when I try to open the report:
enter paramater value
=[Forms]![inventory]![unbounddate]
I want to pass the value automatically rather than entering it each time.

You can do so a couple of ways; the simplest by far is to put a command button
on the form to open the report, and leave the form open while the report is
printing (or previewing). If you close the form and open the report separately
you'll get prompted - but you shouldn't if the form is still open.

 

[John W. Vinson]

My inventory form and report(based on table ProductsSold) are working
perfectly. A new problem has appeared: when I use other forms based on
queries based on the ProductsSold table, I get the same parameter prompt:
enter paramater value
[Forms]![inventory]![unbounddate]
None of my forms is based on the table; all are based on queries based on
the table. However, the subforms on my inventory form have their own queries.

Well, you're getting the prompt because the unbounddate control is being
referenced in some query on the form that you're opening - perhaps it's in the
rowsource of a combo box, or the recordsource of one of the subforms, or some
query called by your main query. I can't see the database so I have no idea
where, but it IS being referenced. Fix that reference and you'll be OK.

 

[Sammie]

I have tried to search, but there are so may queries and subforms, and so far
I can't find it. Is there a way to do a database-wide search for this
reference? If not, do you have any suggestions for a systematic search?

What about a database-wide declaration defining this date so everywhere it's
called the response is known? Keep in mind I want the user to be able to
change it.
--
Thanks.
Sammie Access 2003

My inventory form and report(based on table ProductsSold) are working
perfectly. A new problem has appeared: when I use other forms based on
queries based on the ProductsSold table, I get the same parameter prompt:
enter paramater value
[Forms]![inventory]![unbounddate]
None of my forms is based on the table; all are based on queries based on
the table. However, the subforms on my inventory form have their own queries.

Well, you're getting the prompt because the unbounddate control is being
referenced in some query on the form that you're opening - perhaps it's in the
rowsource of a combo box, or the recordsource of one of the subforms, or some
query called by your main query. I can't see the database so I have no idea
where, but it IS being referenced. Fix that reference and you'll be OK.

 

[John W. Vinson]

I have tried to search, but there are so may queries and subforms, and so far
I can't find it. Is there a way to do a database-wide search for this
reference? If not, do you have any suggestions for a systematic search?

You can use Tools... Analyze Documenter and write the output to a Word
document and search that; or use one of the third-party search and replace
tools:

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

What about a database-wide declaration defining this date so everywhere it's
called the response is known? Keep in mind I want the user to be able to
change it.

If you have a startup form, or (better) a switchboard form that is kept open,
put the textbox on it.

 

[Sammie]

Found and fixed! I hope I'm done.

Thanks so much for staying with me on this.
Sammie Access 2003