Recordset question

[Middlemarch]

Is there any significant cost to Select * from as opposed to specifying the fields wanted?
And can you filter a recordset more than once ? (Saves me trying if it can't be done)

[June7]

Recordset does have a Filter property.
Would open another recordset using the first recordset. Review https://learn.microsoft.com/en-us/of...r-property-dao

[pbaldy]

To your first question, I'd say generally no. There is a cost, but unless there are a lot of unneeded fields and perhaps they contained large amounts of text or something, I doubt you'd notice a difference. Also the number of records being pulled. Basically it's question of how much data is being retrieved. A few fields in a few records, you won't see a difference. 50 extra memo fields in 5,000 records, maybe a different story.

[Middlemarch]

Thanks for the replies. Decided to not use "Select *" and have had some success with filtering,
But have struck a snag... one of my fields is a calculated field Comm: [AAA] & Chr$(10) & [BBBB]
It's index is 9 and printing r(9),name shows Comm as expected.

However using Comm in the Filter command e.g. r.Filter = "[Comm] = " & Chr$(34) & a & Chr$(34) fails.
Also r.Filter = "[AAA] & Chr$(10) & [BBBB] = " & Chr$(34) & a & Chr$(34) fails.

So is it not possible to filter on a calculated field ?

[June7]

Yes, can filter on calculated field.

Why are you using Chr$(10) - that is line feed and its presence could impact search results.

Fails how - error message, wrong result, nothing happens?

[Middlemarch]

The fields are multi-line with vblf separators

The error message was a bit vague - err 1006 error.description unknown
It is worth sorting, or maybe I change to using 2 fields? But if it should work as you say... what would the filter line be ?
I haven't had an issue with chr$(10) impacting searches so far.

[madpiet]

Not sure it matters a lot. I've always thought it's lazy to use SELECT *, instead of just including the columns you need. but then, I try to never use recordsets either, but sometimes you can't avoid it.