indexing on two different fields 1

[kate]

I have a table that I want to create an index for on two fields. I want the table to be ordered by a name field with field type of char and then by a expir_date field with type of date, however I want the name field to be ascending and the date field to be descending.

I've tried creating to different tag names and calling them both at the same time, but I get a "operator/operand type mismatch error". So I tried using the expression dtos(expir_date) and then calling calling both indexes but I get the same error.

Is there a way to make one of the fields ascending and the other descending? I know I could use the sort command, but I want to be able to open this table with an index.

 

[craigber]

INDEX ON NameField + STR({^2099/12/31}-DateField, 10) TAG MyTag

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[GriffMG]

Smack on Craig.

I would even use the same date!

index on NameField+str(ctod("31/12/2099")-DateField,12,0) tag MyTag

Is that affected by regional settings?

Perhaps this is better?

index on NameField+str(ctod("12/12/2099")-DateField,12,0) tag MyTag

Regards

Griff
Keep [Smile]ing

 

[rgbean]

Griff,
Craig used the Strict Date format that's been in VFP since 6.0 SP3 (I think). from the help file:

A caret (^) can be included as the first character in a date to specify year-month-day date ordering, overriding the order specified by the DateFormat property or SET DATE.

Rick

 

[GriffMG]

Rick,

See - I *should* know that, but I had no idea!

perhaps I should RTFM?


Thanks

Regards

Griff
Keep [Smile]ing

 

[craigber]

I would not use CTOD... it can cause problems. Use Strict date as I did or the DATE() function.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[kate]

Thanks for the help. I'm still having trouble though creating this index. Here's the code I'm using

INDEX ON owner_name + STR({^2099/12/31}-expir_date, 10) TAG namedate

where owner_name is the name of the char field and expir_date is the name of the date field. When I enter this code I get an error

"Index does not match the table. Delete the index file and recreate the index."

So I click OK and it looks like it is creating the index, but it only indexes on the name field and not the date field.

I am using VFP 6.0, although I don't think we have SP3. Could this be the problem?

 

[craigber]

It shouldn't be the problem, but I HIGHLY recommend you upgrade to VFP 9...short of that, you should apply SP3.

I just tried the code on a sample table and it worked fine. You should DELETE TAG NameDate and then try again.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[craigber]

Actually, I was wrong.. it will be a problem, as strict date is not supported in VFP 6. Use the following:

INDEX ON owner_name + STR({12/31/2099}-expir_date, 10) TAG namedate

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[GriffMG]

Ah, so that's why I didn't know about it!

I've NOT READ the VFP9 manual!

Although I am a fan of it generally.

SP5 is pretty much a necessity for VFP6, with that you have a pretty stable product - most of my existing apps are still in VFP6 and will remian so for the foreseeable future. If it ain't broke - don't fix it!

Regards

Griff
Keep [Smile]ing

 

[pthalacker]

Hi Kate,

The expression should work fine in VFP6. The strictdate concept was introduced in VFP6.

It sounds as though something is actually wrong with the CDX. Perhaps it was damaged as a result of previous attempts to create this index. Try doing as VFP suggests and delete the CDX and recreate it from scratch.

pamela

 

[kate]

That code returned an error

"Ambiguous date/datetime constant. Use the format:{^yyyy-mm-dd[hh[:mm[:ss]][a|p]]}"

I tried using a format like this, but got the error

"Date/datetime contains illegal characters"

It's starting to seem like this may not be possible. Am I doing something wrong?

 

[kate]

Oh and I have tried deleting the index and recreating it, but when I try to recreate it using this code

"INDEX ON owner_name + STR({^2099/12/31}-expir_date, 10) TAG namedate"

I get that error "Delete index and recreate

 

[kate]

Oh, and where can I download SP5 or even SP3

 

[GriffMG]

Try my solution instead - just to get it going:

index on owner_name+str(ctod("12/12/2099")-expir_date,12,0) tag namedate

Martin

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

http://msdn.microsoft.com/vstudio/downloads/updates/sp/vs6/sp5/default.aspx

Regards

Griff
Keep [Smile]ing

 

[kate]

Thank You Griff. I don't know why I didn't try that earlier. That seemed to work. Thanks for your help everyone.

 

[Stella740pl]

STRICTDATE is supported in VFP6, but in order to use it you should SET STRICTDATE TO 1 or SET STRICDATE TO 2.
Can also be set through menu Tools->Options->General->Strict Date Level.

I, personally, SET STRICDATE TO 0, because I support a lot of converted from previous versions applications. Also, for indexing purposes I would prefer to use DTOS(), not CTOD() or DTOC(), as DTOS() is almost format independent and recommended for indexing. That's why I would go with this solution:

INDEX ON NameField+STR(20991201-VAL(DTOS(DateField)))TAG MyTag

 

[Stella740pl]

Oh, and even with SET STRICTDATE TO 0 VFP6 wouldn't throw an error.

I think I was able to use it way before I installed any service packs - never indexed on an expression with STRICTDATE in it, though.

 

[GriffMG]

Would I be right in suggesting the use of a UDF?

One which saved the current date settings and restored them?

All seems a bit clumsy - I'll use the ctod() route personally!

Regards

Griff
Keep [Smile]ing