>Hi Guys
>
>Kinda bear with me on this, I am new to Fox Pro.
>
>I have a view with several related tables
>I have filters where I relate one field of one table to another field of another table.
>
>When I run my view I get all the information, but if I leave one of the filtered fields blank then none of my data shows up when I run the view.
>
>I still need the data to show up eventhough the one of the fields that I filter on is blank.
>
>Can any one out there help me with this.
>
>Thank
>
>Gary Hagerty
Hi Gary
I'm gonna try to help you, but I'm not sure to understand your question: when you tell that you 'leave one of the filtered fields blank' in your view, do you mean that you don't specify any parameter in the view? In other words, that you leave one of the criterias of the where clause blank? I presume you struggle with the view designer, but the principle is the same with SQL:
E.g., with the following statement:
SELECT * FROM myTable WHERE cSuchField = "ABC",
You'd like to be able to do sometimes:
SELECT * FROM myTable WHERE cSuchField = -Nothing-
and to get a result set in which the cSuchField wouldn't be 'filtered'?
Do I understand well?
If it is was actually the meaning of your question, you cannot create a view with blank parameters: you must always give an 'example' of the criteria you want to use for the 'filtered' fields, even if this example is empty. You have to use either a wild card or some work around, depending on the data type:
SELECT * FROM myTable WHERE cSuchField LIKE "%"
or
SELECT * FROM myTable WHERE cSuchField = ""
SELECT * FROM myTable WHERE nSuchField >0
SELECT * FROM myTable WHERE dSuchField < {01/01/9999}
In the view designer, you will have for the 4 lines above, in the filter tab:
Field name NOT Criteria Example Case Logic
==========================================================
cSuchField = ""
cSuchField LIKE "%"
nSuchField > 0
dSuchField < {01/01/9999}
Or you can build dynamically your SQL statement, but it's another story...
I hope to have been able to help you
Kind regards
Philippe RIONDEL,
a French man in Montreal
=======================================================Who tries can sometimes be wrong, who doesn't is always