These forums were permanently set to read-only mode on July 20, 2022. From that day onwards, no new posting or comment is allowed on the site, but the historical content remains intact and searchable.

A new location for posting questions about PlanetPress Suite is now available:

OL Learn - PlanetPress Classic (opens in new tab)

Topic Options
#42886 - 03/25/13 04:41 PM Filter Failure
SanderO Offline
OL Newbie

Registered: 03/25/13
Posts: 12
Hello All,
First post here so please forgive any improper netiquette.

I check the FAQs and searched the KB and haven't found an answer so I'm trying for one here.

In PSM 7.2 (PC bld 7506) I have a Visual Foxpro v9 .DBF data source which included a field called LANG defined as C(1) (a one byte text character).
The field was populated with either an "E" or an "F" for English/French. All records had either one value or the other.

When I tried to use a filter of LANG / equals / E
or LANG / equals / "E" it always tells me that I can't use that filter as it would remove ALL records. It wouldn't work for "F" either. I tried using "does not equal", "contains" and "does not contain". No dice. I tried restarting the program, rebooting my PC, but nothing worked.

I have used filters in most of my jobs previously without a problem.

I then converted the .DBF file to an Excel .XLS and voila , the filter works like a charm.

Seeing as how I do most of my work with .DBF files (are you listening Microsoft???), I would appreciate any insight into this problem.

Thanks in advance

#42891 - 03/26/13 04:03 AM Re: Filter Failure [Re: SanderO]
Sander vd Berg Offline
OL Expert

Registered: 06/10/08
Posts: 207
Loc: Objectif Lune NL
The filter wizard will use the provided values to construct a SELECT query with a WHERE clause.

I can't explain why it won't work for that particular case. I can't reproduce the problem with the .DBF databases that are available to me. Perhaps the driver expects some special syntax when comparing a field with type C(1)?

Maybe you could bypass the wizard and try to construct the query yourself. After opening the data source, go to Database > Select Table. Double-click 'Custom Query', and append 'WHERE' followed by a filter.

For example:

Like I said, maybe C(1) requires syntax that is slightly different. You could try to coerce the field to string by appending an empty string:

SELECT * FROM [Example.DBF] WHERE LANG & '' = 'E'

Please let us know how it works out.

Edited by Sander vd Berg (03/26/13 05:24 AM)
Edit Reason: Added examples

#42893 - 03/26/13 08:00 AM Re: Filter Failure [Re: Sander vd Berg]
SanderO Offline
OL Newbie

Registered: 03/25/13
Posts: 12
Thanks for responding.

C(1) just means a 1 character field. Nothing special about it. I have used this type of field many times for things like language, gender, groupings etc. Never a problem.

Anyway, I tried ->SELECT * FROM [Example.DBF] WHERE LANG = 'E'<-
and it worked.

I'll have to remember this option (Custom queries) for the future.

While it doesn't answer the question of why it doesn't work , it at least provides a viable, sensible workaround.

Thanks again.