If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form_Date does not work
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 |
#2
|
|||
|
|||
Form_Date does not work
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 "znibk" wrote in message ... 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 |
#3
|
|||
|
|||
Is StartDate and EndDate coming from the unbound textboxes, or are they field names from tables? Also, why is one of your examples using the underscore character in the field names and the other is not? If they are coming from the unbound textboxes, they should just use the textbox names, like =" From " & [txtstart] & " To " & [txtend] or whatever the names of the textboxes are.
Last edited by justme0010 : September 4th, 2007 at 02:09 AM. |
#4
|
|||
|
|||
Form_Date does not work
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 "tina" wrote: 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 "znibk" wrote in message ... 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 |
#5
|
|||
|
|||
Form_Date does not work
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 "justme0010" wrote: 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? -- justme0010 |
#6
|
|||
|
|||
Form_Date does not work
On Mon, 3 Sep 2007 21:02:01 -0700, znibk
wrote: 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] |
#7
|
|||
|
|||
Form_Date does not work
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 "John W. Vinson" wrote: On Mon, 3 Sep 2007 21:02:01 -0700, znibk wrote: 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] |
#8
|
|||
|
|||
Form_Date does not work
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 "John W. Vinson" wrote: On Mon, 3 Sep 2007 21:02:01 -0700, znibk wrote: 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] |
#9
|
|||
|
|||
Form_Date does not work
On Tue, 4 Sep 2007 19:24:01 -0700, znibk
wrote: 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) It's often going to work right when you get all the errors fixed... g 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] |
#10
|
|||
|
|||
Form_Date does not work
On Tue, 4 Sep 2007 19:30:01 -0700, znibk
wrote: 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] |
|
Thread Tools | |
Display Modes | |
|
|