Set Date Filter

[Stevo123]

Hi All,

I'm fairly new to VFP and am trying to use the SET FILTER TO command to filter on a date in a table but I get a syntax error when doing the following:

SET FILTER TO datepaid = "05/07/12"

How can I filter on just a single date?

Thanks

Steve

 

[Mike Lewis]

If the Datepaid field is a Date data type (which it probably is), then you need to do this:

SET FILTER TO DATE(2012, 7, 5)

This will set the filter to 5 May 2012.

The problem with your code is that you are comparing a date to a character string, which doesn't work in VFP (unlike some other languages).

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[TamarGranor]

Just to add to Mike's comments, SET FILTER isn't usually the best way to do things. Depending on your goal, different approaches are better.

So what are you actually trying to do here?

Tamar

 

[Stevo123]

Thanks for your replies!

I'm trying to troubleshoot some data in a table (payments table) that has a record of payments made to a loan. The datepaid field is obviously the date the payment was entered (and is a date field in VFP), so I was trying to browse the table with a filter set to the date I was troubleshooting.

Is this the right approach?

Steve

 

[JRB-Bldr]

You have already been given good advice for setting a FILTER

But first you have to determine the field type of the data.
If it is a Date, then with what you have above you should be good-to-go.

But if it merely Looks like a Date, but is, in reality, something else (e.g. Character, DateTime, etc.), then changes would need to be made.

Until you tell us differently, I'll assume that it really is a Date field type.

Another way to get just the desired records would be to use a SQL Query to acquire only those records matching your criteria

cDateToFind = "05/07/12"  && Criteria in String Format
SELECT *;
   FROM MyTable;
   WHERE Date = CTOD(cDateToFind);
   INTO CURSOR Results READWRITE

Note - In VFP there are a variety of ways to represent a Date value
dDate = {^2012-5-7}
?TYPE('dDate')
dDate = DATE(2012,5,7)
?TYPE('dDate')
dDate = CTOD("5/7/2012")
?TYPE('dDate')

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Steve,

I would normally agree with the advice to avoid using SET FILTER. Within an application, there are better ways of achieving your goal.

However, since you are only interested in browsing the data yourself in order to locate a problem, it's perfectly acceptable to use SET FILTER. In fact, in this particular case, it's probably the simplest way to do it.

Since we've now established that the field is a Date data type, the code I gave you (in the second post, above) is all you need.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike,

almost, because SET FILTER TO DATE(y,m,d) will not work, you need SET FILTER TO datefield = DATE(y,m,d)

I'd use BROWSE FOR datefield = DATE(y,m,d) in this case The advantage is, once you edit the date, it will not match the filter and seem to vanish, if you BROWSE FOR a condition, you will see all reacords you get initially, even if editing them in a way that removes them from that condition.

Bye, Olaf.

 

[Mike Lewis]

My mistake. I should have said this:

SET FILTER TO DatePaid = DATE(2012, 7, 5)

Thanks for pointing that out, Olaf. Also I take your point about editing the date, although I don't suppose that will matter in this case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[TamarGranor]

Just wanted to add that I agree with Mike. For interactive use, SET FILTER is fine. I just wouldn't use it in an application.

Tamar

 

[Stevo123]

Thanks very much everyone. I'm glad I've found this forum! Appreciate the fast and useful help!

 

[Mike Lewis]

Now that you've found us, be sure to come back when you have more questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy