Xref: utzoo comp.sys.ibm.pc:15785 comp.databases:1018
Path: utzoo!attcan!uunet!husc6!bloom-beacon!mit-eddie!bu-cs!purdue!decwrl!hplabs!sdcrdcf!trwrb!scgvaxd!wlbr!etn-rad!markus
From: markus@etn-rad.UUCP (Markus Richardson)
Newsgroups: comp.sys.ibm.pc,comp.databases
Subject: dBase tricky INDEXing question...
Message-ID: <529@etn-rad.UUCP>
Date: 20 May 88 23:45:56 GMT
Organization: Eaton Inc. IMSD, Westlake Village, CA
Lines: 54

It seems like I am using a database in a very common way, but I cannot for
the life of me figure out how to get dBase to index it correctly.  

My trans[action].dbf file has five fields.  The first two fields I wish to 
be combined together (for the index) in order to have the records in an order 
based upon the ordering of the first two fields.  Simple enough, right?  I 
guess if both fields were of the same data type it would be no problem,
but as it is the first field (account number) is a numeric type and the 
second field (date of transaction) is a date type.

acct_no = 1000
SEEK acct_no
IF .NOT. FOUND() THEN
	RETURN
ENDIF
DO WHILE (acct_no = trans->account_no)
	? "account_no="+trans->account_no+"  date="+DTOC(trans->date)
	SKIP
ENDDO
RETURN

Attempt 1:
SET EXACT OFF
USE Trans ALIAS Trans
INDEX ON LTRIM(STR(trans->account_no))+DTOC(trans->date) TO Trans.ndx
SEEK LTRIM(STR(account_no))          && Finds the correct matching records but
                                      && they are NOT in order of trans->date!

Attempt 2:
SET EXACT OFF
SET DATE ANSI
USE Trans ALIAS Trans
INDEX ON LTRIM(STR(trans->account_no))+;
	LTRIM(STR(trans->date - CTOD("80.01.01"))) TO Trans.ndx
SEEK LTRIM(STR(account_no))          && Finds the correct matching records but
                                      && they are NOT in order of trans->date!

What am I doing incorrectly?  This seems like such a common scenario for
data retrieval: ordering on a combination numeric+date index.

FYI, I am using dBase ]I[ + version 1.1 .  Please e-mail me any replies 
(at this point in time, I do not trust our newsfeeding machines :-).
Thanks for your help!

-- 

        Markus N. Richardson
        Research and Development
        Eaton Corporation IMSD
        31717 La Tienda Drive
        Westlake Village, CA 91359
        
        { voder,ihnp4,trwrb,scgvaxd,jplgodo }!wlbr!etn-rad!markus
          wlbr!etn-rad!markus@etn-wlv.eaton.com