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)

Suggested answer

Community Member on at

Like (1)

Report

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

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 on at

Like (3)

Report
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

thank you, i litle changed and now work.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

Like (0)

Report

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
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