Access asks for parameters

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?
 
A

Allen Browne

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
 
J

John F

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?

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top