How to filter records by two date fields?

when i use this query in in ExecuteQuery() it shows “Lexical error” what is the error"??

qbr.value(strfmt(’(%1<=%2)&& (%3>=%4)’,fieldstr(mytable,Date),Date2StrXpp(d),fieldstr(mytable,ToDate),Date2StrXpp(d)));

Why do you insist on trying to develop something complicated instead of using queryRange() method, as I told you more than a week and several threads before? Like this:

qbr.value(queryRange(fromDate, toDate));

My recommendation is that you don’t use the extended query syntax until you get much more experience. Newbies like yourself tend to abuse it and often get into troubles, as you see. Stick to the “normal” options offered by the query framework.

when i use this condition condition are check only at todate…i wont check the condition fromdate???
Thats my question…

qbds = this.query().dataSourceTable(tableNum(mytable));

[tag:s2],s3 is the value of date…

All right, please explain what functional requirement are you trying to implement, instead of just showing some code (that clearly doesn’t do what, otherwise you wouldn’t ask here, so it doesn’t explain much).

i have two field in table…
i want to know how to build QueryRange??between two fields

Now we know that you have two fields, but nothing about the range. What the input for the range? What result do you want to get? Please give us an example.
You seem to be dealing with the for many days, so hopefully you’ll find five minutes to properly explain your goal.

in my table fromdate ,Todate,name…
based on the fromdate and todate all the name should be viewed on the form.

You told me nothing except that you want to do something with FromDate and ToDate fields, which I already new. But you still didn’t explain how you want to use them to filter the data and what’s the input. For example, maybe you want to return all record where FromDate it higher or equal to the current date and ToDate if lower or equal to the current date, but we can’t know until you tell us. You might want to do an infinite number of other things with these two fields.

If you want our help, it should be you who explain what you need. Don’t wonder why you don’t get any answer, if you don’t do better job in that. I won’t interrogate you every time.

fromdate is higher then current Date… To date is lower then Current Date

Great! Next time, please try to explain it by yourself. You’ll save several days by that.

Your requirement clearly requires two ranges - one for FromDate and one for ToDate. And what should be values of those ranges should be clear from your two sentences above. You also know that you can use queryRange() method, because I personally told you that several times. Now just put it all together:

date d = systemDateGet(); // Or some other date

ds.addRange(fieldNum(YourTable, FromDate)).value(queryRange(d+1, dateMax()));
ds.addRange(fieldNum(YourTable, ToDate)).value(queryRange(dateNull(), d-1));

Easy, isn’t it?

Now don’t forget to clean up your threats, as I already asked a few times. Many thanks!

hey sorry i couldn,t get it…What is mean d+1??how can u add date??

can u explain clearly…

d+1 mean the date plus one day.

You can learn basics of X++ from AX documentation. In this case, you can find it on MSDN under Primitive Data Types > Dates.

If the question about filtering is answered, please make sure you mark the thread accordingly.