Pass Through Query Parameter Date

[cjman]

I am using a pass through query. This line of code works.

exec HardDrivetoCrushSP @GreaterThanDateEntered = '05/01/09'

How do I get the date to be a parameter value. I tried = [Enter Date] but that doesn't work. I am new to writing pass through queries. The BE is SQL Server 2005 and the FE is Access 2007.

 

[MSAccessRookie]

I am using a pass through query. This line of code works.

exec HardDrivetoCrushSP @GreaterThanDateEntered = '05/01/09'

How do I get the date to be a parameter value. I tried = [Enter Date] but that doesn't work. I am new to writing pass through queries. The BE is SQL Server 2005 and the FE is Access 2007.

It looks like you did it right to me. Does the Stored Procedure / Function on the SQL Server refer to the date using hte defined Parameter Name {@GreaterThanDateEntered}? [Enter Date] is the Access methid of obtaining Parameters. @Parameter is used by SQL Server.

 

[cjman]

Yes, the SP uses the @GreaterThanDateEntered.

 

[pbaldy]

You want to use a DAO QueryDef to change the actual SQL of the passthrough query. You'd concatenate the user defined date (probably from a form) with the rest of the SQL. Searching on QueryDef should turn up the code.

 

[cjman]

Thanks, I wasn't sure what to search for.

 

[cjman]

This is the code I used on a Click Event.

Dim db As Database
Dim Q As QueryDef

Set db = CurrentDb()
Set Q = db.QueryDefs("AccessQueryName")

Q.SQL = "exec SPName" & " @StartDate=" & "'" & Forms!FormName!StartDate & "'" & “,” & “@EndDate” & “’” & Forms!FormName!EndDate & “’”

DoCmd.OpenReport "ReportName”