Access asks for parameters

  • Thread starter Thread starter John F
  • Start date Start date
J

John F

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?
 
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
 
Thanks Allen,
it seems like the trick was to be consequent on the date format.

John

Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.


John F said:
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?
Click to expand...

.
Click to expand...
 
Back
Top