Passing Parameters to a Query from a Form to Filter a Report

[DavidW]

I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.

Here is the query created as a stored procedure:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];

What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?

Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?

Thanks.

 

[Nick 'The database Guy']

Hi David,

I think that this will work.

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![frmFormName]![StartDate]
And [Forms]![frmFormName]![EndDate])
ORDER BY [Transactions].[Date];

Good luck,

Nick

 

[DavidW]

Thanks, Nick.
Are you saying that I would save that query and create a report based
on it?

Also, how would I set StartDate = txtStartDate and EndDate = txtEndDate
in my code?

Hi David,

I think that this will work.

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![frmFormName]![StartDate]
And [Forms]![frmFormName]![EndDate])
ORDER BY [Transactions].[Date];

Good luck,

Nick

I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.

Here is the query created as a stored procedure:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];

What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?

Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?

Thanks.

 

[DavidW]

Thanks, Nick.
Are you saying that I would save that query and create a report based
on it?

Also, how would I set StartDate = txtStartDate and EndDate = txtEndDate
in my code?

Hi David,

I think that this will work.

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![frmFormName]![StartDate]
And [Forms]![frmFormName]![EndDate])
ORDER BY [Transactions].[Date];

Good luck,

Nick

I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.

Here is the query created as a stored procedure:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];

What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?

Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?

Thanks.

 

[Nick 'The database Guy']

Hi David,

Yes that is precisley what I am saying.

Good luck

Nick

Thanks, Nick.
Are you saying that I would save that query and create a report based
on it?

Also, how would I set StartDate = txtStartDate and EndDate = txtEndDate
in my code?

Hi David,

I think that this will work.

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![frmFormName]![StartDate]
And [Forms]![frmFormName]![EndDate])
ORDER BY [Transactions].[Date];

Good luck,

Nick

I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.

Here is the query created as a stored procedure:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];

What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?

Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?

Thanks.

 

[DavidW]

OK, I created the query and the report based on that query. The name of
the report is TransQueryForm. The form is DateQuery2.

Here is the query:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![DateQuery2]!StartDate And
[Forms]![DateQuery2]!EndDate)
ORDER BY [Transactions].[Date];


Here is the code behind my form:

Function PrintTrans()
Dim strCriteria As String
strCriteria = "StartDate = " & txtStartDate & _
" And EndDate = " & txtEndDate

DoCmd.OpenReport "TransQueryForm", , , strCriteria
End Function

The code runs after a submit. It generates an error:

Run-Time Error '3075':
Syntax Error (Missing operator) in query expression '(StartDate =
8/5/2006 And EndDate = 8/15/2006')

I'm not sure what the missing operator could be. Any suggestions?

Thanks

 

[Nick 'The database Guy']

I don't know why you are defining more criteria in code. But in any
case your missing operator error is occuring because at the start you
have enclosed the brackets within the ' and at the end not. Could be
other things as well, but that is most likely.

Good luck,

Nick

 

[DavidW]

Nick,
I didn't fully understand how the form, query, and report were
interacting with each other. I realized I did not need to define the
criteria in the code and got it to work.

Thanks for your help.

I don't know why you are defining more criteria in code. But in any
case your missing operator error is occuring because at the start you
have enclosed the brackets within the ' and at the end not. Could be
other things as well, but that is most likely.

Good luck,

Nick

OK, I created the query and the report based on that query. The name of
the report is TransQueryForm. The form is DateQuery2.

Here is the query:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [Forms]![DateQuery2]!StartDate And
[Forms]![DateQuery2]!EndDate)
ORDER BY [Transactions].[Date];


Here is the code behind my form:

Function PrintTrans()
Dim strCriteria As String
strCriteria = "StartDate = " & txtStartDate & _
" And EndDate = " & txtEndDate

DoCmd.OpenReport "TransQueryForm", , , strCriteria
End Function

The code runs after a submit. It generates an error:

Run-Time Error '3075':
Syntax Error (Missing operator) in query expression '(StartDate =
8/5/2006 And EndDate = 8/15/2006')

I'm not sure what the missing operator could be. Any suggestions?

Thanks