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
|
|||
|
|||
Access asks for parameters
I have aform with [date1] , [date2] and [print] controls on it.
I want to retrieve data from several tables using a query. The query contains [datespan] Between [date1] And [date2]. The Print button on the Forms has a "where clause" ( [datespan]=[date1] And [datespan]=[date2]. Also there is a graph to be printed that has the same sorting as the where clause. I get 3 pop-ups asking for date1 and date2. If I give the dates three times, I get the print. Otherwise I get nothing. If I re-type "date1" and "date2" in the form's text boxes (design view), the result is OK. Until next time I log on - then it's all over again. Is there somewhere Access does not recognize the field values? |
#2
|
|||
|
|||
Access asks for parameters
If you want the query to read the text boxes on the form, you need to give
the full reference, such as: [datespan] Between [Forms].[Form1].[date1] And [Forms].[Form1].[date2] To ensure the query understands the data type correctly: a) Set the Format property of both text boxes to General Date b) Declare the parameters in the query. Open the Parameters box (menu/ribbon), and enter 2 rows: [Forms].[Form1].[date1] Date/Time [Forms].[Form1].[date2] Date/Time If you are building the WHERE clause, you need to concatenate the dates from the form into the string, e.g.: Dim strWhere As String Const strcJetDate = "#\mm\/dd\/yyyy\#" If IsDate(Me.date1) And IsDate(Me.date2) Then strWhere = "(datespan = " & Format(Me.date1, strcJetDate) & _ ") AND (datespan " & Format(Me.date2+1, strcJetDate) & ")" End If Do not change the date format above to match your regional settings. More info: http://allenbrowne.com/ser-36.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John F" wrote in message news I have aform with [date1] , [date2] and [print] controls on it. I want to retrieve data from several tables using a query. The query contains [datespan] Between [date1] And [date2]. The Print button on the Forms has a "where clause" ( [datespan]=[date1] And [datespan]=[date2]. Also there is a graph to be printed that has the same sorting as the where clause. I get 3 pop-ups asking for date1 and date2. If I give the dates three times, I get the print. Otherwise I get nothing. If I re-type "date1" and "date2" in the form's text boxes (design view), the result is OK. Until next time I log on - then it's all over again. Is there somewhere Access does not recognize the field values? |
#3
|
|||
|
|||
Access asks for parameters
Thanks Allen,
it seems like the trick was to be consequent on the date format. John "Allen Browne" wrote: If you want the query to read the text boxes on the form, you need to give the full reference, such as: [datespan] Between [Forms].[Form1].[date1] And [Forms].[Form1].[date2] To ensure the query understands the data type correctly: a) Set the Format property of both text boxes to General Date b) Declare the parameters in the query. Open the Parameters box (menu/ribbon), and enter 2 rows: [Forms].[Form1].[date1] Date/Time [Forms].[Form1].[date2] Date/Time If you are building the WHERE clause, you need to concatenate the dates from the form into the string, e.g.: Dim strWhere As String Const strcJetDate = "#\mm\/dd\/yyyy\#" If IsDate(Me.date1) And IsDate(Me.date2) Then strWhere = "(datespan = " & Format(Me.date1, strcJetDate) & _ ") AND (datespan " & Format(Me.date2+1, strcJetDate) & ")" End If Do not change the date format above to match your regional settings. More info: http://allenbrowne.com/ser-36.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John F" wrote in message news I have aform with [date1] , [date2] and [print] controls on it. I want to retrieve data from several tables using a query. The query contains [datespan] Between [date1] And [date2]. The Print button on the Forms has a "where clause" ( [datespan]=[date1] And [datespan]=[date2]. Also there is a graph to be printed that has the same sorting as the where clause. I get 3 pop-ups asking for date1 and date2. If I give the dates three times, I get the print. Otherwise I get nothing. If I re-type "date1" and "date2" in the form's text boxes (design view), the result is OK. Until next time I log on - then it's all over again. Is there somewhere Access does not recognize the field values? . |
Thread Tools | |
Display Modes | |
|
|