This contradicts what you posted before as your parameters in the query seems
to be referencing a different form from the Form Date Range form which was
the form name in the SQL statement which you previously posted. If the
parameters really are referencing the Form Query Amount Paid form this would
explain why you are getting the prompts and you should just need to change
the parameter to:
=[Forms]![Form Date Range]![StartDate] And < [Forms]![Form Date Range]![EndDate]+1
As I mentioned in my original reply its also a good idea to declare
parameters of date/time data type as a date entered in short date format in
the Form Date Range form could otherwise be misinterpreted as an arithmetical
expression rather than a date. You can declare the parameters in the way I
described by switching to SQL view, or in query design view as follows:
1. Select parameters from the Query menu item.
2. In the resulting 'Query Parameters' dialogue enter the following in the
two columns on the first two rows:
[Forms]![Form Date Range]![StartDate] Date/Time
[Forms]![Form Date Range]![EndDate] Date/Time
3. Save the query.
When you open the report with the button on the form you should no longer be
prompted for the parameters.
Its possible of course that error is the other way round, that the form's
name really is Form Query Amount Paid and the parameter in the form
incorrectly references Form Date Range as in the SQL statement which you
posted. In which case you'd change the parameters to reference Form Query
Amount Paid.
Plan B:
As I explained before, you can do without the parameters completely. For
this you do as follows:
1. Remove the parameters from the criteria row of the query and save it.
2. In the Form Date Range form amend the code for the Ok button's Click
event procedure, which you do like this:
Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the Click event procedure. If there is any existing code between the
first and last lines already in place, delete this, leaving only the first
and last lines. Then paste in the following code between the two remaining
lines:
Const conREPORT= "Report Query Amount Paid"
Dim strCriteria As String
' build filter expression
strCriteria= "[Date Paid] > #" & _
Format(Nz(Me.[Start Date],#1800-00-01#,"yyyy-mm-dd") & _
"# And [Date Paid] < #" & _
DateAdd("d",1,Format(Nz(Me.[End Date],#3000-01-01#),"yyyy-mm-dd")) & "#"
' open report filtered to selected data range
DoCmd.OpenForm conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
3. Save the form.
I explained the basis for this code in my earlier reply, but on the
information you've given it should work (there were a couple of errors in the
original code!). If you don't want the form to automatically close when the
report opens just exclude the last two lines of the above.
Ken Sheridan
Stafford, England
Mavis said:
Hi,
Thanks so much for your offer. Below will be the detail of what i need and
what i have.
I had a query "Query Amount Paid" and a report "Report Query Amount Paid"
which is created with with the query.
I had created a form "Form Date Range" with 2 fields "Start Date" and "End
Date" and a "Ok" button.
In the table which he query using, there is a field "Date Paid". My query
will pull out the record with Date Paid fell within the Date range user enter
in the form.
I had add ">=[Forms]![Form Query Amount Paid]![StartDate] And <
[Forms]![Form Query Amount Paid]![EndDate]+1" under the "Date Paid" field
criteria.
The user will enter the start date and end date in the form and the click on
the "ok" button then the report will be pull out.
Ken Sheridan said:
I could walk you through it if I knew what it is you want the button to do;
open the query directly in datasheet view? open a form which shows the
results of the query? open a report based on the query?
If its to open a query then let me know the query's name. If its to open a
form or report which you've created then let me know the name of the form or
report.
Ken Sheridan
Stafford, England
:
HI,
Thanks for your detail advice but i am a new user of Microsoft access. Not
very sure how to do all those below. Do you have a easiler way for me to do
it?
:
What is the code in the OK button's Click event procedure? I assume that it
opens the query, or a form or report based on it. If it also closes the Form
Date Range dialogue form then the controls will no longer be exposed to the
query, in which case you should not close the form.
If you do want the form to close automatically then close it in the Close
event procedure of a form or report based on the query.
An alternative would be to pass the values of the two controls to variables
declared Public in a standard module, and return the values of the variables
in functions referenced by the query as the parameters (VBA variables are not
directly visible to a query). I would not recommend this approach, however,
as the variables will retain the last values passed to them, so it would be
possible to open the query (or a form or report based on it) without first
entering the data range, which could lead to it being opened inappropriately.
The simplest method of opening a form or report based on a query, which is
always to be preferred to the crude approach of opening a query in datasheet
view directly, is to remove the parameters from the query and filter the form
or report by means of the WhereCondition argument of the OpenForm or
OpenReport method, e.g.
Const conFORM= "MyForm"
Dim strCriteria As String
' build filter expression
strCriteria= "DatePaid > #" & _
Format(Nz(Me.StartDate,#1800-00-01#,"yyyy-mm-dd") & _
"# And DatePaid <= #" & _
DateAdd("d",1,Format(Nz(Me.StartDate,#3000-01-01#),"yyyy-mm-dd")) & "#"
' open form filtered to selected data range
DoCmd.OpenForm conFORM, WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
A few things to note about this:
1. Formatting the dates to the ISO standard date format makes them
internationally unambiguous.
2. By using the Nz function to return artificially low or high dates if no
date is entered makes them optional, so the results can be restricted to all
records after a single date, all records on or before a single date, all
records within the range delimited by two dates or all rows without any
restriction by date.
3. The use of the DateAdd function is preferable to simple date arithmetic,
which does rely on the implementation of the date/time data type.
Finally, if you do use date/time parameters in a query always declare them
to avoid the values entered being misinterpreted as arithmetical expressions:
PARAMETERS
[Forms]![Form Date Range]![StartDate] DATETIME,
[Forms]![Form Date Range]![EndDate] DATETIME;
SELECT TBLCargoClaims.ClaimNumber etc.
Ken Sheridan
Stafford, England
:
Hi,
I have this query which need to enter parameter for Start Date and End date.
I had created a form for the parameter. In the form "Form Date Range", there
are 2 Unbound Textbox (startdate and enddate) and 1 "Ok" button. I will like
the query to capture the start date and end date in the form for the
parameter but when i click the "OK" button, access will prompt me to enter
the parameter again.
My code as shown below. Please advice.
SELECT TBLCargoClaims.ClaimNumber, TBLValueClaim.DatePaid,
TBLValueClaim.[3rd Party Recovery], TBLValueClaim.[P&I Recovery]
FROM TBLCargoClaims INNER JOIN TBLValueClaim ON
(TBLCargoClaims.ClaimNumber=TBLValueClaim.ClaimNumber) AND
(TBLCargoClaims.BillofLading=TBLValueClaim.BillOfLading)
WHERE (((TBLValueClaim.DatePaid)>=[Forms]![Form Date Range]![StartDate] And
(TBLValueClaim.DatePaid)<[Forms]![Form Date Range]![EndDate]+1));
