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

All responses (6)

Answers (2)

maxhammer on at

Like (1)

Report
RE: Date range in query value

helped from Ivan:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime)); queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), this.dboConvertDateToDateTime(DateTo.dateValue())));
André Arnaud de Cal... 295,455 Super User 2025 Season 1 on at

Like (0)

Report

RE: Date range in query value

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

Like (0)

Report

RE: Date range in query value

thank you, i litle changed and now work.
Verified answer

Ivan (Vanya) Kashperuk on at

Like (3)

Report
RE: Date range in query value

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())));
Verified answer

Community Member on at

Like (0)

Report

RE: Date range in query value

Please follow following link for more details

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

Please verify and let me know if it solves your problem
Suggested answer

Community Member on at

Like (1)

Report

RE: Date range in query value

You need to create expression in Range value

some thing line this

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