Link to home
Get AccessLog in
Avatar of Richard Teasdale
Richard TeasdaleFlag for United Kingdom of Great Britain and Northern Ireland

asked on

autofill a parameter

HI:
I run a  parameterised query which prompts for a month (as an integer), then returns all sales orders from a MSSQL database for the month selected. I would like this to run at night - a nice backup position - but puzzling over how to get a parameter to 'autofill' with the current month. I use the foxpro 9 views and queries, and guess I will have to convert to writing the SQL code 'straight in'   to the method on the form, and unsure of both how to do it, and how to  insert the month into the parameter. Is this something that can be done?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of wcsoctu
wcsoctu

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Richard Teasdale

ASKER

Thanks, wcsoctu. I have opened the view in SQL and here is the extract:

SELECT *
 FROM ;
     dbo.vvdates Vvdates ;
    INNER JOIN dbo.GOOrders Goorders ;
   ON  Vvdates.date = Goorders.Delivered;
 WHERE  Vvdates.year = ( 2014 );
   AND  (  Goorders.DocNo > ( 0 );
   AND  Vvdates.month = ( ?months ) )

Could you explain where the variable goes; or do I have to 'pass' the SQL statement in code rather than calling a view?
Avatar of Pavel Celba
Parametrized view just means to store the parameter value into the variable used as a parameter.

Let say the view is defined as

CREATE SQL VIEW xyz AS  SELECT * FROM SomeTable WHERE  SomeColumn = ?lnMonthParameter

and if you assign month value into the lnMonthParameter before the VIEW is open then it does not ask for the parameter value:

lnMonthParameter = MONTH(DATE())
USE xyz
etc.
It seems wscostu is much faster :-). Please award all points to him.

 ( ?months )   is the parameter variable enclosed in parenthesis.

So
months = MONTH(DATE())
will do the trick for you
Thanks you very much wcsoctu.
And thanks - as usual - to pcelba. Very gracious of you.