access query conversion to SPROC

[rahuldev999]

Hi
can anyone help me in conversion of access query into equivalent stored procedure or views or functions.
In the below query, how one can proceed with the "where" clause conversion into the stored procedure.Problem is not in converting the IIF function but certainly with the "where" clause.Help on this will be highly appreciated.

SELECT t_activity.no_activity, IIf([activity_steps]="GP","Plan",IIf([activity_steps]="Prov","Appr.",
IIf([activity_steps]="Fix","Fix",[activity_steps]))) AS act_steps
FROM t_activity_brand, t_customer INNER JOIN t_activity ON
t_customer.code_customer = t_activity.code_customer
WHERE (((CDate([Forms]![f_gant]![txt_debut_periode])) Between [date_start_activity] And
[date_end_activity])) OR (((CDate([Forms]![f_gant]![txt_fin_periode])) Between [date_start_activity] And
[date_end_activity])) OR (((t_activity.date_start_activity) Between [Forms]![f_gant]![txt_debut_periode] And
[Forms]![f_gant]![txt_fin_periode])) OR
(((t_activity.date_end_activity) Between [Forms]![f_gant]![txt_debut_periode] And
[Forms]![f_gant]![txt_fin_periode]))
GROUP BY t_activity.no_activity, IIf([activity_steps]="GP","Plan",IIf([activity_steps]="Prov","Appr.",
IIf([activity_steps]="Fix","Fix",[activity_steps]))), t_customer.sm_or_key_acc, t_activity.activity_cancelled,
t_activity_brand.activity_brand_cancelled

Thanks in advance!

regards
rahul

[GolferGuy]

What is your question in regards to the WHERE clause? Is it how a stored procedure can get to the date that is referenced from a form? Or is there a different question you have.

[GolferGuy]

just in case the answer to my question is Yes, here is how you would get these two dates from the form into the stored procedure.
the stored procedure would be created in this manner:

CREATE  PROCEDURE dbo.spYourProcedureName
      @DebutPeriode as Date
      @FinPeriode as Date
SELECT ..... 
    WHERE @DebutPeriode 
       BETWEEN [date-start_activity] 
       AND [date_end_activity] 
       OR @FinPeriode 
       BETWEEN [date-start_activity] 
       AND [date_end_activity] 
   .....

Within your VBA code that will either run the stored procedure, or else VBA code that is run just before a report or form uses this stored procedure, you will need to prepare the pass-through query within Access to pass the correct dates to the stored procedure. this is what I do:

CurrentDb.QueryDefs("spYourProcedureName").SQL = "exec spYourProcedureName " & CDate([Forms]![f_gant]![txt_debut_periode]) & ", " & CDate([Forms]![f_gant]![txt_fin_periode])

I do not remember if you need to put hash signs (#) on both ends of these dates or not. Or if you need some other marker for SQL Server. But other than that, this example is correct.