Extracting varying length string between two characters

[ebs17]

Opened a query

That alone is certainly not enough. Keyword lazy loading: Often only the first records are displayed, while later records are still being calculated.
To ensure that ALL records have been recorded, you have to go to the end of the recordset.
You can add your tests with measurements to the database provided by Josef and show them here.

The functions were not called by calling rs.movelast.

That confuses me, although I can see really relevant differences in the database provided when I do another test.
I am/was of the opinion that in a query, a function involved is called and executed record by record if a variable argument from the record is passed to the function, thus making it clear that it is not a constant expression that would then only really be executed once.

 

[Josef P.]

I am/was of the opinion that in a query, a function involved is called and executed record by record if a variable argument from the record is passed to the function

I was of the same opinion until I realized that the test was running too fast (with rs.movelast). Then I inserted a Debug.Print into a function and saw that nothing was output via Debug.Print.

 

[pdanes]

That alone is certainly not enough. Keyword lazy loading: Often only the first records are displayed, while later records are still being calculated.
To ensure that ALL records have been recorded, you have to go to the end of the recordset.
You can add your tests with measurements to the database provided by Josef and show them here.

I also wrote in a subsequent post that I went to the last record. I used the navigation buttons on the bottom of the query's display, although I did not specify how I did it. It did not seem relevant, and I wasn't expecting to open such a detailed debate on the topic. But since you ask, that is how I did it. The query opens instantly in both cases. Jumping to the last record was also instant when using the expression, but had a delay of several seconds when using the VBA function.

I am not going to construct detailed measurements protocols just to post them here. I know what it does, I have posted what I observed and that is enough for me. If you want to do that, by all means go ahead - maybe someone will find it useful.

That confuses me, although I can see really relevant differences in the database provided when I do another test.
I am/was of the opinion that in a query, a function involved is called and executed record by record if a variable argument from the record is passed to the function, thus making it clear that it is not a constant expression that would then only really be executed once.

This does pass a variable argument - the value of the field from which the substring is extracted. That may potentially be different for every single record, so the computation, whether SQL expression or VBA function, is performed for every record. The SQL expression is orders of magnitude faster.

 

[ebs17]

So what's wrong with @Josef P.'s example?
After all, it's complete in the presentation, and it's been downloaded nine times so far.

 

[pdanes]

So what's wrong with @Josef P.'s example?
After all, it's complete in the presentation, and it's been downloaded nine times so far.

I don't claim there is anything wrong with it. Not one comment have I made anywhere that is critical of his work. What makes you think I believe that? I'm simply telling you what *-I-* did.

 

[ebs17]

When statements and conclusions contradict each other significantly, one should think twice ...

 

[pdanes]

When statements and conclusions contradict each other significantly, one should think twice ...

All right, now you're just babbling. I'm out.

 

[ebs17]

There are also different opinions. If someone claims something that is not happening, based on a clear example, it could be reduced to the fact that he himself is babbling or doing something else. In both cases, such statements should not be used to generalize, but should be characterized as a personal, individual situation. See explicit "*-I-*".