Form_Date does not work

[Guest]

I have a form on which I created unbound txt boxes. I have tried

using two unbound txt boxes, putting startdate in one and enddate in the
other; then tried txtstart; txtend;
Then from a post, which I can not find back, I entered the following two
expressions, each one in an unbound txt box
=" From " & [Start_Date] & " To " & [End_Date] and the other
=" From " & Format([StartDate],"Medium Date") & " To " &
Format([EndDate],"Medium Date")

Then, since I always phrase my query parameter to Between [Enter Start Date]
And [Enter End Date], i tried
="Between " & [Enter Start Date] & " And " & [Enter End Date]

With each of the expressions I've tried, I receive the #Name? error.

Also, since the whole concept of having a form as a filter for a report is
new to me, is the only thing I must do to the report/sub reports is enter the
name of the form_Date under the Filter property and set the Filter to "yes?"

I really hope someone knows what I am doing wrong with these and how to
help me? As always, any help would be greatly appreciated. k

 

[tina]

it's not quite clear how you're trying to set this up; but i think you want
two unbound textbox controls on the form, in which the user can enter Start
and End dates to be used a criteria in a query that is bound to a
report...correct? and you're going to preview or print the report while the
form is open, correct?

if so, you only need two textbox controls on the form, such as
txtStart
txtEnd

those extra calculated controls that you mentioned are not necessary.

then, in the query underlying the report, add criteria to the date field, as

Between Forms!FormName!txtStart And Forms!FormName!txtEnd

the above goes all on one line in the query criteria, regardless of linewrap
in this post. replace FormName with the correct name of the form, of course.
then, while still in query Design view, from the menu bar click Query |
Parameters. in the Query Parameters box, enter the parameters and the data
type in the columns, as

Forms!FormName!txtStart Date/Time
Forms!FormName!txtEnd Date/Time

again, taking care to use the correct form name.

now all you have to do is enter two valid dates in the form controls, and
preview/print the report, while the form is still open.

hth

 

[justme0010]

Just to verify, is your form bound to those date fields? i.e., in
View,Field List you see StartDate and EndDate listed? Also, why is one
of your examples using the underscore character in the field names and
the other is not? Are these two sets of different fields?

 

[Guest]

Tina,

Thanks for replying on Labor Day. However, I had some problems.

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it. I proceeded to
your

step 2) I hope I did this right, I clicked on the Query Builder (the three
dots) beside the query Reccord Source for my Report. I copied and pasted,
edited with my form's name, on the Criteria line under my date field. Between
[Forms]![frmDates]![txtStart] And [Forms]![frmDates]![txtEnd] (Access
automatically put the brackets)

Step 3) While in query mode, on the Menu Bar, I clicked Query Parameters,
and entered
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

Mind you, I tripled and quadrupled checked as I did what you said. Thinking
I'm ready to go, I opened the form, and I have the #Name? error. I try to
open the report anyway, and it tells me I have Invalid bracketing of
'[Forms!frmDates!txtStart]' So, I copied and pasted everything you have in
your instructions and tried again. The brackets automatically surround the
field names. I close, try to run and get the same error.

Any suggestions as to what I am doing wrong?

Christ's blessings,
k

it's not quite clear how you're trying to set this up; but i think you want
two unbound textbox controls on the form, in which the user can enter Start
and End dates to be used a criteria in a query that is bound to a
report...correct? and you're going to preview or print the report while the
form is open, correct?

if so, you only need two textbox controls on the form, such as
txtStart
txtEnd

those extra calculated controls that you mentioned are not necessary.

then, in the query underlying the report, add criteria to the date field, as

Between Forms!FormName!txtStart And Forms!FormName!txtEnd

the above goes all on one line in the query criteria, regardless of linewrap
in this post. replace FormName with the correct name of the form, of course.
then, while still in query Design view, from the menu bar click Query |
Parameters. in the Query Parameters box, enter the parameters and the data
type in the columns, as

Forms!FormName!txtStart Date/Time
Forms!FormName!txtEnd Date/Time

again, taking care to use the correct form name.

now all you have to do is enter two valid dates in the form controls, and
preview/print the report, while the form is still open.

hth

I have a form on which I created unbound txt boxes. I have tried

using two unbound txt boxes, putting startdate in one and enddate in the
other; then tried txtstart; txtend;
Then from a post, which I can not find back, I entered the following two
expressions, each one in an unbound txt box
=" From " & [Start_Date] & " To " & [End_Date] and the other
=" From " & Format([StartDate],"Medium Date") & " To " &
Format([EndDate],"Medium Date")

Then, since I always phrase my query parameter to Between [Enter Start Date]
And [Enter End Date], i tried
="Between " & [Enter Start Date] & " And " & [Enter End Date]

With each of the expressions I've tried, I receive the #Name? error.

Also, since the whole concept of having a form as a filter for a report is
new to me, is the only thing I must do to the report/sub reports is enter the
name of the form_Date under the Filter property and set the Filter to "yes?"

I really hope someone knows what I am doing wrong with these and how to
help me? As always, any help would be greatly appreciated. k

 

[Guest]

Justme0010,

Hello. Yes, my form, query, and report are bound to these date fields. The
actual field I am using to obtain my start date and my end date is the date
the check was written, ie ACkDate. Also, I would like the date range to
appear in the Medium Date format in the header of my report so it would look
more professional in the title of than 7/1/07 to 7/31/07.

When I just set the criteria in my query (as I was shown to do in all the
Access text books and classes I've taken) as Between [Enter Start Date] And
[Enter End Date], my report runs perfectly.

However, I developed a report that asked me 101 times for my parameters and
was told by D.H. that I was never ever to set parameters in queries unless I
wanted unpleasant results. I was to set them in a form. Since then, the first
of August, I have been trying to learn how to do it. I keep having similar
problems as I posted in my reply to Tina.

To answer your second question, the underscore was put between the
Start_Date because that was the way it was put in one of the examples I found
in a post (I apologize, I can not find the name of the post) It caught my eye
as one of the suggestions was how to format the date range to medium date.

Hope I gave only the correct amount of information and you can help me out;
I'll check tomorrow. Good night, and

Christ's blessings,
k

 

[John W. Vinson]

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it. I proceeded to
your

step 2) I hope I did this right, I clicked on the Query Builder (the three
dots) beside the query Reccord Source for my Report. I copied and pasted,
edited with my form's name, on the Criteria line under my date field. Between
[Forms]![frmDates]![txtStart] And [Forms]![frmDates]![txtEnd] (Access
automatically put the brackets)

Step 3) While in query mode, on the Menu Bar, I clicked Query Parameters,
and entered
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

The parameters in the Paramters dialog must be *exactly the same* - brackets
and all - as the parameters in the query. Try changing the Parameters lines to
include the brackets.

Perhaps you could also post the SQL view of the resulting query. It may well
be something else that's causing the error. One question though - is frmDates
in fact open when you run the query? You say:

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it.

Where did you "put" these? The intention is that these should be the Name
property of the textboxes; their Control Source property should be blank.

John W. Vinson [MVP]

 

[Guest]

John,

I corrected the unbound text boxes. I had the control sources as txtStart;
txtEnd, not the Name field.

I corrected that, entered my dates, left the form open, made sure the filter
was entered to frmDates and yes it was on. I opened the Query Builder, redid
Parameters. Kept getting bracketing error. I have no idea what happened
except a miracle as I just closed, looked, ran, and there it was (God has
pitty on me so many times)

Question, I would like to have the date, formated in the report header to
medium date, how do I do that?

Question, I would like to have frm_RefDate where I choose a month and year.
I tried the same rules as the frmdates, but sometimes I get more than one
month or a mixture. I'm still working.

Thank you and

Christ's blessings always,

k

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it. I proceeded to
your

step 2) I hope I did this right, I clicked on the Query Builder (the three
dots) beside the query Reccord Source for my Report. I copied and pasted,
edited with my form's name, on the Criteria line under my date field. Between
[Forms]![frmDates]![txtStart] And [Forms]![frmDates]![txtEnd] (Access
automatically put the brackets)

Step 3) While in query mode, on the Menu Bar, I clicked Query Parameters,
and entered
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

The parameters in the Paramters dialog must be *exactly the same* - brackets
and all - as the parameters in the query. Try changing the Parameters lines to
include the brackets.

Perhaps you could also post the SQL view of the resulting query. It may well
be something else that's causing the error. One question though - is frmDates
in fact open when you run the query? You say:

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it.

Where did you "put" these? The intention is that these should be the Name
property of the textboxes; their Control Source property should be blank.

John W. Vinson [MVP]

 

[Guest]

John,

I forgot to tell you that on the frm_RefDate, I forced the Query Builder on
the Row Source and chose the table MonthYear as I want the person entering
the reference month to be able to use the name not the ID#

Does that make a difference?

k

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it. I proceeded to
your

step 2) I hope I did this right, I clicked on the Query Builder (the three
dots) beside the query Reccord Source for my Report. I copied and pasted,
edited with my form's name, on the Criteria line under my date field. Between
[Forms]![frmDates]![txtStart] And [Forms]![frmDates]![txtEnd] (Access
automatically put the brackets)

Step 3) While in query mode, on the Menu Bar, I clicked Query Parameters,
and entered
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

The parameters in the Paramters dialog must be *exactly the same* - brackets
and all - as the parameters in the query. Try changing the Parameters lines to
include the brackets.

Perhaps you could also post the SQL view of the resulting query. It may well
be something else that's causing the error. One question though - is frmDates
in fact open when you run the query? You say:

I opened a form; in two unbound text boxes, I put in one, txtStart; in the
other, txtEnd. Being sure that would work, I did not try it.

Where did you "put" these? The intention is that these should be the Name
property of the textboxes; their Control Source property should be blank.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

I corrected the unbound text boxes. I had the control sources as txtStart;
txtEnd, not the Name field.

I corrected that, entered my dates, left the form open, made sure the filter
was entered to frmDates and yes it was on. I opened the Query Builder, redid
Parameters. Kept getting bracketing error. I have no idea what happened
except a miracle as I just closed, looked, ran, and there it was (God has
pitty on me so many times)

Question, I would like to have the date, formated in the report header to
medium date, how do I do that?

Put a textbox in the report header. Set its Format property to

Medium Date

and its Control Source to

=Forms!frmRefDate!txtStart

Question, I would like to have frm_RefDate where I choose a month and year.
I tried the same rules as the frmdates, but sometimes I get more than one
month or a mixture. I'm still working.

A month and a year IS NOT A DATE. A Date/Time value in Access is a precise
point in time (accurate to a few microseconds); if it is just a date, then
it's midnight on that date. If you need to store non-specific date data then
you may want to have two or three fields - the year, month, and perhaps day.
Don't use the reserved fieldnames Year, Month, or Day though. Your queries
will be more complex because you'll be matching an imprecise date with an
imprecise criterion.

John W. Vinson [MVP]

 

[John W. Vinson]

I forgot to tell you that on the frm_RefDate, I forced the Query Builder on
the Row Source and chose the table MonthYear as I want the person entering
the reference month to be able to use the name not the ID#

I'm sorry, I have NO idea what you mean by this.

Could you post the SQL view of the RowSource of your query? and perhaps a
description of the structure of your MonthYear table? Also describe what you
mean by "the reference month" - that's not a term I know.

John W. Vinson [MVP]

 

[Guest]

John,

The reference to a month and year is the only thing that ties many of my
tables together, I think. I'll try to use the IncDate as the Master/Child
link fields for my Cash and InKind Donations and see if that will work.

Did as instructed with the u/c txt box; just got 8/1/07

Format: Medium Date
Control Source =Forms!frmDates!txtStart

Until I hear from you again, I'll keep working. Incidently do you know
anything about calculating an ending balance in a main report with two sub
reports? I hope so,

God bless you.

k

 

[Guest]

John,

I have a table which has the month and year as that is the only common link
I could find for my information. the table consists, currently, of 12 months
Oct 2006, Nov 2006 . . . Sep 2007. You asked for the query SQL view of it.
Here it is:

SELECT tblMONTHYEAR.MONTH_YEAR, tblMONTHYEAR.MonthYear
FROM tblMONTHYEAR;

The frm_RefDate I created, has an unbound text box, which is blank. However,
I clicked on the properties of the unbound ext box and on Row Source Type:
table/query, I clicked on the three dots beside it . . . (I think it is
called query builder. On Row source, I have:
SELECT tblMONTHYEAR.MONTH_YEAR, tblMONTHYEAR.MonthYear FROM tblMONTHYEAR

I just put the label name as Reference Month.

I am, however, concerned about using this as you made the staement earlier
that it is not a Date/Time as in Access that is a very specific time period.

I chose the MonthYear for the table. In each of the other tables, I have the
field named [RefDate]

As I said earlier, when I group my information by the MonthYear, it seems to
total them in a SumQuery whereas it does not when I use a date/time field.
Perhaps because I have and IncomeDate [IncDate]; for the expenses, the check
date, [ACkDate] for bank A; for bank l, I have [LCkDate]. I was not making
any progress in linking anything so I thought, what is common in all of them,
the month and year of whatever it is. Now you tell me month year and date are
specific to Access and I need to change those names.

After reviewing the information above, please tell me if I need to and
please suggest a better field name.

Christ's blessings,
k