Query on certain field names?

[buddyrich2]

Should be easy but....

Can anyone tell me how to query on field names that begin with a certain character (in the field name). For instance: query all records with field names that begin with "f" that contain data (are not empty).

Thanks.

 

[Mike Lewis]

Hi Buddyrich,

Should be easy but....

Doesn't sound all that easy to me <g>. I guess you could do something like this:

- Scan the table

- For each record:

- Use FCOUNT() and FIELD() to look at each field name in turn.

- If the field name begins with "F", use EVALUATE() to find the contents of the field.

- If the contents of the field is not empty, do whatever you want to do with the record (add it to a result set, for instance).

- Repeat for the next field.

- Repeat for the next record.

Does that sound like it will meet your needs? If so, I'll leave it to you to work out the details.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

The structure of tables and the database can be turned into data eg via ADBOBJECTS, AFIELDS, COPY STRUCTURE EXTENDED.

Nevertheless it sounds like wrong design if you query on field NAMES. That's a very unusual way and would also result on varying structures you'd need to handle afterwards.

Is this some kind of import job, where you are confronted with varying table structures?

This would work, if those fields are char, you'll need to change lcWhere if they are of different type:

Use sometable
Set Fields to all like f*
lcFieldlist = Set("Fields",1)
lcWhere = "not empty("+strtran(lcFieldlist,',','+')+")"
Select &lcFieldlist FROM sometable Where &lcWhere

Bye, Olaf.