Date range in query value

(0) Share

Report

Posted on by maxhammer

I have next code in lookup:

public void lookup()
{
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
     QueryBuildRange queryBuildRange2;
     QueryBuildRange queryBuildRange3;

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SalesTable), this);

    if(DateFrom.dateValue() && DateTo.dateValue())
    {
        sysTableLookup.addLookupfield(fieldNum(SalesTable, CustAccount));

        queryBuildDataSource = query.addDataSource(tableNum(SalesTable));

        queryBuildDataSource.addGroupByField(fieldNum(SalesTable, CustAccount));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime));
        queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), dateNull()));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime));
        queryBuildRange.value(SysQuery::range(dateNull(), this.dboConvertDateToDateTime(DateTo.dateValue())));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, InventSiteId));
        queryBuildRange.value(editInventSiteId.text());

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

    }
    //super();

Value of this query is:

Query  object 2bffea00: SELECT * FROM SalesTable(SalesTable_1) GROUP BY SalesTable.CustAccount WHERE ((createdDateTime>='2015-01-03T00:00:00') OR (createdDateTime<='2015-01-04T00:00:00')) AND ((InventSiteId = N'4'))

Here show OR between two dates, but need AND. How write correct Date range in query?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Sort by

Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

You need to create expression in Range value

some thing line this

strFmt('((A == %1) && (B == %2))',x,y)

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Please follow following link for more details

msdn.microsoft.com/.../aa893981.aspx

Please verify and let me know if it solves your problem

Was this reply helpful? Yes No
Verified answer

Ivan (Vanya) Kashperuk Microsoft Employee on at

Like (3)

Report
Copy link

Link copied!
No, don't use extended ranges, it is not necessary here.

You were using the range method correctly (almost).

Replace your code:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime)); queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), dateNull())); queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime)); queryBuildRange.value(SysQuery::range(dateNull(), this.dboConvertDateToDateTime(DateTo.dateValue())));

with this:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime)); queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), this.dboConvertDateToDateTime(DateTo.dateValue())));

Was this reply helpful? Yes No
maxhammer on at

Like (0)

Report
Copy link

Link copied!

thank you, i litle changed and now work.

Was this reply helpful? Yes No
André Arnaud de Cal... 305,475 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Maxhammer,

Would you mind to indicate which answer helped and did not helped you? Then this thread will get the status answered and other community members will be directed to the correct answer.

Was this reply helpful? Yes No
maxhammer on at

Like (1)

Report
Copy link

Link copied!
helped from Ivan:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime)); queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), this.dboConvertDateToDateTime(DateTo.dateValue())));

Was this reply helpful? Yes No