Avatar of Richard Teasdale
Richard Teasdale
Flag for United Kingdom of Great Britain and Northern Ireland asked on

autofill a parameter

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?


Avatar of undefined
Last Comment
Richard Teasdale

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Richard Teasdale

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

     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?
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
Pavel Celba

It seems wscostu is much faster :-). Please award all points to him.

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

months = MONTH(DATE())
will do the trick for you
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Richard Teasdale

Thanks you very much wcsoctu.
And thanks - as usual - to pcelba. Very gracious of you.