I am selecting records using a date range…

select tablename with field between "date" and "date"

Although there are records there, I cannot select them using the above statement. The dict is right justified and the indexes are good, no errors in database. I can select the records if I use…

select tablename with data field eq "date"

I am soooo cornfused.

This is and OpenInsight application and does work properly in the Arev version?????

Any help would be greatly appreciated.

Jennifer

I think the BETWEEN statement means greater than and less than not greater than or equal to and less than or equal to so if the dates you are expecting are the same as the dates used in the comparison that might be why they aren't being displayed. Try SELECT FILE WITH DATE ]= "DATE1" AND WITH DATE ⇐ "DATE2"

The BETWEEN statement is usually much quicker though.

Paul,

I believe you are correct, the BETWEEN keyword excludes the surrounding values. Jennifer, you might also try FROM "Date" TO "Date", but we just usually do what Paul suggests.

[email protected]

SRP Computer Solutions, Inc.

Jennifer

If you are converting from AREV to OI, you need to be very careful with the Select statements because they bracket up differently. Make sure that the data that you expect to get, is the data you do get.

My worst nightmare was the combination lists of stuff.

Ie if you want SALES for the months of August and September where the manager is any one of Fred, Sara or Chris

with AREV you will feed in

SELECT SALES WITH

MANAGER EQ "FRED" "SARA" "CHRIS"

AND SALE_MONTH EQ "AUGUST" "SEPTEMBER"

and you get

sales where (manager is one of Fred, Sara, or Chris) and (month is August or September).

Ie the criteria against the same field bracket together first.

But with OI it brackets up completely differently.

given

SELECT SALES WITH

MANAGER EQ "FRED" "SARA" "CHRIS"

AND SALE_MONTH EQ "AUGUST" "SEPTEMBER"

you get strict left to right bracketing

((((SALES with MANAGER EQ "FRED")

OR MANAGER EQ "SARA")

OR MANAGER EQ "CHRIS")

AND SALE_MONTH EQ "AUGUST")

OR SALE_MONTH EQ "SEPTEMBER"

So you will get all sales in September regardless of Manager - for starters, which almost certainly isn't what you intended. I found for some things I couldn't use a Reduce statment which allows bracketing, and I'd end up with sequential calls to RLIST (which doesn't allow bracketing).

Good luck, and if you really want to be cornfused, do a search for "Reduce".

Scott

View this thread on the forum...