Queries return empty table results.

[T17Rax]

Here's a Question:

How come when I sometimes run a query or a search by filter expressions, the queries come back with an empty table, rather than the records that I specifically wanted?

For example,

SET FILTER TO Dat <= {04/09/2014) AND dat =>{05/09/2014}

The dates are definitely in the table and there are previous/later dates too, hence the operators...
Usually if the statement is wrong, the fox would say so. But it appears as if it's right just can't be bothered to find anything.
I've noticed this a few times with SQL select queries too. The field type is usually checked to so I can write the correct expression.

Anyone got any ideas?
I wouldn't want to use this in my job, where I can show someone how to filter or select 'x' records and the results come back as empty. That would be embarrassing...

Thanks all.
Vibrant.

 

[Tore Bleken]

You have got it wrong, you have effectively filtered out all the records! Did you mean OR instead of AND?

 

[Mike Lewis]

TB is right. How can a date be both before 4/9 and after 5/9?

If you want the dates to be within that range, use BETWEEN(). If you want them to be outside that range, use NOT BEFORE().

Also, it's slightly dangerous to express date constants in that way. Is 4/9 the same as 4th September or 9th April? The answer, of course, depends on your SET DATE format. The poing is that it is ambiguous and could lead to errors. Better to use the so-called unambiguous date format: {^2014-9-4}, for example, which means 4th September.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[T17Rax]

Re-reading what i've put and examining the statements again, I've realised it was the wrong command to use for such an expression and I've got the expression operators the wrong way around!(Although when I had tried to reverse it, the result came back as the original table).
The objective of the set filter command was to get a scope of those records that fell between the 4th and 5th September.
I could have just used '"=" {date here} AND "=" {date here}'... Deary me!

I definitely wanted to use "AND", although I think it would have been better with a wider date range maybe, and with the select command only. i.e,

SUM(Am1) for Loc = "5" and Dat => {05/05/2015} and Dat <= {12/05/2015}

(The date range is in a british format).

Mike, your comment leads me to another question. Why the "^"? I've seen this in many places before but I can't get a grasp on what it's there for? It probably would be wise to keep to a strict date format unless I did use the set date. I'll think of that in future!

Thanks guys,
Anthony.

 

[Tore Bleken]

http://fox.wikis.com/wc.dll?Wiki~StrictDateFormat

 

[Mike Lewis]

The "^" is to siginify that the date is in "strict date format", that is, it is independent of SET DATE, SET CENTURY, etc. See the VFP Help for the date data type for more information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

CTOD() would depend on the date formart. But even then a British date would not start with the day part. And the date literals you enclose in curly brackets also have to start with ^ after the opening bracket.
Just get your basics corrrect and don't confuse vfp syntax with other languages.

Bye, Olaf.

 

[Mike Lewis]

A (possibly) more intuitive way of expressing literal dates is with the DATE() function, for example DATE(2015, 9, 4), which means year 2015, month 9, day 4.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, the most dynamic thing to use is variables, eg ... WHERE datefield Between ?m.ldStartDate AND ?m.ldEndDate and preset the variables with the dates in any way you're used to.

The syntax with ? works for a) direct sql queries (though optional here) b) views as parameters and c) remote queries sent by sqlexec() or cursoradapters. So it's universal to use variables for variable values in a query.

Bye, Olaf.