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
|
|||
|
|||
Parameter Form
I am using an unbound form (frmSpecify) to specify report
(rptTraining) criteria. Among its controls are text boxes for Start Date (txtStartDate) and End Date (txtEndDate). A parameter query (qrySpecify) is the source for rptTraining. It is based on tblTraining, which includes a field for [SessionDate]. The parameter for [SessionDate] is: Between [Forms]![frmSpecify]![txtStartDate] And [Forms]![frmSpecify]![txtEndDate]. There are other parameters such as EmployeeName, and the option of leaving parameters blank. The default value for txtEndDate on frmSpecify is: =Date(). The format for the text box is Short Date. I would like the default value for txtStartDate to be January 1 of the current year, but I am not having any success with formatting the date (e.g. =Format(Date(),"yy"). The idea was to return two digits for the current year, and concatenate it with "1/1/". Another thing I would like to do in a similar situation elsewhere is to leave txtStartDate blank in order to return all records. I realize I would have to specify that in the parameter query. For Employee I can do something like: [Forms]![frmSpecify]![txtName] Or [Forms]! [frmSpecifyRecord]![txtName] Is Null (no line break) but I cannot figure out how to do that with Between...And. I will never need to specify that [txtEndDate] Is Null, since it defaults to the current date. |
#2
|
|||
|
|||
Hi,
This will give you Jan1 of the current year: DateSerial(DatePart("yyyy",Date),1,1) HTH Dan Artuso, MVP "Bruce" wrote in message ... I am using an unbound form (frmSpecify) to specify report (rptTraining) criteria. Among its controls are text boxes for Start Date (txtStartDate) and End Date (txtEndDate). A parameter query (qrySpecify) is the source for rptTraining. It is based on tblTraining, which includes a field for [SessionDate]. The parameter for [SessionDate] is: Between [Forms]![frmSpecify]![txtStartDate] And [Forms]![frmSpecify]![txtEndDate]. There are other parameters such as EmployeeName, and the option of leaving parameters blank. The default value for txtEndDate on frmSpecify is: =Date(). The format for the text box is Short Date. I would like the default value for txtStartDate to be January 1 of the current year, but I am not having any success with formatting the date (e.g. =Format(Date(),"yy"). The idea was to return two digits for the current year, and concatenate it with "1/1/". Another thing I would like to do in a similar situation elsewhere is to leave txtStartDate blank in order to return all records. I realize I would have to specify that in the parameter query. For Employee I can do something like: [Forms]![frmSpecify]![txtName] Or [Forms]! [frmSpecifyRecord]![txtName] Is Null (no line break) but I cannot figure out how to do that with Between...And. I will never need to specify that [txtEndDate] Is Null, since it defaults to the current date. |
#3
|
|||
|
|||
Thanks for your speedy reply. I discovered that if I
created a hidden text box (txtDate) on the form, set its control source to =Date(), and substituted [txtDate] for Date in the code example (which I put into Control Source for txtStartDate), it worked. While experimenting I found that if instead of putting ="1/1/"&Format(Date(),"yy") into Control Source (instead of Default Value) for txtStartDate, that also worked. I probably could have referenced txtEndDate instead of the hidden text box in the code. That might be the cleanest of all. Anyhow, there seems to be several viable approaches. Any ideas on how to make the parameter query work with txtStartDate blank? The report's default Start Date in that case would be the earliest date for which there is a record. That part is already set up. I just can't remember how to leave Start Date blank. -----Original Message----- Hi, This will give you Jan1 of the current year: DateSerial(DatePart("yyyy",Date),1,1) HTH Dan Artuso, MVP "Bruce" wrote in message ... I am using an unbound form (frmSpecify) to specify report (rptTraining) criteria. Among its controls are text boxes for Start Date (txtStartDate) and End Date (txtEndDate). A parameter query (qrySpecify) is the source for rptTraining. It is based on tblTraining, which includes a field for [SessionDate]. The parameter for [SessionDate] is: Between [Forms]![frmSpecify]![txtStartDate] And [Forms]![frmSpecify]![txtEndDate]. There are other parameters such as EmployeeName, and the option of leaving parameters blank. The default value for txtEndDate on frmSpecify is: =Date(). The format for the text box is Short Date. I would like the default value for txtStartDate to be January 1 of the current year, but I am not having any success with formatting the date (e.g. =Format(Date(),"yy"). The idea was to return two digits for the current year, and concatenate it with "1/1/". Another thing I would like to do in a similar situation elsewhere is to leave txtStartDate blank in order to return all records. I realize I would have to specify that in the parameter query. For Employee I can do something like: [Forms]![frmSpecify]![txtName] Or [Forms]! [frmSpecifyRecord]![txtName] Is Null (no line break) but I cannot figure out how to do that with Between...And. I will never need to specify that [txtEndDate] Is Null, since it defaults to the current date. . |
#4
|
|||
|
|||
Hi,
The easiest way is probably to make the default start date on your form the earliest date in the table. You could also use something like this. I just made the date the year 1900. You could use DMin() to actually use the earliest date in the table, but the below will work. Between IIf([Forms]![frmTestDates]![txtStart] Is Null,#01/01/1900#,[Forms]![frmTestDates]![txtStart]) And [Forms]![frmTestDates]![txtEnd] Substitute your names. HTH Dan Artuso, MVP "Bruce" wrote in message ... Thanks for your speedy reply. I discovered that if I created a hidden text box (txtDate) on the form, set its control source to =Date(), and substituted [txtDate] for Date in the code example (which I put into Control Source for txtStartDate), it worked. While experimenting I found that if instead of putting ="1/1/"&Format(Date(),"yy") into Control Source (instead of Default Value) for txtStartDate, that also worked. I probably could have referenced txtEndDate instead of the hidden text box in the code. That might be the cleanest of all. Anyhow, there seems to be several viable approaches. Any ideas on how to make the parameter query work with txtStartDate blank? The report's default Start Date in that case would be the earliest date for which there is a record. That part is already set up. I just can't remember how to leave Start Date blank. -----Original Message----- Hi, This will give you Jan1 of the current year: DateSerial(DatePart("yyyy",Date),1,1) HTH Dan Artuso, MVP "Bruce" wrote in message ... I am using an unbound form (frmSpecify) to specify report (rptTraining) criteria. Among its controls are text boxes for Start Date (txtStartDate) and End Date (txtEndDate). A parameter query (qrySpecify) is the source for rptTraining. It is based on tblTraining, which includes a field for [SessionDate]. The parameter for [SessionDate] is: Between [Forms]![frmSpecify]![txtStartDate] And [Forms]![frmSpecify]![txtEndDate]. There are other parameters such as EmployeeName, and the option of leaving parameters blank. The default value for txtEndDate on frmSpecify is: =Date(). The format for the text box is Short Date. I would like the default value for txtStartDate to be January 1 of the current year, but I am not having any success with formatting the date (e.g. =Format(Date(),"yy"). The idea was to return two digits for the current year, and concatenate it with "1/1/". Another thing I would like to do in a similar situation elsewhere is to leave txtStartDate blank in order to return all records. I realize I would have to specify that in the parameter query. For Employee I can do something like: [Forms]![frmSpecify]![txtName] Or [Forms]! [frmSpecifyRecord]![txtName] Is Null (no line break) but I cannot figure out how to do that with Between...And. I will never need to specify that [txtEndDate] Is Null, since it defaults to the current date. . |
#5
|
|||
|
|||
Bruce,
I see what you're getting at with the default date being the earliest record being the easiest, but the most useful will probably be January 1 of the current year. I can probably set up Dmin() in a hidden text box and reference that if needed to find the earliest date. The more I think about it the more I realize it will rarely be necessary to determine the earliest date, so I won't spend much time on that now. We can always use 1/1/80 or something, and re-run the report after learning the earliest date. Thanks again for your help. -----Original Message----- Hi, The easiest way is probably to make the default start date on your form the earliest date in the table. You could also use something like this. I just made the date the year 1900. You could use DMin() to actually use the earliest date in the table, but the below will work. Between IIf([Forms]![frmTestDates]![txtStart] Is Null,#01/01/1900#,[Forms]![frmTestDates]![txtStart]) And [Forms]![frmTestDates]![txtEnd] Substitute your names. HTH Dan Artuso, MVP "Bruce" wrote in message ... Thanks for your speedy reply. I discovered that if I created a hidden text box (txtDate) on the form, set its control source to =Date(), and substituted [txtDate] for Date in the code example (which I put into Control Source for txtStartDate), it worked. While experimenting I found that if instead of putting ="1/1/"&Format(Date(),"yy") into Control Source (instead of Default Value) for txtStartDate, that also worked. I probably could have referenced txtEndDate instead of the hidden text box in the code. That might be the cleanest of all. Anyhow, there seems to be several viable approaches. Any ideas on how to make the parameter query work with txtStartDate blank? The report's default Start Date in that case would be the earliest date for which there is a record. That part is already set up. I just can't remember how to leave Start Date blank. -----Original Message----- Hi, This will give you Jan1 of the current year: DateSerial(DatePart("yyyy",Date),1,1) HTH Dan Artuso, MVP "Bruce" wrote in message ... I am using an unbound form (frmSpecify) to specify report (rptTraining) criteria. Among its controls are text boxes for Start Date (txtStartDate) and End Date (txtEndDate). A parameter query (qrySpecify) is the source for rptTraining. It is based on tblTraining, which includes a field for [SessionDate]. The parameter for [SessionDate] is: Between [Forms]![frmSpecify]![txtStartDate] And [Forms]![frmSpecify]![txtEndDate]. There are other parameters such as EmployeeName, and the option of leaving parameters blank. The default value for txtEndDate on frmSpecify is: =Date(). The format for the text box is Short Date. I would like the default value for txtStartDate to be January 1 of the current year, but I am not having any success with formatting the date (e.g. =Format(Date(),"yy"). The idea was to return two digits for the current year, and concatenate it with "1/1/". Another thing I would like to do in a similar situation elsewhere is to leave txtStartDate blank in order to return all records. I realize I would have to specify that in the parameter query. For Employee I can do something like: [Forms]![frmSpecify]![txtName] Or [Forms]! [frmSpecifyRecord]![txtName] Is Null (no line break) but I cannot figure out how to do that with Between...And. I will never need to specify that [txtEndDate] Is Null, since it defaults to the current date. . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Default values to load up automatically in a form based on value entered in another form | Anthony Dowd | Using Forms | 8 | August 12th, 2004 08:53 AM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |
How to syncronize a datasheet form and single form? | Sandra | Using Forms | 9 | June 16th, 2004 05:24 AM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |