Query Parameter

[Former Member]

Hi all,

I hava a query to run on SAP B1 Query Executer with a Date Parameter, so when i execute a query it should ask for the date parameter which the user will pick it up from the datetime picker.

But i find that the [%0] or any other [%..] variable is used for only the table's field.

so is there any possible way to do it.

i have some pseudo query like

*Select * from JDT1 T1 where T1.RefDate=GetLastDateOfMonth('[%0]')*

[Former Member · Accepted solution]

Hi,

Is this GetLastDateOfMonth() an user function by you?

Thanks,

Gordon

[Former Member · Accepted solution]

Hi GordonDu ,

yes, GetLastDateOfMonth() is my userFunction, and i have to pass the date as parameter to that function, which will return me the last date of the month.

regards

Bikram

[Former Member · Accepted solution]

In this case, only predefined parameter may work for you.

Try Declare a variable first.

[Former Member · Accepted solution]

Hi GordonDu,

I have tried for predefined parameter as

Declare @myParam as datetime

set @myParam=[[%0]]

Select * from JDT1 where RateDate=GetLastDateOfMonth(@myParam)

And it still is not working, so i tried to set my parameter as

set @myParam=(Select RateDate form ORTT where RateDate=[[%0]])

regards

Bikram

[Former Member · Accepted solution]

What is the result of your last try?

[Former Member · Accepted solution]

Hi,

The result of my Last try was, it prompts a window which shows the list of RateDates, but when i select one of the date, it will show an error message, it means it is not working

regards

Bikram

[Former Member · Accepted solution]

Could you post the first few line of your function GetLastDateOfMonth()?

[Former Member · Accepted solution]

Hi

The first line of my function GetLastDateOfMonth() is

(DateAdd(Month,1,@Date-Day(@Date)+1)-1)

where @Date is passed as parameter to this function

regards

Bikram

[Former Member · Accepted solution]

Do you set up @Date parameter explicitly?