Can a Query call a Statement?

[gemma-the-husky]

OK - I thought you could use the dir command within your routine

One other thing - I found this to delete the undeleteable folders - it worked OK for me.

http://www.jrtwine.com/Products/DelFXPFiles/

 

[ChrisO]

Thanks, Dave, that worked.

 

[stopher]

Overloading meaning replace the internal Access MkDir Subroutine with a user written MkDir Function which can be called from a Query.

Hi Chris
Would "Overriding" be a better term? The only reason I mention this is my understanding of "overloading", at least from my very limited education in Smalltalk and Java, is that multiple methods with the same name can co-exist providing they can be individually identified by their parameter requirements. Such methods can have completely different functionality. I appreciate you've qualified your meaning of overloaded here. Just wanted to make sure I'm uderstanding correctly.

Thanks for raising the principle of extending a function's functionality in this manner - very interesting. Give lots of food for thought for extending other functions.

Chris

 

[ChrisO]

In VBA I doubt if the difference in term has much meaning. I tend to use the term ‘Overloading’ because it implies the same name but in different context. In other languages, such as C++, it has a more defined meaning but in VBA it can be defined as ‘Scope of Context’.

Example:_
The new MkDir Function overloads the old MkDir Statement in as much as the new MkDir Function takes priority over the old MkDir Statement. But the new MkDir Function does not overload the VBA.MkDir Statement because the VBA qualifier forces the ‘Scope of Context’ which the compiler will use.

The reason that I started this thread was to make sure that the internal MkDir Statement can’t be called from within a Query. If an Access Function, such as Now(), is overloaded it causes the Now() Function to fail in Queries. I did this once in VBA to overload the Now() Function to get it to return server time. The reason for that was, for instance, an audit trail often uses Now() to timestamp a record. But Now() uses front end time and not all front ends would necessarily be time synchronized. The problem was that the overloading worked in VBA but not if Now() was used directly in a Query. It’s fine if the Query is built in VBA because the Now() Function then becomes the VBA.Now(). May say to readers at this stage, I’m not looking or interested in workarounds for that either.

But to answer your direct question about overloading verses overriding; I’m not too fussed with what it’s called provided it works properly within the requirements. Perhaps the correct term would be ‘VBA Overloading with Scope of Context’ but who wants to write that all the time?

If you’re interested, another candidate for overloading might be the Kill Statement. Currently it won’t Kill the type of file Dave was talking about but I’ll bet there’s an API call that will.

 

[gemma-the-husky]

In VBA I doubt if the difference in term has much meaning. I tend to use the term ‘Overloading’ because it implies the same name but in different context. In other languages, such as C++, it has a more defined meaning but in VBA it can be defined as ‘Scope of Context’.

Example:_
The new MkDir Function overloads the old MkDir Statement in as much as the new MkDir Function takes priority over the old MkDir Statement. But the new MkDir Function does not overload the VBA.MkDir Statement because the VBA qualifier forces the ‘Scope of Context’ which the compiler will use.

The reason that I started this thread was to make sure that the internal MkDir Statement can’t be called from within a Query. If an Access Function, such as Now(), is overloaded it causes the Now() Function to fail in Queries. I did this once in VBA to overload the Now() Function to get it to return server time. The reason for that was, for instance, an audit trail often uses Now() to timestamp a record. But Now() uses front end time and not all front ends would necessarily be time synchronized. The problem was that the overloading worked in VBA but not if Now() was used directly in a Query. It’s fine if the Query is built in VBA because the Now() Function then becomes the VBA.Now(). May say to readers at this stage, I’m not looking or interested in workarounds for that either.

But to answer your direct question about overloading verses overriding; I’m not too fussed with what it’s called provided it works properly within the requirements. Perhaps the correct term would be ‘VBA Overloading with Scope of Context’ but who wants to write that all the time?

If you’re interested, another candidate for overloading might be the Kill Statement. Currently it won’t Kill the type of file Dave was talking about but I’ll bet there’s an API call that will.

with regard to Chris's last point, I had managed to create a folder name (with MkDir) with 3 trailing spaces - and there didnt seem to be any way to delete it by normal means.

 

[gemma-the-husky]

out of interest/just a thought

why CAN you put =now() or even =curdir() as the definition of a column in a query

but not put =mkdir("c:\somefolder")

=======
I presume it is because mkdir does not return a value - it is described in help as a statement, not a function - and the message you get is "undefined function".

Now your method does declare mkdir as a function that returns a boolean - so it is possible that your overloaded version WILL work in a query. I havent tested it, and I dont think I will.

But is that the idea, or is that immaterial anyway?

 

[dfenton]

Your code for stripping the leading and trailing spaces could be a lot simpler if you used Split() to chop up the path into an array, then use Trim() on each element of the array and re-assemble the results.

 

[gemma-the-husky]

Your code for stripping the leading and trailing spaces could be a lot simpler if you used Split() to chop up the path into an array, then use Trim() on each element of the array and re-assemble the results.

yep, that is a lot easier isn't it!

 

[ChrisO]

David.

I’m not sure whose code you are talking about but I don’t think using the Trim() Function would work.

 

[gemma-the-husky]

David.

I’m not sure whose code you are talking about but I don’t think using the Trim() Function would work.

i think dfenton was referring to my code to strip out embedded spaces, and i thought he was correct in that using split was easier, than the method i had used

 

[ChrisO]

Using split might be easier, but I said using Trim() might not work.

The reason for not using Trim() is that it would remove both the leading and trailing spaces. But that would violate the functionality of the MkDir Statement in that leading spaces are perfectly valid in a directory name. So Trim() would not work but RTrim() would.

 

[dfenton]

You're right about RTrim() vs. Trim(). I didn't read the original post carefully.

Personally, paths and filenames with *any* spaces in them annoy the hell out of me -- I don't use them ever. But I don't force that preference on my users.