SQL Variable and User Input

[ImStevenB]

OK - I have a problem - other than mental ;-)

I am creating a query in SQL that will look ahead to see what documents are due to be completed.

If I do the math of a 30 60 90 day look ahead from the present date - it works fine.

HOWEVER, they want to be able to enter the number of days that it will look ahead. I have tried to define a variable in the query and keep getting syntax errors.

How do I create this variable, prompt for the input and add the number to the current date.

Everything I have tried, researched and gleaned from various posts has not provided the answer I need.

Your time and help is appreciated. Im Steven B

http://www.myarcadia.net

 

[cmmrfrds]

One way would be to create a public variable in the standard module and return it in a function. Create a Form for the user to enter the number of days and store the days in the public variable.

public ndays integer

Function ReturnDays() as integer
ReturnDays = ndays
End Function

IN a Form
ndays = me.yourNumberfield

IN the Form to return resultset
Dim sql as string
sql = &quot;Select * from table where yourdate < &quot;
sql = sql & &quot;#&quot; & dateadd(&quot;d&quot;,ReturnDays,Now()) & &quot;#&quot;

me.recordsource = sql

 

[tlbroadbent]

You could also try this in a parameter query.

Select * From Table
Where DateCol < date()+[Enter the number of days];

This will ask for the number of days, add to today's date and select record with a date lees than the resulting date. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[ImStevenB]

Thanks Terry,

I knew there was a way to do this in SQL - Ijust couldn't find it.

I used the PARAMETERS setting and it worked like a charm.

Thanks again.

Im Steven B

http://www.myarcadia.net