I created a select as follows:

SELECT EMP WITH HIRE_DATE BETWEEN LM_1ST_DATE AND LM_LAST_DATE

LM_1ST_DATE and LM_LAST_DATE are symbolics I created which calculate the 1st and last days of the previous month.

When I run the select on the entire database (no select done previously), I get a "0 rows found" message.

If I, however, do a select to get a smaller (test) population, it finds and selects the desired records.

What's up?

HIRE_DATE is an indexed field (although the index is not being used in either instance. We reindex the files every weekend, so I don't think that's the problem (same results on Friday and following Monday).

Thank you.

My experience with select on indexed fields suggests

a corrupted index file. Try removing the index from

the field (your select will work properly with no index)

and then re-define the index and hopefully your select

will work. If not, then remove all indexing from the

file and re-define them all anew. This last try deletes

the entire index file, not just "fix" the old one.

(Maybe someone else has a neater way to force a new

clean index file.) good luck!

Try creating a symbolic such that

If {HIRE_DATE} ] {LM_1ST_DATE} and {HIRE_DATE} Sprezzatura, Inc.[/url]

I have found that there is a problem selecting with a date field using the "between" operator. Try the select using greater than and less than criteria.

You might also verify that you have some basic things set up correctly.

Are the date fields involved all set as right-adjusted. Are your symbolics set with input conversion (D) and with output conversion D4/ or D2/.

How does the select work when you enter the dates explicitily (e.g. SELECT EMP WITH HIRE_DATE BETWEEN "12/1/97" AND "12/31/97".

Note: When you select against the entire database the index 'should' get used; but when you start with an existing select list it won't.

Hope this helps

Lauren Floyd

[email protected]

Thanks to all with your suggestions.

Here's what I found out/did to fix my problem….

The date fields were right justified.

It worked correctly when I explicitely selected on "12/01/97" and "12/31/97".

My symbolics were set up with appropriate input and output conversions for dates.

I got the same results using greater than and less than as opposed to between.

I wanted to use the symbolics to compare to more than just the HIRE_DATE, so I did not try Aaron's suggestion.

What I did find out was that when I selected using another field, TERM_DATE, it worked. TERM_DATE is not an indexed field. I concluded that there was either a problem with the index or something about my symbolic which is not compatible with the index format.

I will follow up to see if removing/redefining the index solves the problem at its core, but in the meantime, I created a symbolic called HIRE_DATE_NOINDEX which merely points at the HIRE_DATE. This works like a charm.

Yahoo!

View this thread on the forum...