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

[DavidWE]

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.

 

[pbaldy]

Between Forms!FormName.txtStartDate And ...

 

[DavidWE]

At someone's suggestion, I created the following query and created a report based on that 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];

I have the following function that runs when a user clicks a submit button:

Function PrintTrans()
Dim strCriteria As String
strCriteria = "StartDate = " & txtStartDate & _
" And EndDate = " & txtEndDate
DoCmd.OpenReport "TransQueryForm", , , strCriteria
End Function

That generates the following error:

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


Does anyone know what is causing the error? Do I have my quotes wrong? I'm not even sure if I am going about it the right way.

Thanks for any suggestions.

 

[pbaldy]

The 2 date values must be surrounded by "#".

 

[DavidWE]

Thanks, Paul. I'll try:

Between Forms!DateQuery2.txtStartDate And Forms!DateQuery2.txtEndDate

Maybe that will do it.

 

[DavidWE]

I decided to try it with the quotes first. This is what I have:

strCriteria = "StartDate = '" & txtStartDate & "'" & _
" And EndDate = '" & txtEndDate & "'"
DoCmd.OpenReport "TransQueryForm", , , strCriteria

I no longer get the error, but the OpenReport command brings up input boxes for the start date and end date. I am trying to get it to accept the dates from the textboxes rather than through input boxes. What am I doing wrong? Am I using the wrong approach?

 

[pbaldy]

Not quotes:

The 2 date values must be surrounded by "#".

 

[ronculbert]

A differant approch

This is a little differant then what you are tring but I tought I would throw it in any how.

 

[DavidWE]

I've tried it two ways.

The following gives a "Syntax error in date" error:
strCriteria = "#StartDate# = '" & txtStartDate & "'" & _
' " And #EndDate# = '" & txtEndDate & "'"

This one gives a "missing parameter" error:
strCriteria = "#StartDate# = " & txtStartDate & _
" And #EndDate# = " & txtEndDate

What am I doing wrong?

 

[DavidWE]

Paul,
I went with your first suggestion:
Between Forms!DateQuery2.txtStartDate And Forms!DateQuery2.txtEndDate

That works. Thank you.

 

[DavidWE]

Ron,

I might use something similiar to what you suggested for some other reports. Thanks for the help.

 

[pbaldy]

I've tried it two ways.

The following gives a "Syntax error in date" error:
strCriteria = "#StartDate# = '" & txtStartDate & "'" & _
' " And #EndDate# = '" & txtEndDate & "'"

This one gives a "missing parameter" error:
strCriteria = "#StartDate# = " & txtStartDate & _
" And #EndDate# = " & txtEndDate

What am I doing wrong?

Sorry, but this made me laugh out loud. I forget how confusing it can be when you start out. I said "#" surrounding the values:

strCriteria = "StartDate = #" & txtStartDate & "# And EndDate = #" & txtEndDate & "#"

In any case, glad you got a solution that works for you. I suspect you would have wanted to use Between here anyway.

 

[DavidWE]

I've used stored procedures with VB.Net and C# and don't think I've ever used "#" passing parameters. I don't know if that is unique to interacting with reports or not. I'll find out soon enough. Yes, I'm new to programming in VBA with Access. I have written programs in VB and C# that interact with Access. Some of the differences in syntax, at least the syntax I'm familiar with , are really throwing me right now.

 

[Adeptus]

# is Access's date delimiter. Kind of like putting something in quotes forces it to be evaluated as a string.
Not unique to reports, but unique to Access.
I think it's also used in MS SQL? Though SQL is smarter about converting strings to dates (from memory...)

I know what you mean about syntax differences...
I have recently been switching between Access (with VBA), MS SQL, VB6, and VB.NET
(oh, and ADO & DAO)

 

[pbaldy]

# is Access's date delimiter...I think it's also used in MS SQL?

Unique to Access in this instance; SQL Server uses a single quote.