Way to much in query

christopherb

Hello,
Does anyone have the answer why when this query runs it grab all the data within the given dates but also grabs all records outside the date range also!

FIND Payment WHERE Payment.DueDate<=LoggedInRegularUser.InvoiceBeginDate AND Payment.DueDate>=LoggedInRegularUser.InvoiceEndDate

As always... Thank you
Chris

tford

Chris,

Looks like you have your >= and <= backwards.

To get everything BETWEEN InvoiceBegindate and InvoiceEndDate, use:

FIND Payment WHERE Payment.DueDate>=LoggedInRegularUser.InvoiceBeginDate AND Payment.DueDate<=LoggedInRegularUser.InvoiceEndDate

Tom

christopherb

Tom,
Thank you, I had already tried that. The query acts the same as before grabbing all the records not only the records within the date range.

I will keep digin' to see if I can solve this one

Thank you
Chris

Feeldabuzz

Try taking the = out ......

I had a similar problem some months ago.

tford

Chris,

In looking at the BSV, the issue was actually in the CREATE statement.

CREATE InvoiceBatch WITH InvoiceBatch.DateTimeCreated=CURRENT_DATE, InvoiceBatch.InvoiceDate=CURRENT_DATE, InvoiceBatch.TotalAmount=SUM Payment.BalanceDue WHERE (Payment.DueDate>=LoggedInRegularUser.InvoiceBeginDate AND Payment.DueDate<=LoggedInRegularUser.InvoiceEndDate AND Payment.BalanceDue<>0), InvoiceBatch.TotalInvoices=COUNT Payment WHERE (Payment.DueDate>=LoggedInRegularUser.InvoiceBeginDate AND Payment.DueDate<=LoggedInRegularUser.InvoiceEndDate AND Payment.BalanceDue<>0)

The "WHERE..." needed to be added to calculate the appropriate batch totals in the SUM and COUNT actions.

Tom